The birth of a new query language
Posted by thomasivarssonmalmo on September 16, 2007
With the release of Performance Point soon coming MS is presenting a new query language included in this product: PEL or Performance Expression Language. You can read more about this in this review of PP here .
But first some thoughts about the right query language for the MS BI platform.
When is SQL most suitable for queries and reports and when is MDX the best choice?
I have always advised my customers to use the relational world(SQL) for leaf level queries and the multidimensional MDX for top level queries. SSAS2005 do a crossjoin of everything you put i a cube(dimensions and measures) and quering the star schema directly with SSRS2005 and TSQL(in an inner join) will only show the combinations that exists between the dimensions and measures involved. The crossjoin will also show combinations that do not exist.
I have never belived the fancy talk of UDM and that you can use SSAS2005 for all reporting. We are far from there.
A lot of Information workers(MS vocabulary) sometimes have the wrong tool for the problem. Sales representatives are given a multidimensional client to analyze data in SSAS2005 and only want to see what their customers have bought. With a multidimensional client they are sometimes forced to combine an entire company’s customers with the same company´s leaf level products. When a report like this have been running for several minutes they finally put the NON EMPTY statement on both columns and rows to get what they wanted from the beginning, an inner join of information.
MDX is superior for creating advanced time calculations. Creating an accumulated value requires less code than doing the same thing in TSQL.
Working with the report part of BI projects on top of the MS platform will require you to know when MDX or TSQL is the best choice.
So what is PEL then? I am quoting OLAP reports review of PP on page 17:
- It is the new MDX-like language used in PP to define nearly all calculations
- The PEL compiler validates and translates expressions into either SQL or MDX
- PEL also detects many errors at compile-time that Analysis Services would only detect at runtime
This sounds good and perhaps is the way to solve the problem that I have decribed above.