Service Manager Intelligence & Analysis: Part 2 ” DW drilldown”
This is the part 2 of Service Manager Intelligence & Analysis series.
Service Manager Intelligence & Analysis: Part 1 “Where the shoe pinches?”
Service Manager Intelligence & Analysis: Part 3 “Access the Data With Power Query”
Service Manager Intelligence & Analysis: Part 4 “Access the Data With Power BI Desktop”
For an overview of how we have installed Service Manager in our Demo environment. Check out Powation.com
Before getting started with connecting Power Query to our Service Manager databases we need to take a look at how Service Manager is structured and where we can find the information that we need. Service Manager is built using SQL as the structure of data. This means we will be using the SQL Database connection in Power Query to access the data in SCSM.
This is from Excel 2016 (Preview)
The Databases
When installing Service Manager the following databases will be available:
The {Service Manager} db will contain Configuration Items, Work Items, Incidents and information related to configuration of Service Manager as a product. This database can become extremely large and contain a lot of information. Since SCSM has connectors that to collect objects and their attributes from other systems in our in infrastructure. The different information collected from the different connectors needs to be merged together and made more friendly for reporting purposes. This introduces the Service Manager Dataware house. Data registered in Service Manager database will be transformed and moved to the Dataware house by jobs registered in Service Manager.
The data warehouse in System Center Service Manager provides three primary functions:
- Offload data from the main Service Manager database to improve performance of the Service Manager database
- Long-term data storage
- Provide data for reports
The dataware house consist of three different databases:
DWStagingAndConfig
DWRepository
DWDataMart
These three databases make up the data warehouse. The extract process populates the {DWStagingAndConfig database}, which is transformed into a proper format in the {DWRepository database}, which, through the load process, becomes the content for the {DWDataMart} database. The {DWDataMart} is the database we will use to connect to with Power Query.
In a future blog post I will talk about using the {DWASDataBase} Analysis Services, with the Power BI Analysis Connector.( To be continued).
Here is an technical overview by Microsoft showing the process of transforming data and sending the data to the different database stages.
The DWDataMart Schema
So now we know that our information is contained in the
{DWDataMart} database. Now we need to look at how our data is structured and how the db schema is structured. This is the most important step in creating informative reports and visualizations later on in the process.
Now as you probably know Service Manager is divided into different modules. Service Request, Incident, Problem, Change, Release and Configuration Items etc..
Each of these modules are available as Classes in Service Manager. In the DW the classes are represented as Dimensions. You can look at dimensions as a table with rows and every column is a property.
Note: Classes = Dimensions
Here is an example of the IncidentDimvw dimension in DW. Here you can see example of some of the properties that are available.
Extracted from the DW with Power Query
As you can see in the above table you can see that some of the properties has a FK (foreign key) in front of them. This means that this property is linked to another table, where there is more information about the specific property. Often the key can be a unique ID that is related to a readable text in the related table.
Let’s look at the property Status_IncidentStatusID, here you can see that there is a relationship to another table with more information on the property. You can easily see on the name that there is a related table with the information. The Status of the Incident is represented by a ID and not by readable text. The actual status can be found in the InsidentStatusvw and in the IncidentStatusValue Column.
The connection between the two tables is called a relationship. Dimensions have relationships to other tables, that contain information that is relevant.
There are three types of tables that can be referred to in a relationship:
1.Other Dimensions
2.Fact tables
3 Outriggers.
I’m not going to go into details about the different types. But to understand more about the different types.
Check out the following blogs:
http://blogs.technet.com/b/servicemanager/archive/2011/03/14/service-manager-data-warehouse-schema-now-available.aspx
http://blogs.technet.com/b/servicemanager/archive/2010/03/30/deep-dive-into-the-data-warehouse-custom-fact-tables-dimensions-and-outriggers.aspx
So when we got the bits and pieces let’s look at how the Incident module relationships looks like in a graphical Visio view.
As the picture shows there are different relationships for the Incident Module for the different properties. The tables are linked together with keys that are column properties. These properties can be merged together to show the related information. Later in this blog series we are going to do analysis and create visualizations based on the information in the tables and their table relationships.
To download a Visio version of the DWDataMart Schema, you can find it here -> DWDataMart DB Schema
In the next part we are going to look at how we use Power Query to connect to the DWDataMart and start working with the tables and relationships.
Continue to Part 3: -> Service Manager Intelligence & Analysis: Part 3 “Access the Data with Power Query”
About the Author
Fredrik Knalstad assist companies in deploying, automating and designing management solutions based on Datacenter | Intelligence | Automation. The latest years I have been working deeply with System Center and developing integrations with systems outside of System Center.This includes planning, design, implementation and using self-service Business Intelligence.
Related Posts
Quick Post: SCSM Incident Reporting -> PowerBI tiles in PowerPoint →
Service Manager Intelligence & Analysis: Part 5 “Visualization with Power View” →
Service Manager Intelligence & Analysis: Overview →
Pingback: Service Manager Intelligence & Analysis: Part 1 “Where the shoe pinches?” | Knalstad.no
Pingback: Service Manager Intelligence & Analysis: Part 3 “Access the Data” | Knalstad.no
Pingback: Service Manager Intelligence & Analysis: Overview | Knalstad.no
Pingback: Service Manager Intelligence & Analysis: Part 4 “Access the Data with Power BI Desktop” | Knalstad.no
Pingback: Service Manager Intelligence & Analysis: Part 5 “Visualization with Power View” | Knalstad.no