MDX VS SQL

Hi All,

Back in 1995 sql did not have analytical function support that is there now and it was very difficult to run analytical queries using sql. It had two fundamental problems for Comparison and Sequential opearation. However with the advent of MDX this was made very easy due to the functions that MDX provided .

SQL soon caught up with MDX( Microsoft) and SQL now has almost all analytical functions supported by MDX. Below is my notes after reading ralph kimball article on limitation of SQL . Link to Kimball article at the end

 

Sql Limitations

1. In ability to perform comparisons--- Comparison of this year to Last year

2. Lack of sequential computations --- Running Totals , Moving Averages these things were difficult earlier

My Scenario -- We have a Periodic snapshot table for electricity bill. Each row of this snapshot captures montly electricity meter reading so one month it is 100 next month is is 150 after that next month its 230 . So the billing is (150-100)* money per unit. I want to plot a graph of monthly comparison of bills how would you do that.

How do you write sql for this

1. We will write without using analytical functions ( Over partition by )

Crosstab	Q1 2013	Q1 2014	2013	2014	% of total sale of  all products
Camping	100	120	333	3432
Mountain	11	13	444	3432
household	22	45	4345	4321

Select product_name,  sum(case when ( quarter = Q1 and year = 2013) then f.sales else 0 end ) Quarter_13,

Sum( case when ( quarter = Q1 and year = 2014) then f.sales else 0 end) Quarter_14

From  Fact , dim_prod prd, time t

Where fact.product_id = prd.prod_id

And fact.year_id = t.year_id

Group by prod_id

Earlier the Entire set of analytical functions that are supported by oracle was not available. With the advent of analytical functions those analysis can be easily pused to database. Now mostly these are taken care by reporting tool

Please give some more example from Cognos sample of the Analytical questions typically asked

http://cognossimplified.blogspot.in/2012/01/mdx-functions-in-report-studio.html    ------------ MDX functions . Now databases are not giving all these analytical  functions  in databases

Important note - How to handle Comparisons in SQL --  CASE statements, SQL self-joins, SQL correlated subselects, and separate queries combined in the client application.

Very good Articles by Ralph Kimball on use of SQL for Analytics and what are the challenges

http://www.kimballgroup.com/1996/02/sql-roadblocks-and-pitfalls/

http://www.kimballgroup.com/1996/01/the-problem-with-comparisons/

Available Set of Analytical functions available on SQL

AVG *

CORR *

COUNT *

COVAR_POP *

COVAR_SAMP *

CUME_DIST

DENSE_RANK

FIRST

FIRST_VALUE *

LAG

LAST

LAST_VALUE *

LEAD

LISTAGG

MAX *

MIN *

NTH_VALUE *

NTILE

PERCENT_RANK

PERCENTILE_CONT

PERCENTILE_DISC

RANK

RATIO_TO_REPORT

REGR_ (Linear Regression) Functions *

ROW_NUMBER

STDDEV *

STDDEV_POP *

STDDEV_SAMP *

SUM *

VAR_POP *

VAR_SAMP *

VARIANCE *