15 October 2014

Working with METADATA (Part I)

Why not give to the user of SSAS cubes from your DW solution more than the value of a metric?! Why can’t you give them a description of the metric, or even its calculation formula?! And for developers, if you had the chance to see what impact that a column in the relational database that feeds the DW change, or even which metrics depends that fantastic calculated metric that we have in your MDX?! And for managers, what is the importance of having a report with all OLAP model of the DW solution implemented in its structure?! To answer all these questions was implemented a solution, regardless the company structure and software versions, which links several “worlds” in one place. The process involved creating a database that gathers information from both relational and analytical databases. To enrich this database, it was necessary to develop several C# scripts based on OLTP information and OLAP METADATA, as well as information entered by the user, information complementary to that available. The sources are:

  • OLTP system tables (like syscolumns);

  • DMVs DMX, to extract information like MDX scripts from metrics;

  • AMO.net to read and write SSAS METADATA;

  • CSV files to export and import input information.

With information of all sources, it’s possible to create the relationship between databases objects, and even create relationships within the same type of object, as for example a MDX calculated metrics. We can easily identify that the calculated metric C depends on A and B. With all the information together in one place, we can deliver the METADATA information. In our solution we use two:

  • Reports. Availability of information in a structured OLAP report with the desired objects;

  • Additional Actions in Excel. This tool allows you to view one type of action built in SSAS cubes to provide information such as a description of a metric. More information in: https://www.artisconsulting.com/blogs/greggalloway/Lists/Posts/Post.aspx?ID=17

In Part II we will demonstrate some of the methods used to obtain information from METADATA.

       Rui Valente
  Associate Manager
Solutions Development