In this article I want to go through the design specifications and explain the technologies I used in the application and the features. I had a lot of fun building the application and had some inspiration from my earlier UI(C#) development days. I wanted to grab information from the SSAS tabular model with AMO (C#) and use technologies like WPF forms and the Entity Framework to create the user experience. My goal is to work with this design and hope to get some insight from the community for additions and changes that could make this a useful tool for SSAS documentation. I’m currently making the modifications for this tool to work with the SSAS Multidimensional model as well so I’ll keep you posted with those changes.
The following are the technologies used:
- SQL Server 2012 Database Engine SP1
- SQL Server Analysis Service BISM Tabular Model SP1
- .NET Framework 4.5
- ENTITY FRAMEWORK 5.0
- WPF (Windows Presentation Foundation)
- EXCEL 2013
- Visual Studio 2012
The SSAS Tabular Design
The intentions of the application were to grab current SSAS Tabular Model objects and be able to document them. With that we can have a better understanding of these models and the ability to export that information to the business. We all know that nobody likes documentation but at the end of the day we need it and always ask for it when we have questions about the designs and how to use these models.
The Basic concept of the app is to connect to any tabular model and extract information from key objects within a model. The data is added to the UI and with the UI you have the ability to select the objects and add a description which can be saved back to a metadata database.
The application foundation is a WPF form and uses data grids and text boxes to hold the data objects. I use the Entity framework to allow me to pull the data into the grids on the WPF form. I extract the SSAS Tabular model objects using Analysis Management Objects (C#).
Data Layer Design:
The Entity framework is the data layer that allows for the data to interact with the form and be updated and saved back to the Meta database. The data layer is very powerful as you can build your tables, views and stored procedures to interact with the model and access it with C#. Once I extract all the Information from the Tabular Model using AMO I insert the data into tables and then use the Entity Framework to access those tables in my WPF form.
The tables that hold the information from the model are all based on the Server, Database, and the model. So when you bring in the data it will look and see if that data exists and either updates, inserts, or deletes using merge statements. So this handles change data very well using SQL stored procedures as its method to perform this action.
The following are features and screenshots:
- Save– Allows descriptions elements to be saved for any SSAS Tabular Object. You can select a row and the description box is enabled and allows you to add text and save it.
- Refresh Data – Allows for a real-time update of SSAS Tabular model objects. This action is extracting the latest objects from the model using AMO and refreshing the WPF datagrids.
- View Report – Allows you to view some reports on the SSAS details like the measures, measure groups, and dimensions.
- Export – All the data from the model and descriptions can be exported to Excel.
- Settings – gives you the option to connect to any SSAS Tabular Model.
- Excel Add-in – Added a Ribbon to Excel that has a button to execute this application.
- Additional Features (Relationships) – The neat thing that I feel is useful is the relationship tab it is nice because it defines the type of relationship and the keys.
Well I hope you find interest in this as I plan on continuing down the road of documentation for the SSAS models and I would love to hear some feedback. I feel that with this technology becoming a major element in self service we need a good way to keep track of the objects and descriptions. I have other ideas as well such as being able to edit the objects exported in excel and save them back to the database kind of like the MDS design that microsoft added in with Excel.