What is MDX and what is its use in SAP BPC

Question

I would like to know more about "MDX" (Multidimensional Expressions).

What is it?
What is it used for?
Why would you use it?
Is it better than SQL?
What is its use in SAP BPS (I haven't seen BPC, just heard that MDX is in it and want to know more)?

Solution 1

MDX is the query language developed by Microsoft for use with their OLAP tools. Since its creation, others (The open source project Mondrian, and Hyperion) have tried to create versions of it for use in their products.

OLAP data tends to look like a star-schema with a central fact table surrounded by multiple dimensions. MDX is designed to allow you to query these structures and create cross-tab type results.

While the language looks like SQL it doesn't behave like it and if you are an SQL programmer, the mental leap can be tough.

As to whether it is better than SQL, it serves a highly specialized purpose, i.e. analyzing data in a specific format. So if you want to query a star schema, it is better, otherwise, SQL will probably do the job.

Solution 2

Focused on SAP BPC:

What is it used for?

It's used when you want to apply some custom calculation/business logic over many records/intersections and after submitting raw data. Example, first send prices in one input schedule, then quantities in other one, as a third step run a calculation for sales amount based on prices and quantities for all products. It's also used to execute the Business Rules, for that you run a predefined program (like CALC_ACCOUNT, CONSOLIDATION, etc)

Is it better than SQL?

In BPC, "SQL" logic scripts have better performance than MDX. However SQL for BPC purposes has not much to do with SQL used in other it's just how they call it.

Solution 3

SQL is for 'traditional' databases (OLTP). Most people learn the basics fairly easily. MDX is only for multi-dimensional databases (OLAP), and is harder to learn than SQL in my opinion. The trouble is they look very similar.

Many programmers never need MDX even if they have to query multi-dimensional databases, because most analysis software forces them to build reports with drag-drop interfaces.

If you don't have a requirement to work with a multi-dimensional database, then don't create one just for the fun of it.....it won't be...

Solution 4

There are 2 versions of SAP-BPC (Business Objects Planning and Consolidation)

1. SAP-BPC Netweaver
2. SAP-BPC Microsoft Analysis Services

The Microsoft analysis services version of the product allows you to use MDX or multi dimensional expressions to both query the multi-dimensional database (OLAP) and write calculation logic.

However, SAP-BPC does not require a knowledge of MDX to either be used or administered.

You can see product documentation and a demonstration.

Best of luck on your research,

Solution 5

MDX means Multi Dimensional eXpressions or some such. It is relevant to OLAP cubes and not to regular relational databases such as Oracle or SQL Server (although some SQL Server editions come with Analysis Services which is OLAP). The multidimensional world is about data warehousing and efficient reporting, not about doing normal transactional processing so you wouldn't use it for an order entry system, but you might move that data into a datamart to run reports against to see sales trends. That should be enough to get you started I hope.

View more solutions

Comments

[ivansabik]

However in BPC, this is a modified version of MDX not all functions are available and syntaxis may vary

[ivansabik]

Not quite correct, the NW version also supports MDX for script logics (logic scripts in v10 and onwards)