Problem
Your SQL Server Analysis Services cube (All tips | tutorial) has a large number of dimensions, attributes, hierarchies, and measures, but the end users are not sure of the meaning of some of the items. How can you generate a data dictionary with a SQL Server Reporting Services report which describes these objects? Check out this tip to learn more.
Solution
In order to generate a single source, meta-data dictionary, the first step is to be sure to complete the description field in your Cube for each measure, hierarchy, dimension and attribute as shown in the figure below. You many want to note within the description which attributes or measures are not visible, so you can filter those items in your final report. Furthermore, be sure the description fields are not just a repeat of the name and include an appropriate level of detail, such as source, valid values, valid time frames or suggestions for further detail.
Setup Descriptions for SQL Server Analysis Services Objects
Create a SQL Server Linked Server for Centralized Reporting
Once the descriptions fields are populated, the next step is to create a linked server pointing to our SSAS Database. Although not necessary, this linked server allows for easy access from a central metadata repository / database. A linked server can be created using either the following commands or through the SQL Server Management Studio interface.
EXEC @server = N'CUBEDLINKEDSERVER', -- name of linked server
@srvproduct=N'MSOLAP', @provider=N'MSOLAP', @datasrc=N'OlapServer', -- SSAS Server @catalog=N'OlapDatabase' -- SSAS database
Query the SQL Server Analysis Services Dynamic Management Views
Now that the linked server is in place, queries can be run against the SSAS Multidimensional database using a set of SSAS Dynamic Management Views (DMVs) which became available in SSAS 2008. The DMV’s are scarcely documented in Books Online, but a few sites including: http://www.ssas-info.com/analysis-services-faq/80-ssas-2008-dmvs/1342-analysis-services-2008-systemmdschema-dmvs- and http://dwbi1.wordpress.com/2010/01/01/ssas-dmv-dynamic-management-view/ have some details about these new DMV’s. MSDN is also a good reference point.
In particular, we will use the $SYSTEM.MDSCHEMA_MEASURES DMV to retrieve measure descriptions and the $SYSTEM.MDSCHEMA_LEVELS DMV to retrieve dimension/attribute description.
We will create the following view to combine the measure and attribute descriptions in one dataset:
CREATE VIEW [Volume].[Volume_EDW_CUBE_Data_Dictionary] AS -- Retrieve Dimensions and Hierarchy SELECT CUBE_NAME, CATALOG_NAME, REPLACE(REPLACE(CAST(DIMENSION_UNIQUE_NAME AS VARCHAR(100)),'[',''),']','') AS Dim_Name, LEVEL_NAME AS Attribute_Name, LEVEL_UNIQUE_NAME AS Dimension_Attribute_Level, LEVEL_CAPTION, DESCRIPTION AS Description FROM OPENQUERY(CubeLinkedServer, 'SELECT * FROM $SYSTEM.MDSCHEMA_LEVELS WHERE [LEVEL_NUMBER]>0 AND [LEVEL_IS_VISIBLE]') WHERE CAST(CUBE_NAME AS VARCHAR(255))= 'Population_OLAP_OLAP' AND CAST([CATALOG_NAME] AS VARCHAR(255)) = 'Population_OLAP_OLAP' UNION ALL -- Retrieve Measures SELECT CUBE_NAME, CATALOG_NAME, MEASURE_UNIQUE_NAME, MEASURE_NAME, MEASURE_UNIQUE_NAME, MEASURE_CAPTION, Case WHEN CAST(DESCRIPTION AS VARCHAR(200)) ='' THEN 'Calc: ' + CAST(EXPRESSION AS VARCHAR(500)) ELSE DESCRIPTION END AS DESCRIPTION FROM OPENQUERY(CubeLinkedServer, 'SELECT * FROM $SYSTEM.MDSCHEMA_MEASURES') WHERE CAST(CUBE_NAME AS VARCHAR(255))= 'Population_OLAP' AND CAST([CATALOG_NAME] AS VARCHAR(255)) = 'Population_OLAP'
Creating the SQL Server Reporting Services Report
The view we just created now becomes our Dataset Source in SQL Server Reporting Services. You can create a new SSRS Report, add a Data source and then create the following dataset. For detailed steps on setting up the SQL Server Reporting Services report check out this tutorial.
Next in the design tab, add a Tablix with this dataset as the source, drag the following fields onto the Tablix and last add the following sorts.
Post your data dictionary report to the report server, and it is ready for users to view.
Next Steps
- Data dictionaries are not just for OLTP databases. This tip is a great example of setting up your data dictionary and retrieving the data.
- Just like with any data dictionary, the data needs to be updated with changes. So as the dimensions and measures in your cubes change, be sure to description field for the object.
- Two additional web blogs which reference the SSAS DMV’s are:
I have a passion for crafting Business Intelligence Solutions for my user groups. My experience includes almost 15 years of SQL Server involvement with the last 12 years focused specifically on Business Intelligence, SharePoint, OLAP, SSRS, and Decision Support solutions. Currently, I am a Business Intelligence Architect in the healthcare industry, and I also teach database and analytics classes for Kennesaw State University, Southern New Hampshire University, and Reinhardt University. My education includes an MBA and an undergraduate in Accounting (yes I am a reformed accountant!), both from Kennesaw State University. I enjoy every day by trying to grow my faith and spend precious time with my family. I have been happily married to my wife of over 20 years, and we have two teenagers one who we home school with the help of a University Model School, Cornerstone Prep in Acworth, GA (cornerstoneprep.org). Our other child is a Construction Management major at KSU’s Southern Poly / Marietta campus. We are a soccer and Cross Country (XC) family who play, coach, and referee soccer or run for fun most every day. For several years, our family has volunteered (and played with the dogs and cats) at Etowah Valley Humane Society in Cartersville, GA.
- MSSQLTips Awards: Champion (100+tips) – 2016 | Author of the Year – 2015 | Author Contender – 2014, 2016-2021