Database Reference
In-Depth Information
How it works...
This recipe is broken down as follows:
•
Connecting to System Center data sources
: Data gathering is the initial step in
the creation of your query. The data in your dataset is retrieved from the selected
data source. From this point, you start manipulating the data with the Power BI
tools in Excel.
•
Exploring the data and overview of the interface
: This part of the recipe de-
scribes the functionality used to edit the original data load query. The data that you
need to keep, manipulate, or add to the query depends on the required end result. In
the example of the incident view from Service Manager, all incidents with their
properties defined in the default view of SCSM were included in your dataset. In-
cident data that is not required for your analysis can be removed from the query.
After shaping and manipulating the data, you can remove the data that was re-
quired only for calculations or manipulation.
•
Extending the query with data from other queries
: You can merge additional
data from different data sources to create one super dataset. This may be required
in order to translate column data into friendly names, and extend the query with in-
formation from other queries or data sources. In the example used in this recipe,
Service Manager data was included in the query. Service Manager uses internal
names for all list items that can be viewed and selected in the incident form. The
equivalent friendly names are included in the
DisplayStringDimvw
query,
which we included in the workbook queries at the beginning of this recipe. This is
just an example of the capabilities of merging data from different queries. Different
scenarios will exist for each System Center product. Additionally, the merge can
also be done with a different System Center component (for example,
Orchestrat-
or
).
•
Adding functions to your Query
: Calculations and manipulations are the key to
perform an accurate analysis of data. Repetitive calculations or transformations on
data can be included in a function and added to the workbook query list. Database
date/time data is, in most cases, is time-zone-specific. Date and time entries that
you see in the dataset can be localized. You can create a function to perform this
localization. Calculations and manipulations are important in dataset creation. The
examples used in the recipes describe functions for the date/time data type. Num-
bers, text, and many other data-type-specific functions are available for you to in-
clude in your query.
