SystemCenter DW_drilldown

Published on July 20th, 2015 | by Fredrik Knalstad

5

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.

8

This is from Excel 2016 (Preview)

The Databases

When installing Service Manager the following databases will be available:

SCSM_databases

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:

  1. Offload data from the main Service Manager database to improve performance of the Service Manager database
  2. Long-term data storage
  3. 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.

7

 

The DWDataMart Schema
So now we know that our information is contained in thedb {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.

Incident Table

Extracted from the DW with Power Query

IncidentDImvw

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.

IncidentDImvw2

IncidentDImvw4

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.

Incident Module

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”

 

Tags: ,


About the Author

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.



5 Responses to Service Manager Intelligence & Analysis: Part 2 ” DW drilldown”

  1. Pingback: Service Manager Intelligence & Analysis: Part 1 “Where the shoe pinches?” | Knalstad.no

  2. Pingback: Service Manager Intelligence & Analysis: Part 3 “Access the Data” | Knalstad.no

  3. Pingback: Service Manager Intelligence & Analysis: Overview | Knalstad.no

  4. Pingback: Service Manager Intelligence & Analysis: Part 4 “Access the Data with Power BI Desktop” | Knalstad.no

  5. Pingback: Service Manager Intelligence & Analysis: Part 5 “Visualization with Power View” | Knalstad.no

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to Top ↑