Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips






Learn more about SQL Server tools








Learn more about SQL Server tools


   Got a SQL tip?
            We want to know!

Build a SQL Server Analysis Services Data Dictionary

MSSQLTips author Scott Murray By:   |   Read Comments (8)   |   Related Tips: > Analysis Services Development
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

Description Field


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

Linked Serer Setup


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))= 'Volume_OLAP'
AND CAST([CATALOG_NAME] AS VARCHAR(255)) = 'Volume_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))= 'Volume_OLAP'
AND CAST([CATALOG_NAME] AS VARCHAR(255)) = 'Volume_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.

DataSource Dataset

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.

Tablix Design

Tablix Sorts

Post your data dictionary report to the report server, and it is ready for users to view.

Final Report

Next Steps


Last Update: 3/2/2012


About the author
MSSQLTips author Scott Murray
Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Friday, March 02, 2012 - 7:57:44 AM - Desh Maharaj Read The Tip

This cool. Are there other options. How do you create .ddf files in mssql.


Friday, March 02, 2012 - 8:31:52 AM - Scott Murray Read The Tip

There are many different options available using the linker server and SSAS DMVs and thus you can create just about any report you need.  I am not sure what a DDF file is.


Friday, March 02, 2012 - 12:55:22 PM - Roger falor Read The Tip

Great article!  This could also be the basis for a multi-purpose Data Dictionary, using extended properties.  I'd be inclined to forgo SSRS and create a worksheet with the query results, so the user wouldn't need to bounce around to different apps.


Wednesday, March 07, 2012 - 6:52:31 PM - sandeep Read The Tip

Hi scott.Great Article.Do you know how to create a data dictionary for ssrs db listing out reportnames,columns,datatypes,username and everything possible.I could not find in detail in executionlog3 in report server db.Thanks,


Wednesday, March 07, 2012 - 7:22:32 PM - Scott Murray Read The Tip

To get to the details of report server, you actually have to drill into the reportserver database tables including the catalog, datasets, and datasources tables.  Some of the fields must be parsed out because of the data they contain (XML).    This blog may get you started.http://social.msdn.microsoft.com/Forums/en/sqlreportingservices/thread/0cbb8997-c54c-4de8-87e8-529dc6e2b017


Thursday, March 08, 2012 - 1:52:23 PM - sandeep Read The Tip

Thanks Scott


Sunday, December 16, 2012 - 7:45:43 PM - sqlraf Read The Tip

Scott, this is great.  But what about the other way around?  How can one populate these Description properties into the SSAS from an external source automatically?

 

Thank you!


Monday, December 17, 2012 - 6:22:39 AM - Scott Read The Tip

You will have to generate the XMLA via some sort of script.



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 

Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.