microstrategy for data engineers
TRANSCRIPT
![Page 1: Microstrategy for Data Engineers](https://reader033.vdocuments.us/reader033/viewer/2022042611/587b3c441a28ab9c0e8b4d15/html5/thumbnails/1.jpg)
MicroStrategy for
Data Engineers
Francesco Mucio
London, 10 June 2016
![Page 2: Microstrategy for Data Engineers](https://reader033.vdocuments.us/reader033/viewer/2022042611/587b3c441a28ab9c0e8b4d15/html5/thumbnails/2.jpg)
What is MicroStrategy
- Born in 1989
- BI Reporting Tool
- The last of the old one not acquired and not acquiring
- Etc...
![Page 3: Microstrategy for Data Engineers](https://reader033.vdocuments.us/reader033/viewer/2022042611/587b3c441a28ab9c0e8b4d15/html5/thumbnails/3.jpg)
MicroStrategy Architecture
dwh MicroStrategyiServer
web serverwith MSTR webapp
user
MicroStrategymetadata
![Page 4: Microstrategy for Data Engineers](https://reader033.vdocuments.us/reader033/viewer/2022042611/587b3c441a28ab9c0e8b4d15/html5/thumbnails/4.jpg)
What MicroStrategy do wants!
- A Dimensional model (snowflake flavour)
- Dummy Records for Null values
- Good Data Quality
![Page 5: Microstrategy for Data Engineers](https://reader033.vdocuments.us/reader033/viewer/2022042611/587b3c441a28ab9c0e8b4d15/html5/thumbnails/5.jpg)
Star vs Snowflake schema
fact
dimension2
dimension4
dimension5
dimension3
dimension1
fact
dimension2 - lvl1
dimension4
dimension5 - lvl1
dimension3
dimension1 - lvl2
dimension1 - lvl1
dimension5 - lvlb
dimension5 - lvl2a
dimension2 - lvl2
dimension2 - lvl3
![Page 6: Microstrategy for Data Engineers](https://reader033.vdocuments.us/reader033/viewer/2022042611/587b3c441a28ab9c0e8b4d15/html5/thumbnails/6.jpg)
Star vs Snowflake schema
Star Schema Dimension Snowflake Schema Dimension
date_id day_of_week month_id month_name quarter_id
20160101 Friday 201601 January 20161
20160102 Saturday 201601 January 20161
20160103 Sunday 201601 January 20161
date_id day_of_week month_id
20160101 Friday 201601
20160102 Saturday 201601
20160103 Sunday 201601
month_id month_name quarter_id
201601 January 20161
201601 January 20161
201601 January 20161
quarter_id quarter_name
20161 2016-Q1
20161 2016-Q1
20161 2016-Q1
DIM_CALENDAR
DIM_DATE
DIM_MONTH
DIM_QUARTER
![Page 7: Microstrategy for Data Engineers](https://reader033.vdocuments.us/reader033/viewer/2022042611/587b3c441a28ab9c0e8b4d15/html5/thumbnails/7.jpg)
MicroStrategy Objects
➔ Schema Objects
◆ mapping of physical objects: Tables, Columns, etc…
➔ Public Objects
◆ application objects: Metrics, Reports, Dashboards, etc…
![Page 8: Microstrategy for Data Engineers](https://reader033.vdocuments.us/reader033/viewer/2022042611/587b3c441a28ab9c0e8b4d15/html5/thumbnails/8.jpg)
MicroStrategy Schema - Tables
A MicroStrategy Tables maps a database table or view.
Multiple physical tables can be mapped as partition of a MicroStrategy table.
![Page 9: Microstrategy for Data Engineers](https://reader033.vdocuments.us/reader033/viewer/2022042611/587b3c441a28ab9c0e8b4d15/html5/thumbnails/9.jpg)
MicroStrategy Schema - Logical Tables
A Logical Table allows to use a query as a table.
In the definition you must:
- Set the database instance to use
- Map all the columns returned by you query
![Page 10: Microstrategy for Data Engineers](https://reader033.vdocuments.us/reader033/viewer/2022042611/587b3c441a28ab9c0e8b4d15/html5/thumbnails/10.jpg)
MicroStrategy Schema - Attributes
- A level of a Dimension.
- Needs a lookup table.
- Can have parents and children.
- Has multiple forms.
- Each form can have multiple expressions.
![Page 11: Microstrategy for Data Engineers](https://reader033.vdocuments.us/reader033/viewer/2022042611/587b3c441a28ab9c0e8b4d15/html5/thumbnails/11.jpg)
MicroStrategy Schema - Facts
A numeric* value that needs to be measured by the business.
Can have multiple expressions.
Doesn’t know how it will be used.
Can be extended, if required.
* usually
![Page 12: Microstrategy for Data Engineers](https://reader033.vdocuments.us/reader033/viewer/2022042611/587b3c441a28ab9c0e8b4d15/html5/thumbnails/12.jpg)
MicroStrategy Schema - Facts
A numeric* value that needs to be measured by the business.
Can have multiple expressions.
Doesn’t know how it will be used.
Can be extended, if required.
* usually
![Page 13: Microstrategy for Data Engineers](https://reader033.vdocuments.us/reader033/viewer/2022042611/587b3c441a28ab9c0e8b4d15/html5/thumbnails/13.jpg)
MicroStrategy Schema - Advanced topics
Expressions can contain intra-row operations.
Expressions can contain 1-1 functions.
Attributes determine the Logical Weight of a table.
Transformations defines ways to transform an attribute element to one or more elements of the same attribute:
i.e. Previous Year or Year-to-date.
![Page 14: Microstrategy for Data Engineers](https://reader033.vdocuments.us/reader033/viewer/2022042611/587b3c441a28ab9c0e8b4d15/html5/thumbnails/14.jpg)
MicroStrategy Metrics
A Fact defines which column of which table we want to use.
A Metric defines what, how, where, when to do with that fact/column.
![Page 15: Microstrategy for Data Engineers](https://reader033.vdocuments.us/reader033/viewer/2022042611/587b3c441a28ab9c0e8b4d15/html5/thumbnails/15.jpg)
MicroStrategy Metrics
How to aggregate?
The Formula defines the kind of aggregation we want to use.
Metrics can be compound of other metrics:
Profit = Revenue - Cost
![Page 16: Microstrategy for Data Engineers](https://reader033.vdocuments.us/reader033/viewer/2022042611/587b3c441a28ab9c0e8b4d15/html5/thumbnails/16.jpg)
MicroStrategy Metrics
Where to aggregate?
The Level allows to fine tune the aggregation, depending on attributes/dimension.
Filtering: ignore or not a where condition?
Grouping: aggregate using the standard grouping or not.
![Page 17: Microstrategy for Data Engineers](https://reader033.vdocuments.us/reader033/viewer/2022042611/587b3c441a28ab9c0e8b4d15/html5/thumbnails/17.jpg)
MicroStrategy Metrics
When to aggregate?
Condition: a where condition.
This can become a CASE statement or a separated sub-query.
Transformation: a rule to transform an attribute value
![Page 18: Microstrategy for Data Engineers](https://reader033.vdocuments.us/reader033/viewer/2022042611/587b3c441a28ab9c0e8b4d15/html5/thumbnails/18.jpg)
MicroStrategy Reports
Reports or Datasets are glorified SQL queries.
Components:
- Report Objects
- Filter
- Template
![Page 19: Microstrategy for Data Engineers](https://reader033.vdocuments.us/reader033/viewer/2022042611/587b3c441a28ab9c0e8b4d15/html5/thumbnails/19.jpg)
MicroStrategy Reports
OLAP capabilities:
- Moving objects in template- Moving objects between Report
object and template- Modify the View Filters
Non-OLAP:
- Add new objects- Change the Report Filter
![Page 20: Microstrategy for Data Engineers](https://reader033.vdocuments.us/reader033/viewer/2022042611/587b3c441a28ab9c0e8b4d15/html5/thumbnails/20.jpg)
MicroStrategy Reports: SQL...Pass0 - Query Pass Start Time: 09/06/2016 23:22:50
Query Pass End Time: 09/06/2016 23:22:52Query Execution: 0:00:01.78Data Fetching and Processing: 0:00:00.00 Data Transfer from Datasource(s): 0:00:00.00Other Processing: 0:00:00.02Rows selected: 131
with gopa1 as (select /*Administrator - job 1049 - New Report - 20160609:232250*/ a13.PROFILE_GENDER_ID PROFILE_GENDER_ID,
a12.ACQUISITION_BRAND ACQUISITION_BRAND,a11.PROFILE_REGISTERED_DT Reporting_Date_DT,count(distinct a11.PROFILE_USER_ID) n_users
from MICRO.MS_F_PROFILE_REGISTRATION a11...
For the full SQL code click here.
![Page 21: Microstrategy for Data Engineers](https://reader033.vdocuments.us/reader033/viewer/2022042611/587b3c441a28ab9c0e8b4d15/html5/thumbnails/21.jpg)
MicroStrategy Intelligent Cubes
In memory copy of a query result.
Can be also partially re-published.
A report based on an Intelligent Cubes:
- has access only to that cube objects- generates an MDX query
Dynamic Sourcing allows also not cube-based reports to use available cubes.
![Page 22: Microstrategy for Data Engineers](https://reader033.vdocuments.us/reader033/viewer/2022042611/587b3c441a28ab9c0e8b4d15/html5/thumbnails/22.jpg)
MicroStrategy Reports - Free Form SQL
A FFSQL is a report created starting from an existing query.
Very good for prototyping
Very bad for maintenance
You can also have FFSQL cubes.
![Page 23: Microstrategy for Data Engineers](https://reader033.vdocuments.us/reader033/viewer/2022042611/587b3c441a28ab9c0e8b4d15/html5/thumbnails/23.jpg)
MicroStrategy Reports - Free Form SQL
Things to define:
- Database Instance
- Query
- Free form objects
![Page 24: Microstrategy for Data Engineers](https://reader033.vdocuments.us/reader033/viewer/2022042611/587b3c441a28ab9c0e8b4d15/html5/thumbnails/24.jpg)
MicroStrategy Filters
Filters are like where conditions.
Multiple filters can be combined, using AND, OR, and NOT, to make complex ones.
![Page 25: Microstrategy for Data Engineers](https://reader033.vdocuments.us/reader033/viewer/2022042611/587b3c441a28ab9c0e8b4d15/html5/thumbnails/25.jpg)
MicroStrategy Filters: Advanced
Reports can be used as filters.
Set Qualification are used to filter attributes based on elements not always present in the final report.
![Page 26: Microstrategy for Data Engineers](https://reader033.vdocuments.us/reader033/viewer/2022042611/587b3c441a28ab9c0e8b4d15/html5/thumbnails/26.jpg)
MicroStrategy Filters: Advancedgopa1 as (select /*Administrator - job 2158 - All TYPE REG Active with Revenue - 20160610:132345*/ a11.PROFILE_USER_ID PROFILE_USER_ID from MICRO.MS_F_PROFILE_REGISTRATION a11 group by a11.PROFILE_USER_ID having sum(a11.PROFILE_PHOTOS_TOTAL_COUNT) >= 3.0 ), gopa9 as (select /*Administrator - job 2158 - All TYPE REG Active with Revenue - 20160610:132345*/ a15.REGISTRATION_METHOD REGISTRATION_METHOD, a13.PROFILE_REGISTERED_DT first_click_date, a14.ACQUISITION_BRAND ACQUISITION_BRAND, a14.ACQUISITION_TRANSLATION ACQUISITION_TRANSLATION, a13.PROFILE_GENDER_ID PROFILE_GENDER_ID, count(distinct a11.PROFILE_USER_ID) WJXBFS1 from INGRES.MS_DIM_PROFILE a11 join gopa1 pa12 on (a11.PROFILE_USER_ID = pa12.PROFILE_USER_ID) join INGRES.MS_DIM_PROFILE a13 on (a11.PROFILE_USER_ID = a13.PROFILE_USER_ID) join ingres.DIM_ACQUISITION_PLATFORM a14 on (a13.PROFILE_ACQUISITION_PLATFORM = a14.ACQUISITION_PLATFORM) join ingres.MS_DIM_PROFILE_EXTRA a15 on (a11.PROFILE_USER_ID = a15.PROFILE_USER_ID) where (a13.PROFILE_REGISTERED_DT >= DATE '2016-01-01' and a13.PROFILE_IS_TEST_USER_ID = 0 and a14.ACQUISITION_TRANSLATION in ('Android', 'iOS', 'Webapp', 'Windows') and a14.ACQUISITION_BRAND in ('Badoo')) group by a15.REGISTRATION_METHOD, a13.PROFILE_REGISTERED_DT, a14.ACQUISITION_BRAND, a14.ACQUISITION_TRANSLATION, a13.PROFILE_GENDER_ID )
![Page 27: Microstrategy for Data Engineers](https://reader033.vdocuments.us/reader033/viewer/2022042611/587b3c441a28ab9c0e8b4d15/html5/thumbnails/27.jpg)
MicroStrategy Dashboards
A more modern tool for data exploration.
- Simpler to develop and use
- Slick design
- Just nicer
![Page 28: Microstrategy for Data Engineers](https://reader033.vdocuments.us/reader033/viewer/2022042611/587b3c441a28ab9c0e8b4d15/html5/thumbnails/28.jpg)
MicroStrategy Dashboards
![Page 29: Microstrategy for Data Engineers](https://reader033.vdocuments.us/reader033/viewer/2022042611/587b3c441a28ab9c0e8b4d15/html5/thumbnails/29.jpg)
MicroStrategy Dashboards
A dashboard has at least one tab.
A tab has one or more panels.
A panel contains visualizations.
Each visualization is based on one or more datasets.
![Page 30: Microstrategy for Data Engineers](https://reader033.vdocuments.us/reader033/viewer/2022042611/587b3c441a28ab9c0e8b4d15/html5/thumbnails/30.jpg)
MicroStrategy Dashboards
A dashboard has at least one tab.
A tab has one or more panels.
A panel contains visualizations.
Each visualization is based on one or more datasets.
![Page 31: Microstrategy for Data Engineers](https://reader033.vdocuments.us/reader033/viewer/2022042611/587b3c441a28ab9c0e8b4d15/html5/thumbnails/31.jpg)
MicroStrategy Debugging
A simple report sql .
[originally here there was a link to a SQL report were I was discussing the different sections, results and how to interpreter the SQL, with common table expressions, generated by MicroStrategy]