How to do OLAP analyse after building the Data Warehouse?

Question

We have built a data warehouse which contains multiple fact tables and multiple dimension tables (on a MySQL server).

What is the next step to do the OLAP operations? I read an answer says that OLAP operations are often expressed in MDX expressions. (https://stackoverflow.com/a/18923809/7887590)

Where could I execute my MDX expression to produce the report?

BTW, is OLAP an outdated technology? Because I have seen fewer and fewer discussions on this topic in recent years.

Answer 1

OLAP is not outdated, but maybe outmoded. The buzz nowadays is to run queries against the raw data directly, but IMO olap still plays a very important role, especially for users that are not versed in SQL. You can plug an OLAP engine on top of your data warehouse (e.g., Mondrian), and run MDX queries against the star schema. MDX may be tricky to learn, but business users tend to enjoy it more than SQL.

Also, you can use a drag&drop tool (Power BI, Tableau, Pentaho Analyzer) which can talk to OLAP engines (either using MDX or direct SQL) and avoid the need for your end users to write query code completely.

Answer 2

We're using an on premises Report Server by Microsoft to host Power BI files, which are using "Import Data" mode as opposed to live connections to their data source.

Our Power BI files hosted on the Report Server have up to 15 million rows (3 or 4 columns with very high cardinality) in their fact tables. They function very quickly and we are finding improvements in speed for distinct count measures in comparison to OLAP/MDX.

Power BI actually creates an instance of SSAS on the fly so you are actually using the very same technology as OLAP.

Power BI is hugely flexible and most things are possible using DAX - in fact the learning curve for DAX starts off a whole lot more gentle than MDX.

So if I have a powerbi file (.pbix) that is using 15m rows of data via "Import Data" I do the following:

• upload the pbix to our Report Server
• add a refresh scheme to the Report Server e.g. 'refresh every hour'
• then what happens is that it will go and get the 15m rows of data every hour but when the user interacts with the pbix the data is inside an instance of SSAS that is integrated into the pbix - so all calculation are carried out internally in-memory with no additional round trips to the server.

(I miss MDX but I would not recommend it to anyone)