MicroStrategy Desktop allows us to build ad hoc schemas in intuitive ways using a friendly interface. Ad hoc schemas help business users to achieve quicker values. The core of the ad hoc building process is Data Import, which has many features such as auto-detect attributes type, auto-create metrics, auto-identify data relations, and allows us to preview data. In addition, it allows us to combine data from multiple sources in a single dataset, create multi-form attributes, and create and edit relationships across attributes. Finally, it can be mapped to project attributes in order to inherit project security filters in MicroStrategy Enterprise.
We are going to work with relational databases and use the same source as we used in Chapter 2, Setting Up an Analytics Semantic Layer and Public Objects when we built schemas. There is a picture from CA ERwin Data Modeler, that displays physical data models. Using this model, we can easily identify what tables we need and what the joins are between tables.
Let's connect to Oracle database and build an ad hoc schema:
- Click File | New.
- Choose Database | Oracle 11g. We have several options:
- Type a Query - we can write an SQL query or copy it
- Build a Query - with the help of a graphical interface we can create a query
- Pick tables - simply choose tables that we need for analyses In our case we have a data model that shows us list tables.
- Click Add in order to add a new data source. We can create a new DSN or use an existing one. However, keep in mind that MicroStrategy Desktop is installed on Windows and we should create an ODBC connection to Oracle if we want to use it (or we can choose DNS-less and type the host, port, login, and password):
- Hostname:
localhost - Port:
1521 - SID:
orcl - User:
system - Password:
MasterinBI2016
- Hostname:
- Now we can choose our schema EDW and pick the required tables:
Before we finish, we should define the joins between tables, attributes, and metrics.
- Click on Prepare Data and we get a Preview window, which helps us to prepare data. In addition, we can preview data, change data type, transform the column to measure or attribute. For all columns which we do not need for the dashboard, we can choose Do Not Import:
- Let's define joins between tables. By default, all columns with numeric types become metrics. We should transform all key fields to attributes by clicking on the column and choosing Convert to Attribute.
- In order to the define joins between two tables, we should drag the name of a column in one table and drop to another column in another table. As a result, an
INNER JOINwill be created. These are the join conditions:#
Join Conditions
1
FactInternetSales.ProductKey=DimProduct.ProductKey2
FactInternetSales.OrderDateKey=DimDate.DateKey3
FactInternetSales.CustomerKey=DimCustomer.CustomerKey4
FactInternetSales.PromotionKey=DimPromotion.PromotionKey5
FactInternetSales.CurrencyKey=DimCurrency.CurrencyKey6
FactInternetSales.SalesOrderNumber =FactInternetSalesReason. SalesOrderNumberMicroStrategy Desktop can automatically map the same columns. We should be careful and unmap all columns that do not service joins between tables.
- Click Finish. We have two options: extract all data in-memory or use a live connection. When we choose the in-memory option, we can publish an in-memory cube in MicroStrategy Platform. Let's use a live connection.
Now we are ready to build a new dashboard but before we actually start to realize our dashboard, let's discuss several very important terms from the world of data analytics and dramatically increase the flexibility and power of MicroStrategy Desktop:
- Data Blending: A quick process of extracting data from various data sources, transforming and combining them into one dataset.
- Data Profiling: The process of analyzing the nature of data in existing datasets in order to find outliers or skews. In addition, it allows us to understand the distribution of data values.
- Data Wrangling: This allows us to map data from one format to another using the rich functionality of MicroStrategy commands.
- Data Mashup: Used for the integration of several datasets into one new dataset.
Let's try to understand how we can leverage these features in MicroStrategy Desktop.
First of all, we need to add one more table to the existing ad hoc schema. There is an Excel spreadsheet that you can find in the attachment for this chapter - Geography.xls.
Let's try to mashup data with a relational database and an Excel spreadsheet:
- Right-click on our dataset and choose Edit Dataset...:
- Click on Add new table and choose file from disk. Find
Geography.xlsand click Add. It will automatically join the fact table withGeographyKey. - Let's look at the Preview and we find that the field Location has many values separated with commas. We are lucky, because we can wrangle that data and prepare it for the dashboard.
- Click on Wrangle....
- Choose Location as a field and Split on Separate as a function. Our separator is a comma. Click Apply:
- As a result, we get six columns. Let's rename them. MicroStrategy saves all operations with data in History Script. We can easy rollback any of the changes:
- Let's rename the column by right-clicking on the column name:
We can examine our data and fix some issues. In order to examine data we should use data profiling by clicking on the column name and choosing Text Selector. Let's do it for State Province Name and City. There are some issues with the data:
There are two values for New York and two values for München. Let's fix it by clicking Edit, which appears near the value when we choose it.
We are done with data wrangling. Click Ok.
MicroStrategy will understand that is it geodata and will transform these attributes to geodata. There is one very useful feature - data enrichment. For example, if we have only the zip code, MicroStrategy can look up the country, state, and city for us. We don't need this because our data file already has provided all this data.
Click on Update Dataset and we are ready to work with our ad hoc schema.