Archive for November, 2009

« Older Entries

Discover Method Part 2

November 21, 2009

Lets look at some more Discover Commands . Many of this commands are not documented at BOL .

For previous part of this article
Discover Method Part 1


Provides information for memory usage by each object

SELECT *
FROM SYSTEMRESTRICTSCHEMA (
$SYSTEM.DISCOVER_MEMORYUSAGE)

Provides information for transaction

SELECT *
FROM SYSTEMRESTRICTSCHEMA (
$SYSTEM.DISCOVER_TRANSACTIONS)

Provides performance counter details

SELECT *
FROM SYSTEMRESTRICTSCHEMA (
$SYSTEM.DISCOVER_PERFORMANCE_COUNTERS,
PERF_COUNTER_NAME = ‘\MSAS 2005:Memory\Memory Usage KB’)

Provides information for sessions

SELECT *
FROM SYSTEMRESTRICTSCHEMA (
$SYSTEM.DISCOVER_SESSIONS)

Provides information for selected partition

SELECT *
FROM SYSTEMRESTRICTSCHEMA
(
$SYSTEM.DISCOVER_PARTITION_STAT
,DATABASE_NAME = ‘Adventure Works DW 2008’
, CUBE_NAME=’Adventure Works’
,MEASURE_GROUP_NAME = ‘INTERNET SALES’
,PARTITION_NAME = ‘Internet_Sales_2002’
)

Provides information for dimensions related to partition

SELECT *
FROM SYSTEMRESTRICTSCHEMA
(
$SYSTEM.DISCOVER_PARTITION_DIMENSION_STAT
,DATABASE_NAME = ‘Adventure Works DW 2008’
, CUBE_NAME=’Adventure Works’
,MEASURE_GROUP_NAME = ‘INTERNET SALES’
,PARTITION_NAME = ‘Internet_Sales_2002’
)

Provides information for Dimensions

SELECT *
FROM SYSTEMRESTRICTSCHEMA (
$SYSTEM.DISCOVER_DIMENSION_STAT
, DIMENSION_NAME = ‘Customer’
, DATABASE_NAME=’Adventure Works DW 2008′
)

Provides attribute name and attribute count for the dimension

Posted in Analysis Service-MDX | 1 Comment »

Discover Method Part 1

November 21, 2009

DISCOVER METHODS
They are very rich source for getting meta data information for the server.
Lets look for more detail on DISCOVER METHODS

Provides information for commands

SELECT *
FROM SYSTEMRESTRICTSCHEMA(
$SYSTEM.DISCOVER_COMMANDS
)

Provides resource usage information for current running or last executed command.From command_text column we can get query for the session.

You can get more information from here

http://msdn.microsoft.com/en-us/library/bb934103.aspx

Provides information for connections.

SELECT *
FROM SYSTEMRESTRICTSCHEMA(
$SYSTEM.DISCOVER_CONNECTIONS
)

Provides information related to connection.
Useful column will be connection_host_name which will provide ip of the connection, connection host application.

You can get more information from here

http://msdn.microsoft.com/en-us/library/bb934105.aspx

Provides information for datasources

SELECT *
FROM SYSTEMRESTRICTSCHEMA(
$SYSTEM.DISCOVER_DATASOURCES
)

But this one will not give results using select command.

You can get the results with using DISCOVER method.

<Envelope xmlns=”http://schemas.xmlsoap.org/soap/envelope/”&gt;
<Header />
<Body>
<Discover xmlns=”urn:schemas-microsoft-com:xml-analysis”>
<RequestType>DISCOVER_DATASOURCES</RequestType>
<Restrictions>
<RestrictionList />
</Restrictions>
<Properties>
<PropertyList />
</Properties>
</Discover>
</Body>
</Envelope>

You can get more information from here

http://msdn.microsoft.com/en-us/library/ms126129.aspx

To get information for current instance

SELECT *
FROM SYSTEMRESTRICTSCHEMA(
$SYSTEM.DISCOVER_INSTANCES
,INSTANCE_NAME=’sseight’
)

You can get more information from here

http://msdn.microsoft.com/en-us/library/ms126129.aspx

Provides information for the active jobs running on the server

SELECT *
FROM SYSTEMRESTRICTSCHEMA (
$SYSTEM.DISCOVER_JOBS
)

You can get more information from here
http://msdn.microsoft.com/en-us/library/bb934102.aspx

Provides information for all keywords reserved by provider

SELECT *
FROM SYSTEMRESTRICTSCHEMA (
$SYSTEM.DISCOVER_KEYWORDS
)

You can get more information from here

http://msdn.microsoft.com/en-us/library/ms126063.aspx

Provides information for locks

SELECT *
FROM SYSTEMRESTRICTSCHEMA (
$SYSTEM.DISCOVER_LOCKS
)

SELECT *
FROM SYSTEMRESTRICTSCHEMA (
$SYSTEM.DISCOVER_LOCKS,
LOCK_STATUS =’1′
)

— 0 Granted locks
— 1 Waiting locks
Another useful column in lock_type

You can get more information from here
http://msdn.microsoft.com/en-us/library/bb934104.aspx

Provides information for memory usage by objects

SELECT *
FROM SYSTEMRESTRICTSCHEMA (
$SYSTEM.DISCOVER_OBJECT_MEMORY_USAGE
)
ORDER BY OBJECT_MEMORY_NONSHRINKABLE DESC

You can get more information from here
http://msdn.microsoft.com/en-us/library/bb934098.aspx

Provides information about the standard and provider-specific properties

SELECT *
FROM SYSTEMRESTRICTSCHEMA (
$SYSTEM.DISCOVER_PROPERTIES
)
You can get more information from here
http://msdn.microsoft.com/en-us/library/ms126045.aspx

Posted in Analysis Service | 2 Comments »

DMVs in Analysis Service –Part4

November 15, 2009

For previous part of this article

DMVs in Analysis Service — Part 3 (MDSCHEMA_MEMBERS)

DMVs in Analysis Service — Part 2 (MDSCHEMA)

DMVs in Analysis Service — Part 1 (DBSCHEMA)

Information for Measures

SELECT * FROM $SYSTEM.MDSCHEMA_MEASURES
WHERE CUBE_NAME = ‘Adventure Works’

SELECT * FROM $SYSTEM.MDSCHEMA_MEASURES
WHERE CUBE_NAME = ‘Adventure Works’
and [MEASURE_AGGREGATOR] = ‘1’

Aggregation type

1 Sum
2 Count
3 Min
4 Max
5 Avg
6 Var
7 STDEV

You can get more information from here

http://msdn.microsoft.com/en-us/library/ms126250.aspx

To get information for member/cell property

SELECT * FROM $SYSTEM.MDSCHEMA_PROPERTIES

SELECT * FROM
SYSTEMRESTRICTSCHEMA
($SYSTEM.MDSCHEMA_PROPERTIES,
CUBE_SOURCE = ‘1’ — 1 for CUBE
— 2 for DIMENSION
)

http://msdn.microsoft.com/en-us/library/ms126309.aspx

For information on functions

SELECT * FROM $SYSTEM.MDSCHEMA_FUNCTIONS

gives information for available functions.
Parameter list provides list of parameters which we need to provide to function while using it

SELECT * FROM $SYSTEM.MDSCHEMA_FUNCTIONS WHERE ORIGIN = ‘1’

Origin
1 for MDX
2 for user defined”

You can get more information from here

http://msdn.microsoft.com/en-us/library/ms126257.aspx

For information on Actions

SELECT * FROM $SYSTEM.MDSCHEMA_ACTIONS

This one is giving error while running it.
I have created a bug here

For information on KPIS

SELECT * FROM $SYSTEM.MDSCHEMA_KPIS

You can get more information from here

http://msdn.microsoft.com/en-us/library/ms126258.aspx

For information on MeasureGroups

SELECT * FROM $SYSTEM.MDSCHEMA_MEASUREGROUPS

SELECT * FROM $SYSTEM.MDSCHEMA_MEASUREGROUPS
WHERE CUBE_NAME = ‘ADVENTURE WORKS’

You can get more information from here

http://msdn.microsoft.com/en-us/library/ms126178.aspx

For information on MeasureGroup Dimensions

SELECT * FROM $SYSTEM.MDSCHEMA_MEASUREGROUP_DIMENSIONS
WHERE CUBE_NAME = ‘ADVENTURE WORKS’

You can get more information from here
http://msdn.microsoft.com/en-us/library/ms126192.aspx

For information on Datasources

SELECT * FROM $SYSTEM.MDSCHEMA_INPUT_DATASOURCES

Here you can get last time when data source is updated

You can get more information from here

http://msdn.microsoft.com/en-us/library/ms126243.aspx

Posted in Analysis Service-MDX | 2 Comments »

Last Nonempty Date/ Last Transaction Date

November 12, 2009

I freqeuntly got question on to get last non empty date or last date when transaction happened.

Here we will look at two example on how to get it from Adventure Works cube

SELECT
[Measures].[Internet Sales Amount] ON 0
,Tail
(
Filter
(
[Date].[Date].MEMBERS
,(NOT
IsEmpty([Measures].[Internet Sales Amount]))
)
,1
) ON 1
FROM [Adventure Works];

SELECT
[Measures].[Internet Sales Amount] ON 0
,Tail
(
NonEmpty
(
[Date].[Date].MEMBERS
,[Measures].[Internet Sales Amount]
)
,1
) ON 1
FROM [Adventure Works];

Cheers
Amish Shah

Posted in Analysis Service-MDX | Leave a Comment »

DMVs in Analysis Service — Part 3 (MDSCHEMA_MEMBERS)

November 10, 2009

Till now we have used MDX query to get hierarchical members in a hierarchy, But after DMVs its now possible to get this values using DMV MDSCHEMA_MEMBERS
It will be a fun to use DMV to get value. Lets more explore in it.

For previous part of this article

DMVs in Analysis Service — Part 2 (MDSCHEMA)

DMVs in Analysis Service — Part 1 (DBSCHEMA)

SELECT * FROM $SYSTEM.MDSCHEMA_MEMBERS

SELECT TOP 10 * FROM $SYSTEM.MDSCHEMA_MEMBERS
WHERE [MEMBER_TYPE] = ‘3’

Type of Member_Type

1 Regular
2 ALL Member
3 Measure
4 Formula
0 Unknown

Formula has precedence over measure

Now lets look at queries to get hierarchical information

To get all ancestors of a member

SELECT * FROM SYSTEMRESTRICTSCHEMA
($SYSTEM.MDSCHEMA_MEMBERS,
[CUBE_NAME] =’ADVENTURE WORKS’,
[MEMBER_UNIQUE_NAME] = ‘[CUSTOMER].[CUSTOMER GEOGRAPHY].[STATE-PROVINCE].&[NY]&[US]’,
TREE_OP = ’32’
)

To get immediate children of a member

SELECT * FROM SYSTEMRESTRICTSCHEMA
($SYSTEM.MDSCHEMA_MEMBERS,
[CUBE_NAME] =’ADVENTURE WORKS’,
[MEMBER_UNIQUE_NAME] = ‘[CUSTOMER].[CUSTOMER GEOGRAPHY].[STATE-PROVINCE].&[NY]&[US]’,
TREE_OP = ‘1’
)

To get immediate immediate parent

SELECT * FROM SYSTEMRESTRICTSCHEMA
($SYSTEM.MDSCHEMA_MEMBERS,
[CUBE_NAME] =’ADVENTURE WORKS’,
[MEMBER_UNIQUE_NAME] = ‘[CUSTOMER].[CUSTOMER GEOGRAPHY].[STATE-PROVINCE].&[NY]&[US]’,
TREE_OP = ‘4’
)

To get self

SELECT * FROM SYSTEMRESTRICTSCHEMA
($SYSTEM.MDSCHEMA_MEMBERS,
[CUBE_NAME] =’ADVENTURE WORKS’,
[MEMBER_UNIQUE_NAME] = ‘[CUSTOMER].[CUSTOMER GEOGRAPHY].[STATE-PROVINCE].&[NY]&[US]’,
TREE_OP = ‘8’
)

To get all descendants

SELECT * FROM SYSTEMRESTRICTSCHEMA
($SYSTEM.MDSCHEMA_MEMBERS,
[CUBE_NAME] =’ADVENTURE WORKS’,
[MEMBER_UNIQUE_NAME] = ‘[CUSTOMER].[CUSTOMER GEOGRAPHY].[STATE-PROVINCE].&[NY]&[US]’,
TREE_OP = ’16’
)

Posted in Analysis Service-MDX | 1 Comment »

« Older Entries
Design a site like this with WordPress.com
Get started