Those who have been following the series of blogs today will notice a trend in the topics being picked up. These are all features that are launched in SQL 2012 but more importantly they all reflect the T-SQL equivalent of similar functionality available in SSAS. It makes sense considering the push MS has been making on scalable VLDB database volumes and high speed performance in OLTP systems. With the introduction of tabular model in SSAS, Columnstore indexes and the focus on power pivot and Power view it’s pretty clear that MS wants Enterprises to continue to use OLTP systems for very large data volumes without the need to shift to SSAS.
With the new OVER functionality we see one more feature that has long been available in MDX query language and that is the lag keyword. Coupled with the OVER clause we can now use the LAG keyword to lookup the previous primary key value to fetch the data in a manner similar to navigating a set. While this is not the equivalent of the Next member or previous member syntax available in mdx it comes close to helping address common OLAP business cases. E.g. a typical case is comparison of this week’s sales with last weeks . The below query uses the LAG and Over syntax to fetch data from the FactinternetSales table to show how this example is implemented.
SELECT d.WeekNumberOfYear
,SUM(UnitPRICE) Weeklysales
,LAG(SUM(UnitPRICE)) OVER (
ORDER BY WeekNumberOfYear
) PrevWeekSales
,SUM(UnitPRICE) - LAG(SUM(UnitPRICE)) OVER (
ORDER BY WeekNumberOfYear
) Diff
FROM FactInternetSales f
INNER JOIN DimDate d ON d.DateKey = f.OrderDateKey
GROUP BY D.WeekNumberOfYear
ORDER BY WeekNumberOfYear
Please Consider Subscribing
