Being a business intelligence consultant, I like to spend my time designing data warehouses, ETL scripts and OLAP cubes. An unfortunate consequence of this is having to write the documentation that goes with the fun techy work. So it got me thnking, is there a slightly more fun techy way of automating the documentation of OLAP cubes…
There are some good tools out there such as BI Documenter, but I wanted a way of having more control over the output, and also automating it further so that you don’t have to run an overnight build of the documentation.
I found a great article by Vincent Rainardi describing some DMVs (Dynamic Management Views) available in SQL 2008 which got me thinking, why not just build a number of SSRS reports calling these DMVs, which would then dynamically create the cube structure documentation in real time whenever the report rendered..
This post is the first in a 3 part set which will demonstrate how you can use these DMVs to automate the SSAS cube documentation and user guide.
- Part 1 – Creating the DMV stored procs
- Part 2 – Create the SSRS reports
- Part 3 – Use spatial data and maps to create a star schema view
- Download Source Code
UPDATE: I presented a 1 hour session at SQLBits 8 covering all of this work, you can watch the video here.
There’s a full list of DMVs available in SQL 2008 R2 on the msdn site.
The primary DMVs that are of interest are:
DMV | Description |
MDSCHEMA_CUBES | Lists the cubes in an SSAS database |
MDSCHEMA_MEASUREGROUPS | Lists measure groups |
MDSCHEMA_DIMENSIONS | Lists dimensions |
MDSCHEMA_LEVELS | Dimension attributes |
MDSCHEMA_MEASUREGROUP_DIMENSIONS | Enumerates dimensions of measure groups |
MDSCHEMA_MEASURES | Lists measures |
When querying DMVs we can use SQL style SELECT statements, but executed against the cube in a DMX window.
SELECT * FROM $SYSTEM.MDSCHEMA_CUBES
This returns a dataset like any other SQL query.
We can even enhance it with DISTINCT and WHERE clauses, although they are more restricted than basic SQL. One of the main limitations is the lack of a JOIN operator. A number of the queries that I’ll perform below need to use JOIN, so to get around this I wrap up each query in an SQL OPENROWSET command, executed against a SQL database with a linked server to the cube. This enables me to perform JOINs using queries such as
SELECT * FROM OPENQUERY(CubeLinkedServer, 'SELECT * FROM $SYSTEM.MDSCHEMA_MEASUREGROUP_DIMENSIONS') mgd INNER JOIN OPENQUERY(CubeLinkedServer, 'SELECT * FROM $SYSTEM.MDSCHEMA_MEASUREGROUPS') mg ON mgd.XXX = mg.XXX
etc.
I’m therefore going to create a number of stored procs to wrap up this functionality, the SSRS reports can then just call the procs.
Within BIDS, every item (cube, measure group, measure, dimension, attribute, hierarchy, KPI, etc.) has a description in the properties pane which is a multi-line free text property. These are exposed by the DMVs, so I’m going to make use of them and bring them out in the reports. This allows you to create the descriptions within BIDS as you’re developing the cube, meaning they’re version controlled and always in sync with the code.
I should also point out that I’m using SQL Server 2008 R2. All of the queries below will work with SQL 2008, but I want to use the spatial report functionality of SSRS 2008 R2 to generate dynamic star schema visualisations, which is only supported in R2.
In this post I’ll script out the stored procedures used as the basis of the documentation. In my next post I’ll put these into SSRS reports.
Lets get started.
Firstly we need to create our linked server. This script will create a linked server called CubeLinkedServer pointing to the Adventure Works DW 2008R2 OLAP database on the local server.
EXEC master.dbo.sp_addlinkedserver @server = N'CubeLinkedServer', @srvproduct=N'MSOLAP', @provider=N'MSOLAP', @datasrc=N'(local)', @catalog=N'Adventure Works DW 2008R2'
You’ll have to set up the security according to your requirements. So now lets start creating the source procs.
The first proc lists all of the cubes. The MDSCHEMA_CUBES DMV returns not only cubes, but also dimensions, I’m filtering it to only return cubes by specifying CUBE_SOURCE=1.
CREATE PROCEDURE [dbo].[upCubeDocCubes] (@Catalog VARCHAR(255) = NULL ) AS SELECT * FROM OPENQUERY(CubeLinkedServer, 'SELECT * FROM $SYSTEM.MDSCHEMA_CUBES WHERE CUBE_SOURCE = 1') WHERE CAST([CATALOG_NAME] AS VARCHAR(255)) = @Catalog OR @Catalog IS NULL GO
The next proc returns all measure groups found within a specified cube.
CREATE PROCEDURE [dbo].[upCubeDocMeasureGroupsInCube] (@Catalog VARCHAR(255) ,@Cube VARCHAR(255) ) AS SELECT * FROM OPENQUERY(CubeLinkedServer, 'SELECT * FROM $SYSTEM.MDSCHEMA_MEASUREGROUPS ') WHERE CAST([CATALOG_NAME] AS VARCHAR(255)) = @Catalog AND CAST([CUBE_NAME] AS VARCHAR(255)) = @Cube GO
This next proc returns a list of measures within a specified measure group.
CREATE PROCEDURE [dbo].[upCubeDocMeasuresInMeasureGroup] (@Catalog VARCHAR(255) ,@Cube VARCHAR(255) ,@MeasureGroup VARCHAR(255) ) AS SELECT * FROM OPENQUERY(CubeLinkedServer, 'SELECT * FROM $SYSTEM.MDSCHEMA_MEASURES WHERE [MEASURE_IS_VISIBLE]') WHERE CAST([CATALOG_NAME] AS VARCHAR(255)) = @Catalog AND CAST([CUBE_NAME] AS VARCHAR(255)) = @Cube AND CAST([MEASUREGROUP_NAME] AS VARCHAR(255)) = @MeasureGroup GO
The following proc queries all dimensions available within a specified cube. I’m filtering using the DIMENSION_IS_VISIBLE column to only show visible dimensions.
CREATE PROCEDURE [dbo].[upCubeDocDimensionsInCube] (@Catalog VARCHAR(255) ,@Cube VARCHAR(255) ) AS SELECT * FROM OPENQUERY(CubeLinkedServer, 'SELECT * FROM $SYSTEM.MDSCHEMA_DIMENSIONS WHERE [DIMENSION_IS_VISIBLE]') WHERE CAST([CATALOG_NAME] AS VARCHAR(255)) = @Catalog AND CAST([CUBE_NAME] AS VARCHAR(255)) = @Cube GO
Then we can query all available attributes within a dimension. This DMV returns a bitmask field (LEVEL_ORIGIN) which defines whether the attribute is a key, attribute or hierarchy. I’m using bitwise AND (&) to split this into three seperate fields for ease of use. I’m also filtering out invisible attributes, as well as those with a level of 0. Level 0 is the [All] member of any attribute, which we can ignore for this purpose.
CREATE PROCEDURE [dbo].[upCubeDocAttributesInDimension] (@Catalog VARCHAR(255) ,@Cube VARCHAR(255) ,@Dimension VARCHAR(255) ) AS SELECT * , CASE WHEN CAST([LEVEL_ORIGIN] AS INT) & 1 = 1 THEN 1 ELSE 0 END AS IsHierarchy , CASE WHEN CAST([LEVEL_ORIGIN] AS INT) & 2 = 2 THEN 1 ELSE 0 END AS IsAttribute , CASE WHEN CAST([LEVEL_ORIGIN] AS INT) & 4 = 4 THEN 1 ELSE 0 END AS IsKey FROM OPENQUERY(CubeLinkedServer, 'SELECT * FROM $SYSTEM.MDSCHEMA_LEVELS WHERE [LEVEL_NUMBER]>0 AND [LEVEL_IS_VISIBLE]') WHERE CAST([CATALOG_NAME] AS VARCHAR(255)) = @Catalog AND CAST([CUBE_NAME] AS VARCHAR(255)) = @Cube AND CAST([DIMENSION_UNIQUE_NAME] AS VARCHAR(255)) = @Dimension GO
The next proc returns measure groups with their associated dimensions. We have to join two DMVs together in order to get the description columns of both the dimension and measure group.
CREATE PROCEDURE [dbo].[upCubeDocMeasureGroupsForDimension] (@Catalog VARCHAR(255) ,@Cube VARCHAR(255) ,@Dimension VARCHAR(255) ) AS SELECT mgd.* , m.[DESCRIPTION] FROM OPENQUERY(CubeLinkedServer, 'SELECT [CATALOG_NAME] , [CUBE_NAME] , [MEASUREGROUP_NAME] , [MEASUREGROUP_CARDINALITY] , [DIMENSION_UNIQUE_NAME] FROM $SYSTEM.MDSCHEMA_MEASUREGROUP_DIMENSIONS WHERE [DIMENSION_IS_VISIBLE]') mgd INNER JOIN OPENQUERY(CubeLinkedServer, 'SELECT [CATALOG_NAME] ,[CUBE_NAME] ,[MEASUREGROUP_NAME] ,[DESCRIPTION] FROM $SYSTEM.MDSCHEMA_MEASUREGROUPS') mg ON CAST(mgd.[CATALOG_NAME] AS VARCHAR(255)) = CAST(mg.[CATALOG_NAME] AS VARCHAR(255)) AND CAST(mgd.[CUBE_NAME] AS VARCHAR(255)) = CAST(mg.[CUBE_NAME] AS VARCHAR(255)) AND CAST(mgd.[MEASUREGROUP_NAME] AS VARCHAR(255)) = CAST(mg.[MEASUREGROUP_NAME] AS VARCHAR(255)) WHERE CAST(mgd.[CATALOG_NAME] AS VARCHAR(255)) = @Catalog AND CAST(mgd.[CUBE_NAME] AS VARCHAR(255)) = @Cube AND CAST(mgd.[DIMENSION_UNIQUE_NAME] AS VARCHAR(255)) = @Dimension GO
The next proc is similar to the above, but the opposite way around. It returns all dimensions that are related to a measure group.
CREATE PROCEDURE [dbo].[upCubeDocDimensionsForMeasureGroup] (@Catalog VARCHAR(255) ,@Cube VARCHAR(255) ,@MeasureGroup VARCHAR(255) ) AS SELECT mgd.* , d.[DESCRIPTION] FROM OPENQUERY(CubeLinkedServer, 'SELECT [CATALOG_NAME] ,[CUBE_NAME] ,[MEASUREGROUP_NAME] ,[MEASUREGROUP_CARDINALITY] ,[DIMENSION_UNIQUE_NAME] ,[DIMENSION_CARDINALITY] ,[DIMENSION_IS_VISIBLE] ,[DIMENSION_IS_FACT_DIMENSION] ,[DIMENSION_GRANULARITY] FROM $SYSTEM.MDSCHEMA_MEASUREGROUP_DIMENSIONS WHERE [DIMENSION_IS_VISIBLE]') mgd INNER JOIN OPENQUERY(CubeLinkedServer, 'SELECT [CATALOG_NAME] ,[CUBE_NAME] ,[DIMENSION_UNIQUE_NAME] ,[DESCRIPTION] FROM $SYSTEM.MDSCHEMA_DIMENSIONS WHERE [DIMENSION_IS_VISIBLE]') d ON CAST(mgd.[CATALOG_NAME] AS VARCHAR(255)) = CAST(d.[CATALOG_NAME] AS VARCHAR(255)) AND CAST(mgd.[CUBE_NAME] AS VARCHAR(255)) = CAST(d.[CUBE_NAME] AS VARCHAR(255)) AND CAST(mgd.[DIMENSION_UNIQUE_NAME] AS VARCHAR(255)) = CAST(d.[DIMENSION_UNIQUE_NAME] AS VARCHAR(255)) WHERE CAST(mgd.[CATALOG_NAME] AS VARCHAR(255)) = @Catalog AND CAST(mgd.[CUBE_NAME] AS VARCHAR(255)) = @Cube AND CAST(mgd.[MEASUREGROUP_NAME] AS VARCHAR(255)) = @MeasureGroup GO
The next proc builds a BUS matrix, joining every dimension to its related measure groups. Later we’ll use the SSRS tablix control to pivot this into matrix form.
CREATE PROCEDURE [dbo].[upCubeDocBUSMatrix] (@Catalog VARCHAR(255), @Cube VARCHAR(255) ) AS SELECT bus.[CATALOG_NAME] ,bus.[CUBE_NAME] ,bus.[MEASUREGROUP_NAME] ,bus.[MEASUREGROUP_CARDINALITY] ,bus.[DIMENSION_UNIQUE_NAME] ,bus.[DIMENSION_CARDINALITY] ,bus.[DIMENSION_IS_FACT_DIMENSION] ,bus.[DIMENSION_GRANULARITY] ,dim.[DIMENSION_MASTER_NAME] ,1 AS Relationship FROM OPENQUERY(CubeLinkedServer, 'SELECT [CATALOG_NAME] ,[CUBE_NAME] ,[MEASUREGROUP_NAME] ,[MEASUREGROUP_CARDINALITY] ,[DIMENSION_UNIQUE_NAME] ,[DIMENSION_CARDINALITY] ,[DIMENSION_IS_FACT_DIMENSION] ,[DIMENSION_GRANULARITY] FROM $SYSTEM.MDSCHEMA_MEASUREGROUP_DIMENSIONS WHERE [DIMENSION_IS_VISIBLE]') bus INNER JOIN OPENQUERY(CubeLinkedServer, 'SELECT [CATALOG_NAME] ,[CUBE_NAME] ,[DIMENSION_UNIQUE_NAME] ,[DIMENSION_MASTER_NAME] FROM $SYSTEM.MDSCHEMA_DIMENSIONS') dim ON CAST(bus.[CATALOG_NAME] AS VARCHAR(255)) = CAST(dim.[CATALOG_NAME] AS VARCHAR(255)) AND CAST(bus.[CUBE_NAME] AS VARCHAR(255)) = CAST(dim.[CUBE_NAME] AS VARCHAR(255)) AND CAST(bus.[DIMENSION_UNIQUE_NAME] AS VARCHAR(255)) = CAST(dim.[DIMENSION_UNIQUE_NAME] AS VARCHAR(255)) WHERE CAST(bus.[CATALOG_NAME] AS VARCHAR(255)) = @Catalog AND CAST(bus.[CUBE_NAME] AS VARCHAR(255)) = @Cube GO
Next, in order to make it easier for users to find items within the cube, I’ve created a searching proc which will scour a number of the DMVs for anything containing the search term.
CREATE PROCEDURE [dbo].[upCubeDocSearch] (@Search VARCHAR(255) ,@Catalog VARCHAR(255)=NULL ,@Cube VARCHAR(255)=NULL ) AS WITH MetaData AS ( --Cubes SELECT CAST('Cube' AS VARCHAR(20)) AS [Type] , CAST(CATALOG_NAME AS VARCHAR(255)) AS [Catalog] , CAST(CUBE_NAME AS VARCHAR(255)) AS [Cube] , CAST(CUBE_NAME AS VARCHAR(255)) AS [Name] , CAST(DESCRIPTION AS VARCHAR(4000)) AS [Description] , CAST(CUBE_NAME AS VARCHAR(255)) AS [Link] FROM OPENQUERY(CubeLinkedServer, 'SELECT [CATALOG_NAME], [CUBE_NAME], [DESCRIPTION] FROM $SYSTEM.MDSCHEMA_CUBES WHERE CUBE_SOURCE = 1') WHERE (CAST([CATALOG_NAME] AS VARCHAR(255)) = @Catalog OR @Catalog IS NULL) UNION ALL --Dimensions SELECT CAST('Dimension' AS VARCHAR(20)) AS [Type] , CAST(CATALOG_NAME AS VARCHAR(255)) AS [Catalog] , CAST(CUBE_NAME AS VARCHAR(255)) AS [Cube] , CAST(DIMENSION_NAME AS VARCHAR(255)) AS [Name] , CAST(DESCRIPTION AS VARCHAR(4000)) AS [Description] , CAST(DIMENSION_UNIQUE_NAME AS VARCHAR(255)) AS [Link] FROM OPENQUERY(CubeLinkedServer, 'SELECT [CATALOG_NAME], [CUBE_NAME] , [DIMENSION_NAME], [DESCRIPTION] , [DIMENSION_UNIQUE_NAME] FROM $SYSTEM.MDSCHEMA_DIMENSIONS WHERE [DIMENSION_IS_VISIBLE]') WHERE (CAST([CATALOG_NAME] AS VARCHAR(255)) = @Catalog OR @Catalog IS NULL) AND (CAST([CUBE_NAME] AS VARCHAR(255)) = @Cube OR @Cube IS NULL) AND LEFT(CAST(CUBE_NAME AS VARCHAR(255)),1) <>'$' --Filter out dimensions not in a cube UNION ALL --Attributes SELECT CAST('Attribute' AS VARCHAR(20)) AS [Type] , CAST(CATALOG_NAME AS VARCHAR(255)) AS [Catalog] , CAST(CUBE_NAME AS VARCHAR(255)) AS [Cube] , CAST(LEVEL_CAPTION AS VARCHAR(255)) AS [Name] , CAST(DESCRIPTION AS VARCHAR(4000)) AS [Description] , CAST(DIMENSION_UNIQUE_NAME AS VARCHAR(255)) AS [Link] FROM OPENQUERY(CubeLinkedServer, 'SELECT [CATALOG_NAME], [CUBE_NAME] , [LEVEL_CAPTION], [DESCRIPTION], , [DIMENSION_UNIQUE_NAME] FROM $SYSTEM.MDSCHEMA_LEVELS WHERE [LEVEL_NUMBER]>0 AND [LEVEL_IS_VISIBLE]') WHERE (CAST([CATALOG_NAME] AS VARCHAR(255)) = @Catalog OR @Catalog IS NULL) AND (CAST([CUBE_NAME] AS VARCHAR(255)) = @Cube OR @Cube IS NULL) AND LEFT(CAST(CUBE_NAME AS VARCHAR(255)),1) <>'$' --Filter out dimensions not in a cube UNION ALL --Measure Groups SELECT CAST('Measure Group' AS VARCHAR(20)) AS [Type] , CAST(CATALOG_NAME AS VARCHAR(255)) AS [Catalog] , CAST(CUBE_NAME AS VARCHAR(255)) AS [Cube] , CAST(MEASUREGROUP_NAME AS VARCHAR(255)) AS [Name] , CAST(DESCRIPTION AS VARCHAR(4000)) AS [Description] , CAST(MEASUREGROUP_NAME AS VARCHAR(255)) AS [Link] FROM OPENQUERY(CubeLinkedServer, 'SELECT [CATALOG_NAME], [CUBE_NAME] , [MEASUREGROUP_NAME], , [DESCRIPTION] FROM $SYSTEM.MDSCHEMA_MEASUREGROUPS') WHERE (CAST([CATALOG_NAME] AS VARCHAR(255)) = @Catalog OR @Catalog IS NULL) AND (CAST([CUBE_NAME] AS VARCHAR(255)) = @Cube OR @Cube IS NULL) AND LEFT(CAST(CUBE_NAME AS VARCHAR(255)),1) <>'$' --Filter out dimensions not in a cube UNION ALL --Measures SELECT CAST('Measure' AS VARCHAR(20)) AS [Type] , CAST(CATALOG_NAME AS VARCHAR(255)) AS [Catalog] , CAST(CUBE_NAME AS VARCHAR(255)) AS [Cube] , CAST(MEASURE_NAME AS VARCHAR(255)) AS [Name] , CAST(DESCRIPTION AS VARCHAR(4000)) AS [Description] , CAST(MEASUREGROUP_NAME AS VARCHAR(255)) AS [Link] FROM OPENQUERY(CubeLinkedServer, 'SELECT [CATALOG_NAME], [CUBE_NAME] , [MEASURE_NAME], [DESCRIPTION], , [MEASUREGROUP_NAME] FROM $SYSTEM.MDSCHEMA_MEASURES WHERE [MEASURE_IS_VISIBLE]') WHERE (CAST([CATALOG_NAME] AS VARCHAR(255)) = @Catalog OR @Catalog IS NULL) AND (CAST([CUBE_NAME] AS VARCHAR(255)) = @Cube OR @Cube IS NULL) AND LEFT(CAST(CUBE_NAME AS VARCHAR(255)),1) <>'$' --Filter out dimensions not in a cube ) SELECT * FROM MetaData WHERE @Search<>'' AND ([Name] LIKE '%' + @Search + '%' OR [Description] LIKE '%' + @Search + '%' ) GO
We can now use these procs to form the basis of a number of SSRS reports which will dynamically query the DMVs to generate the SSAS cube documentation. I’ll be covering this stage in my next post.
- Part 1 – Creating the DMV stored procs
- Part 2 – Create the SSRS reports
- Part 3 – Use spatial data and maps to create a star schema view
- Download Source Code
This is very interesting stuff. Can’t wait for the other 2 parts of this
Here are this and some other articles on SSAS Dynamic Management Views: http://ssas-wiki.com/w/Articles#DMV_.28Dynamic_Management_View.29
Thanks Sam, some great articles in that list, very useful.
Hi Alex!
A good post, but a little question when specifiying the linked server do you always have to specify the Catalog? Because then it’s not necessary to have that as a parameter is it?
I tried to create a linked server without specifiying the Catalog and it took the first one. While I would imagine that I would get the option to choose the Catalog since it is a part of a parameter.
Any thought about that?
Regards Shakir
Hi Shakir
You’re right that at the moment the catalog parameter doesn’t make that much sense. When I first started this project I intended the catalog to be selectable, however I came across the same problem of the catalog having to be specified in the linked server. I kept the parameter in though for future enhancements. for example you could drop and recreate the linked server dynamically if required.
Alex
hi,
thanks for posting this solution.
I have been searching for a way to track measures from the cube to the source tables they originate from. Your solution only lacks the ability to identify the source table/column and from what I can tell, the system views doesn’t supply this information, but it is possible to accomplish this by querying the solution (.cube) file. Is there a better way to do this?
I don’t think there’s any easy way to do this – without querying the cube file or something similarly complex. There are certainly no DMVs that I’m aware of which provide a lineage of the data source.
If you find a neat way of doing it then please post here – I’d be very interested if you figure it out.
Alex
Hi again
The process is a bit cumbersome, but the required steps can probably be scripted.
There are two files of interest in the solution, the .cube and .partitions files.
So far, I have only investigated the .cube file.
Get latest cube definition file (.cube) from source control and store its contents as XML type in the database.
I created a table, dbo.Cube, in which I store the cube definition as XML.
Then I run the query below to get the desired information. Combined with the DMV queries you have provided, it’s just what I need.
;WITH XMLNAMESPACES( — only one namespace was required for my queries
‘xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine”‘ AS A
, DEFAULT ‘http://schemas.microsoft.com/analysisservices/2003/engine’
)
SELECT
x.m.value(‘(ID)[1]’, ‘varchar(64)’) AS ID
,x.m.value(‘(Name)[1]’, ‘varchar(64)’) AS Name
,y.n.value(‘(ID)[1]’, ‘varchar(64)’) AS MeasureID
,y.n.value(‘(Name)[1]’, ‘varchar(64)’) AS MeasureName
,y.n.value(‘(DataType)[1]’, ‘varchar(64)’) AS MeasureDataType
,y.n.value(‘(Source/DataType)[1]’, ‘varchar(64)’) AS MeasureSourceDataType
,y.n.value(‘(Source/Source/TableID)[1]’, ‘varchar(64)’) AS SourceTableID
,y.n.value(‘(Source/Source/ColumnID)[1]’, ‘varchar(64)’) AS SourceColumnID
,x.m.value(‘(../../Name)[1]’, ‘varchar(64)’) AS Cube
FROM (SELECT Definition FROM DBO.Cube) AS P
CROSS APPLY Definition.nodes(‘/Cube/MeasureGroups/MeasureGroup’) x(m)
CROSS APPLY Definition.nodes(‘/Cube/MeasureGroups/MeasureGroup/Measures/Measure’) y(n)
WHERE x.m.value(‘(ID)[1]’, ‘varchar(64)’)=y.n.value(‘(../../ID)[1]’, ‘varchar(64)’)
Good work! I’ll take a more detailed look when I get a few minutes spare.
Thanks for sharing
I discovered within the $system DMVs the relational source of the multidimensional data.
The MDSCHEMA_PROPERTIES rowset contains Information about the relational source, specifically the column PROPERTY_ATTRIBUTE_HIERARCHY_NAME. The discription within BOL is ‘The name of the attribute hierarchy sourcing this property.’
Maybe you guys can check it out and let me know whether this is it. Also could you give me a heads up as to what PROPERTY_CARDINALITY means?
Thanks.
See here :
http://msdn.microsoft.com/en-us/library/ms126309.aspx
I can’t find any relational source field details in that DMV? The PROPERTY_ATTRIBUTE_HIERARCHY_NAME just shows which dimension attribute is used in the selected hierarchy, not where it comes from in the underlying source database.
PROPERTY_CARDINALITY is an interesting one. BOL states that it’s the “The cardinality of the property”, which is less than helpful. From a quick check of a few cubes, it seems to represent the grouping behavior of the attribute; encourage (MANY) or discourage (ONE).
If anyone has any further details on this, please speak up!
Alex
Thank you for another brilliant article, Alex!
There is a very handy documentation of these DMVs on MSDN: http://msdn.microsoft.com/en-us/library/ms126079.aspx
According to that documentation cardinality is actually the number of members within that object or Key attribute of the object. Quite useful actually!
Thanks for the link,
Alex
Hi, I want to prepare a document for all measure group and dimension attributes in cube with the actual SOURCE VIEW and COLUMN names.Could you please help me with this.
Thanks
Hi Jerry. This can’t be done using the DMVs, they don’t provide any access to the data source views.
You could access this from the xmla definition of the cube, or from the Microsoft.AnalysisServices object, but both of these would require some C# so not as simple as querying a DMV I’m afraid.
Hi Alex, thanks for your quick response,I wanted to know whether it is possible through MDX queries?
By any chance do you have any code similar to my requirement?
Thanks
No, I’m afraid there’s no way to get this using MDX, you would need to use C# or similar.
Regards
Alex
Oh k..Thanks for ur help! 🙂
Hi Alex,
I really liked your article and I found it is very useful. What I would like to know is if there is possibility to get dependency diagram for calculated measures? To get an tree starting from one parent measure to all its children measures which explicitly or implicitly depend on starting parent.
Not that i’m aware of I’m afraid. This would be difficult due to scoped assignments. On a very basic level you can get the calculations from the DSVs, so you could scan these for anything containing a measure name. But that wouldn’t include anything done in scoped assignments. MDX calculations are not always that straightforward, so you’d have to do some pretty complex parsing of the scripts to get this to work.
Thank you for your fast reply. It is clear to me what kind of custom application it needs to be implemented to support this requirement.
Hi Alex,
This website has been really useful for many people thanks for that..
My query is related to a Named set in Cube..
I have dimension and fact table as follows-
Dim X Fact Y—>Some QTy
-3 -3 10
-2 0 20
-1 -1 30
0 0 40
1 1 50
2 2 60
3 1 70
There is a linkage between Dim X and Fact Y (ignore the order )
Result:
It will combine and get the values, for example in this case there are two values for ‘0’and ‘1’ so under 0 and 1 we will see 20 n 40 ..and under 1 we will see 50 n 70 ..this is implemented!
My question is: I need to create a named set to select “Next 3 values” (i.e from Dim X 0,1,2) or “Past 3 and Next 3 values”(i.e Dim X -3,-2-,1,0,1,2) like this I have diff named sets to be created.. this dimension X is just number..
Could you please help me the MDX query for named set as to how to do it..I am new to MDX so not aware of much functions
Thanks
*Sorry looks like table values did not display like I thought..re-arranging them-
Dim X =-3 -2 -1 0 1 2 3
Fact Y =-3 0 -1 0 1 2 1
|
Some Qty = 10 20 30 40 50 60 70
Thanks
Hi Jerry
This is a little too detailed and specific to be able to look at without having access to the cube to examine it in more detail. Can you send me a backup of the cube? Drop me an email through the website contact form and I can give you FTP or dropbox details.
Alex
Hi Alex,
I tried with few functions in MDX and found the result..goes like this for few named sets and can be created for different sets
//Next 26–starts from ‘0’
subset(filter([Dimension_Name].[Hierarchy_Name].[Level],
[Dimension_Name].[Hierarchy_Name].MemberValue >=0),0,26)
//Past 26 and Next 26
subset(filter([Dimension_Name].[Hierarchy_Name].[RN],
[Dimension_Name].[Hierarchy_Name].MemberValue >= -25),0,51)
//Past and next 26
filter([Dimension_Name].[Hierarchy_Name].[Level],
[Dimension_Name].[Hierarchy_Name].MemberValue =0),0,26)
Filter evaluates the condition and subset uses the positions to display the results
Sorry for “Past and Next 26” code was copied half..below works correct
//Past and next 26
filter([Dimension_Name].[Hierarchy_Name].[Level],
[Dimension_Name].[Hierarchy_Name].MemberValue =0),0,26)
Alex there is some problem again ..I donno why code is nt showing completely..I’l divide it in 2 parts and send
Note: Plus(+) mark should be included in the code between both codes
1)Past
filter([Dimension_Name].[Hierarchy_Name].[Level],
[Dimension_Name].[Hierarchy_Name].MemberValue =0),0,26)
+
2)Next 26
subset(filter([Dimension_Name].[Hierarchy_Name].[Level],
[Dimension_Name].[Hierarchy_Name].MemberValue >=0),0,26)
Ok Alex there is definitely something wrong while uploading the code..
Please ignore past and next 26 code its getting displayed wrong..
For past filter condition shd be
(MemberValue < 0)
Hi Alex,
I have written a scope statement (which calcualtes the sum of child and displays on parent for a particular case) this scope is written on a particular dimension and a hierarchy..and I have total 6 dimensions and 30+ hierarchies..
Question is ,Is it possible to make the scope statement work on all the dimensions and hierarchies or I need to repeat the same scope for all dimension and hierarchies?
Let me know if there is any way.
Thanks
Hi Jerry – sorry for delay in replying, I didn’t see the comments.
No I’m not aware of making a scope statement work on all hierarchies in all dimensions. You need to cope with each one individually as far as I know. Scope statements are awesome, but can get frustrating when trying to work with too many at the same time!
Hi Alex,
I took care of my previous query in different manner, I have some other query now:
Ist regarding using the hierarchy in filter conditions of a Report:
Measures\Week W0 w1 w2 w3 w4 w5 w6
X 10 0 0 0 0 0 0
y 2 1 0 5 0 0 0
Running sum(X+Y) 12 13 13 18 18 18 18
Now I have hierarchy to filter the Weeks I want to see..if i select week from W0-W6 there is no issue with the running sum but the Problem is when I select particular buckets like from W3-W8,then the running sum of (X+Y) will be
Measures\Week w3 w4 w5 w6
Running sum(X+Y) 5 5 5 5
in this case it ignores the values from previous weeks..
Could you please suggest me if there is anyway to select values from W0 even when i filter weeks from W3-W8?
Thanks
Hi Jerry
How you resolve this will depend on how your calculations are written, and the exact MDX used in the query. i.e. filtering the weeks using the WHERE clause or using a FROM subselect, or something more complex. I’d need to look in more detail to advise.
You really do have too many questions to answer one at a time in blog comments – I strongly recommend that you engage an MDX consultant to help get things working as you want.
Regards
Alex
Hey Alex,
I have gone through your blogs on cube meta data. it is really cool and awesome.
I am trying to get the partitions metadata.
I am working on developing cube, where I am automating all the possible things.
I am struck at getting the partitions of a measure group.
Can you help me to get the partition names that exist for a measure group?
Thanks in Advance.
Karna
Hi Karna
I’m afraid there are no DMVs available that can access partition information for measure groups.
Your best bet if you need to automate it is to write some C# and access it using AMO. There is a Partitions collection within the Microsoft.AnalysisServices.MeasureGroup object which should give you everything you need.
Regards, Alex
HI,
Thanks for the Awesome post.
I have a doubt in the Bus Metrics Calculation.
How to know or display the REFERENCED relationships between the dimension and the Measure Group. ?
Good article.
I have an Requirement to show ” Report wise -Cube name,Dimension ,Measure groups and Measure”
How to achieve above scenario.
This doesn’t make sense, measures and dimensions are a many to many link, so can’t really be shown in a single table like that.
But either way, the article has a link to the DMVs available from SSAS, which will give you all the queries to get the available metadata.
Regards
Alex
Hi Alex,
thanks for the brilliant piece of work. It has helped me to create a very efficient cube document. The place where I am stuck is that we have different cubes in different catalog on the server. When I run the query SELECT *
FROM $SYSTEM.MDSCHEMA_CUBES
WHERE CUBE_SOURCE = 1
it gives only the cubes of the one catalog. I want to show on my first page all the catalogs and their cube.
I tried to join the above query with $system.dbschema_catalogs DMV but still it gives cubes from one catalog. My query is like this :
SELECT *
FROM OPENQUERY(CubeLinkedServer,
‘SELECT *
FROM $SYSTEM.MDSCHEMA_CUBES
WHERE CUBE_SOURCE = 1’) a
inner join
OPENQUERY(CubeLinkedServer,
‘select [CATALOG_NAME],[DATE_MODIFIED] from $system.dbschema_catalogs’) b
ON CAST(a.[CATALOG_NAME] AS VARCHAR(255)) = CAST(b.[CATALOG_NAME] AS VARCHAR(255))
Do you know if there is any way to show all catalogs and cubes on the first page of the report ?
Thanks & Regards,
Diwakar
I know this is an old thread, but this comes useful to me every now and then, just wanted to post a small extension to the code shared by Morten earlier – thank you very much, Morten, it works a treat! Naturally, one can use PowerShell or any other XML manipulation code for this – the structure is quite clear from the SELECT statement provided by Morten.
/*NB! Make sure you run this script on the SQL Instance installed on your Dev machine to be able to access the file*/
DROP TABLE IF EXISTS #MyCube
CREATE TABLE #MyCube (CubeDefinition XML)
BULK INSERT #MyCube
/*This is the path to the definition of your UDM cube in the SSAS project – the file with .cube extension*/
FROM ‘C:\Git\cubes\TS_DPL\TS_DPL\TransactionalServices.cube’
WITH (ROWTERMINATOR = ‘\0’,
CODEPAGE = ‘ACP’)
;WITH XMLNAMESPACES(‘xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine”‘ AS A
, DEFAULT ‘http://schemas.microsoft.com/analysisservices/2003/engine’
)
SELECT
x.m.value(‘(ID)[1]’, ‘varchar(64)’) AS ID
,x.m.value(‘(Name)[1]’, ‘varchar(64)’) AS Name
,y.n.value(‘(ID)[1]’, ‘varchar(64)’) AS MeasureID
,y.n.value(‘(Name)[1]’, ‘varchar(64)’) AS MeasureName
,y.n.value(‘(DataType)[1]’, ‘varchar(64)’) AS MeasureDataType
,y.n.value(‘(Source/DataType)[1]’, ‘varchar(64)’) AS MeasureSourceDataType
,y.n.value(‘(Source/Source/TableID)[1]’, ‘varchar(64)’) AS SourceTableID
,y.n.value(‘(Source/Source/ColumnID)[1]’, ‘varchar(64)’) AS SourceColumnID
,x.m.value(‘(../../Name)[1]’, ‘varchar(64)’) AS Cube
FROM (SELECT CubeDefinition FROM #MyCube) AS P
CROSS APPLY CubeDefinition.nodes(‘/Cube/MeasureGroups/MeasureGroup’) x(m)
CROSS APPLY CubeDefinition.nodes(‘/Cube/MeasureGroups/MeasureGroup/Measures/Measure’) y(n)
WHERE x.m.value(‘(ID)[1]’, ‘varchar(64)’)=y.n.value(‘(../../ID)[1]’, ‘varchar(64)’)
This is a great set of articles, thank you for creating such helpful information. I do have a question, as I was going through my cubes and adding descriptions for all my measures I got stuck on my Calculated Measures. They are all listed in $SYSTEM.MDSCHEMA_MEASURES but have not found a way to get to their properties. I’m in Data Tools 2015. I’m not able to write to this table either. Wondering if there is a way