DMVs in Analysis Service — Part 1 (DBSCHEMA)

As we all know that AS 2008 has come with many new features
I am using SQL Server since last 8 years and DMVs are great improvement in SQL Server 2005
Now exciting thing is that this DMVs are also in Analysis Service
Till now its not possible to view metadata in Analysis Service , but after 2008 its possible.
We will explore this DMVs in coming articles.

DBSCHEMA_CATALOGS

SELECT * FROM $SYSTEM.DBSCHEMA_CATALOGS

Here * in roles means that current user is a server or database administrator
Roles are shown as comma separated.

We can get column information from here

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

DBSCHEMA_TABLES

SELECT * FROM $SYSTEM.DBSCHEMA_TABLES

To find dimensions /measuregroups

SELECT * FROM $SYSTEM.DBSCHEMA_TABLES
WHERE TABLE_TYPE = ‘TABLE’ — TABLE indicates its a dimension
— SYSTEM TABLE indicates is a measure group

To get column information
http://msdn.microsoft.com/en-us/library/ms126299.aspx

DBSCHEMA_COLUMNS

SELECT * FROM $SYSTEM.DBSCHEMA_COLUMNS

SELECT * FROM $SYSTEM.DBSCHEMA_COLUMNS
WHERE COLUMN_OLAP_TYPE = ‘MEASURE’ — MEASURE for measurgroup columns
— ATTRIBUTE for dimension attributes

To get column information
http://msdn.microsoft.com/en-us/library/ms126208.aspx

DBSCHEMA_PROVIDER_TYPES

SELECT * FROM $SYSTEM.DBSCHEMA_PROVIDER_TYPES
Provides information for base data types

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

How to get comma separated list of members for Dimension Attribute

Q:- I want a comma separated list of all members of attribute
How can I get it?

Thanks
Arvind

Ans:-
WITH SET [ABC] AS [CUSTOMER].[CUSTOMER GEOGRAPHY].[COUNTRY].MEMBERS
MEMBER [MEASURES].[TEST] AS GENERATE([ABC], [ABC].CURRENTMEMBER.NAME, “,”)
SELECT [MEASURES].[TEST]
ON 0
FROM [ADVENTURE WORKS]

Gemini Renamed as PowerPivot

hi friends
Who are working with analysis service must be aware of Gemini.Its addon for Excel 2010 for users to handle large amount of data and analysis them , a self service BI
This project is now renamed as PowerPivot
We can get more information over here
http://www.powerpivot.com/

Clear Cache and bug in Clearing Cache for partition

We can Clear Cache by using ClearCache command inXML forAnalysis (XMLA)

for a specified Database,Cube,MeasureGroup ,dimension or partition.

Once we clear the cache analysis servicewill rebuild the cache

We can use this command to check performance testing ofMDX Queries

Syntax for clear cache

<Command>

<ClearCache>

<Object>…Object>

ClearCache>

Command>

Clear Cache for Database

<ClearCache xmlns=“http://schemas.microsoft.com/analysisservices/2003/engine&#8221;>

<Object>

<DatabaseID>Adventure Works DW 2008DatabaseID>

Object>

ClearCache>

Clear Cache for Cube

<ClearCache xmlns=“http://schemas.microsoft.com/analysisservices/2003/engine&#8221;>

<Object>

<DatabaseID>Adventure Works DW 2008DatabaseID>

<CubeID>Adventure WorksCubeID>

Object>

ClearCache>

Clear Cache for Measure Group

<ClearCache xmlns=“http://schemas.microsoft.com/analysisservices/2003/engine&#8221;>

<Object>

<DatabaseID>Adventure Works DW 2008DatabaseID>

<CubeID>Adventure WorksCubeID>

<MeasureGroupID>Fact Internet Sales 1MeasureGroupID>

Object>

ClearCache>

Clear Cache for Dimension

<ClearCache xmlns=“http://schemas.microsoft.com/analysisservices/2003/engine&#8221;>

<Object>

<DatabaseID>Adventure Works DW 2008DatabaseID>

<DimensionID>Dim CustomerDimensionID>

Object>

ClearCache>

Clear Cache for partition

<ClearCache xmlns=“http://schemas.microsoft.com/analysisservices/2003/engine&#8221;>

<Object>

<DatabaseID>Adventure Works DW 2008DatabaseID>

<CubeID>Adventure WorksCubeID>

<MeasureGroupID>Fact Internet Sales 1MeasureGroupID>

<PartitionID>Internet_Sales_2001PartitionID>

Object>

ClearCache>

This gives error

Errors in the metadata manager.The object withID of ‘Internet_Sales_2001’, Name of ‘Internet_Sales_2001’ isof type ‘partition’ which does notsupport the requested operation.

But inBOOKS Online ofSQL Server 2008

ms–help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10md_2devguide/html/afad5c39–d4c3–4307–b3b9–a06617da0028.htm

It says that we can clear cache of partition also , but while we try to clear partition cache it gives error.

It sounds me like a bug.

Cheers

Amish Shah

DRILLTHROUGH – Analysis Service

DRILLTHROUGH [MAXROWS Unsigned_Integer]
select mdx statement
Return set of attributes and measures

Its used to get detailed information from a cell of aggregated result
If storage is MOLAP then detailed information will be retrived from the cube. If its ROLAP then information will be retrieved from table of source database

MAXROWS is maximum number of rows that will be returned from source table
IF not specified then all rows will be returned

Cube dimension will are preceded by $ to separate them from measures

following information can be retrieved using drill through

Name(attribute_name)
Returns the name of the specified attribute member.

UniqueName(attribute_name)
Returns the unique name of the specified attribute member.

Key(attribute_name[, N])
Returns the key of the specified attribute member, where N specifies column in the composite key (if any). The default value for N is 1.

Caption(attribute_name)
Returns the caption of the specified attribute member.

MemberValue(attribute_name)
Returns the member value of the specified attribute member.

Translation(attribute_name[, N])
Returns the translated value of the specified attribute member, where N is the language.

CustomRollup(attribute_name)
Returns the custom rollup expression of the specified attribute member.

CustomRollupProperties(attribute_name)
Returns the custom rollup properties of the specified attribute member.

UnaryOperator(attribute_name)
Returns the unary operator of the specified attribute member.

Lets take an example

We want row detail for fourth quarter of year 2004

DRILLTHROUGH maxrows 1000
SELECT ([Date].[Calendar].[Calendar Quarter].&[2002]&[4]
)
ON 0
FROM [Adventure Works]
RETURN
[$Date].[Date] AS CALENDAR_DATE
,KEY([$Product].[Model Name]) AS PRODUCTKEY
,NAME([$Employee].[Employee]) AS EMPLOYEENAME
,UNIQUENAME([$Employee].[Employee]) AS EMPLOYEE_UNIQUE_NAME
,CAPTION([$Employee].[Employee]) AS EMPLOYEE_CAPTION
,MEMBERVALUE([$Employee].[Employee]) AS EMPLOYEE_MEMBERVALUE
,[$Geography].[Country] AS COUNTRY
,[Reseller Sales].[Reseller Sales Amount] AS RESELLER_SALES_AMOUNT
,[Reseller Sales].[Reseller Tax Amount] AS RESELLER_TAX_AMOUNT
,[Reseller Sales].[Reseller Standard Product Cost] AS
RESELLER_STANDARD_PRODUCT_COST