Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / Languages / T-SQL

OLAP for Free

5.00/5 (1 vote)
20 Jun 2019CPOL10 min read 13.2K   532  
How to implement free OLAP cube in your project

Free OLAP Step By Step

In this article, I'm going to show how one can implement OLAP absolutely free. You won't have to spend a penny for MSAS, Cognos or such. If you check out prices for OLAP systems, you find that they cost tens thousand dollars not including yearly fee per each report user. On the other hand, the hardware is very cheap nowadays and there are lots of free databases. In many cases for mid-size projects, it would be easier to buy RAD or HDD and solve the problem at the expense of hardware resources.

OLAP cube generators take tabular data as input and generate cube file of special format with aggregated data. What if we take tabular data, aggregate them in SQL Server database and put them backwards in SQL Server table? I'm going to use Tableau superstore file as a data source for the examples. The file is accessible on Tableau forum. Superstore file

I changed it a bit. I replaced quotes and apostrophes by `. And replaced spaces and dashes for underline symbol in headers. I also added 2 years to shipping date and take only 2018 and 2019. All OLAP queries return huge data sets, so I only give an excerpt to understand the data structure, not all data. If you want all data, please, run it yourself. I put Year, Month and Quarter to a separate column. I also added Shipment month in date format. All these make SQL queries simpler.

Source Data Sample (Tableau Superstore Excerpt)

Click to enlarge image

(Click to enlarge table)

You can use SQL script to generate full table that I use. SQL INSERT Script for Superstore

The most primitive analytic query looks like this...

Simple Analytic Query

SQL
SELECT
State,
Ship_Year,
Ship_Month,
Category ,
sum(Sales) as SumSales,	  
sum(Quantity) as SumQuantity,
sum(Profit) as SumProfit	
FROM  tblSales
GROUP BY State, Category , Ship_Year, Ship_Month

The results of this query are sales by month by category by state.

Query 1 Results (Not All Data Included)

State Ship_Year Ship_Month Category SumSales SumQuantity SumProfit
Alabama 2018 9 Furniture 350.98 1 84.2352
Alabama 2019 10 Furniture 10.16 2 3.4544
Alabama 2019 12 Furniture 141.96 2 35.49
Alabama 2018 5 Office Supplies 4.98 1 2.4402
Alabama 2018 6 Office Supplies 247.65 9 75.6134
Alabama 2018 8 Office Supplies 197.05 7 59.115
Alabama 2018 9 Office Supplies 913.16 6 123.0272
Alabama 2018 10 Office Supplies 88.86 12 37.9872
Alabama 2018 11 Office Supplies 269.77 22 117.3024
Alabama 2019 1 Office Supplies 56.37 16 23.1341
Alabama 2019 4 Office Supplies 492.18 7 16.4172
Alabama 2019 5 Office Supplies 26.16 4 12.8184
Alabama 2019 6 Office Supplies 16.24 4 6.7064
Alabama 2019 12 Office Supplies 728.8 10 349.281
Alabama 2018 6 Technology 979.95 5 274.386
Alabama 2018 7 Technology 29 2 7.25
Alabama 2018 10 Technology 209.97 3 71.3898
Alabama 2018 11 Technology 4359.96 12 1987.184
Alabama 2019 4 Technology 25.98 2 1.5588
Alabama 2019 11 Technology 239.92 8 23.992
Alabama 2019 12 Technology 90.48 2 23.5248
Arizona 2018 2 Furniture 14.368 2 3.9512
Arizona 2018 4 Furniture 2229.726 15 -702.3924
Arizona 2018 5 Furniture 111.888 7 22.3776
Arizona 2018 9 Furniture 393.165 3 -204.4458
Arizona 2018 10 Furniture 477.488 7 -34.641
Arizona 2018 12 Furniture 455.97 6 -218.8656
Arizona 2019 1 Furniture 83.413 3 -19.9054
Arizona 2019 2 Furniture 455.614 11 -169.3996
Arizona 2019 5 Furniture 209.979 7 -356.9643
Arizona 2019 6 Furniture 280.792 1 35.099
Arizona 2019 7 Furniture 1275.144 10 -481.6947
Arizona 2019 8 Furniture 120.576 8 33.1584
Arizona 2019 11 Furniture 1126.592 8 -141.8228
Arizona 2019 12 Furniture 1033.47 9 -27.8676000000001

To calculate OLAP cube by months, we have to change this query a little. The source cube contains hierarchies.

  • Geography hierarchy: Country->Region->State->City
  • Product hierarchy: Category->Sub_category->Product
  • Periods: Month, Quarter

Let's add hierarchy level field to each dimension (geography, product, period) to definitely identify hierarchy level.

Query 2

SQL
 SELECT
'State'  as GeoLevel,
State as Geo,
Ship_Year as SaleYear,
Ship_Month as SalePeriod,
max(Ship_MonthDate) as SalePeriodDate,
DATEADD(month, -1, max(Ship_MonthDate)) as PrevSalePeriodDate,
'M' as PeriodType,
'M' as PeriodSubType,
'Category' as ProductLevel,
Category as ProductName,
sum(Sales) as SumSales,	  
sum(Quantity) as SumQuantity,
sum(Profit) as SumProfit	
FROM  tblSales
GROUP BY State,  Category ,  Ship_Year, Ship_Month

Query 2 Results (Not All Data Included)

Geo
Level
Geo Sale
Year
Sale
Per
iod
Sale
Period
Date
Prev
Sale
Period
Date
Per
iod
Type
Per
iod
Sub
Type
Product
Level
Product
Name
Sum
Sales
Sum
Quan
tity
Sum
Profit
State Alabama 2018 9 01.09.2018 01.08.2018 M M Category Furniture 350.98 1 84.2352
State Alabama 2019 10 01.10.2019 01.09.2019 M M Category Furniture 10.16 2 3.4544
State Alabama 2019 12 01.12.2019 01.11.2019 M M Category Furniture 141.96 2 35.49
State Alabama 2018 5 01.05.2018 01.04.2018 M M Category Office Supplies 4.98 1 2.4402
State Alabama 2018 6 01.06.2018 01.05.2018 M M Category Office Supplies 247.65 9 75.6134
State Alabama 2018 8 01.08.2018 01.07.2018 M M Category Office Supplies 197.05 7 59.115
State Alabama 2018 9 01.09.2018 01.08.2018 M M Category Office Supplies 913.16 6 123.0272
State Alabama 2018 10 01.10.2018 01.09.2018 M M Category Office Supplies 88.86 12 37.9872
State Alabama 2018 11 01.11.2018 01.10.2018 M M Category Office Supplies 269.77 22 117.3024
State Alabama 2019 1 01.01.2019 01.12.2018 M M Category Office Supplies 56.37 16 23.1341
State Alabama 2019 4 01.04.2019 01.03.2019 M M Category Office Supplies 492.18 7 16.4172
State Alabama 2019 5 01.05.2019 01.04.2019 M M Category Office Supplies 26.16 4 12.8184
State Alabama 2019 6 01.06.2019 01.05.2019 M M Category Office Supplies 16.24 4 6.7064
State Alabama 2019 12 01.12.2019 01.11.2019 M M Category Office Supplies 728.8 10 349.281
State Alabama 2018 6 01.06.2018 01.05.2018 M M Category Technology 979.95 5 274.386
State Alabama 2018 7 01.07.2018 01.06.2018 M M Category Technology 29 2 7.25
State Alabama 2018 10 01.10.2018 01.09.2018 M M Category Technology 209.97 3 71.3898
State Alabama 2018 11 01.11.2018 01.10.2018 M M Category Technology 4359.96 12 1987.184
State Alabama 2019 4 01.04.2019 01.03.2019 M M Category Technology 25.98 2 1.5588
State Alabama 2019 11 01.11.2019 01.10.2019 M M Category Technology 239.92 8 23.992
State Alabama 2019 12 01.12.2019 01.11.2019 M M Category Technology 90.48 2 23.5248
State Arizona 2018 2 01.02.2018 01.01.2018 M M Category Furniture 14.368 2 3.9512
State Arizona 2018 4 01.04.2018 01.03.2018 M M Category Furniture 2229.726 15 -702.3924
State Arizona 2018 5 01.05.2018 01.04.2018 M M Category Furniture 111.888 7 22.3776
State Arizona 2018 9 01.09.2018 01.08.2018 M M Category Furniture 393.165 3 -204.4458
State Arizona 2018 10 01.10.2018 01.09.2018 M M Category Furniture 477.488 7 -34.641
State Arizona 2018 12 01.12.2018 01.11.2018 M M Category Furniture 455.97 6 -218.8656
State Arizona 2019 1 01.01.2019 01.12.2018 M M Category Furniture 83.413 3 -19.9054
State Arizona 2019 2 01.02.2019 01.01.2019 M M Category Furniture 455.614 11 -169.3996
State Arizona 2019 5 01.05.2019 01.04.2019 M M Category Furniture 209.979 7 -356.9643
State Arizona 2019 6 01.06.2019 01.05.2019 M M Category Furniture 280.792 1 35.099
State Arizona 2019 7 01.07.2019 01.06.2019 M M Category Furniture 1275.144 10 -481.6947
State Arizona 2019 8 01.08.2019 01.07.2019 M M Category Furniture 120.576 8 33.1584
State Arizona 2019 11 01.11.2019 01.10.2019 M M Category Furniture 1126.592 8 -141.8228
State Arizona 2019 12 01.12.2019 01.11.2019 M M Category Furniture 1033.47 9 -27.8676000000001
State Arizona 2018 3 01.03.2018 01.02.2018 M M Category Office Supplies 272.318 9 17.1947
State Arizona 2018 4 01.04.2018 01.03.2018 M M Category Office Supplies 396.813 20 -32.6021
State Arizona 2018 7 01.07.2018 01.06.2018 M M Category Office Supplies 95.424 13 -30.1926

In further manipulations, I'm going to use dynamic SQL generated with JavaScript. Let's parametrize Query 2 to unwind all levels of hierarchies (geography, products and periods). I use JavaScript here-docs for templating and variable interpolation. Thank God it now support here-docs and one doesn't have to deal with Perl here-docs. We have to generate Cartesian product of all hierarchy members and calculate measure aggergation on it.

Month Quarter

To generate SQL, copy JavaScript code to HTML file and open it with Chrome. Before running it in SQL Server Management Studio, create a scalar function for quarter arithmetic dbo.prevQuarter.

Month Quarter generation script sales_M_Q.html
Results of Month Quarter queries generation script sales_M_Q.html.

<script>

var  regions = [ //Geography hierarchy
                   {level:'Country', db_field: 'Country', groupby : 'Country'}
                   ,{level:'Region',  db_field: 'Region', groupby : 'Region'}
                   ,{level:'State',   db_field: 'State', groupby : 'State'}
     			   ,{level:'City',    db_field: " City + ' [' + State + ']'" , groupby : 'State, City '}
               ];

var products = [  //Products hierarhy
		    	    {level:'Category',     db_field: 'Category',     groupby : 'Category'}
				   ,{level:'Sub_Category', db_field: 'Sub_Category', groupby : 'Category, Sub_Category'}
				   ,{level:'Product_Name', db_field: 'Product_Name', groupby : 'Category, Sub_Category, Product_Name'},
  ];
  
document.writeln('<pre>');
document.writeln('CREATE VIEW cubeq.CUBE_Sales_M_Q as');
var xdate = Date(Date.now()); 
document.writeln('-- ' + Date(Date.now()).toString());
//======================================================================================================

for (var xregion of regions) {
		for (var xproduct of products) {
			let M_SQL = `
			  --================================================================================
			  -- Period:  M M                                                          
			  -- Region:   ${xregion.db_field}
			  -- Product:  ${xproduct.level}  
			  --================================================================================
			  SELECT
				'${xregion.level}'  as GeoLevel,   --Substitution of hierarchy level name
				 ${xregion.db_field} as Geo,       --Substitution of value
				 Ship_Year as SaleYear,
				 Ship_Month as SalePeriod,
				 max(Ship_MonthDate) as SalePeriodDate, 
				 DATEADD(month, -1, max(Ship_MonthDate)) as PrevSalePeriodDate,  --Previous period for PPG% calculations
				'M' as PeriodType,
				'M' as PeriodSubType,
				'${xproduct.level}' as ProductLevel,
				${xproduct.db_field} as ProductName,
				sum(Sales) as SumSales,	  
				sum(Quantity) as SumQuantity,
				sum(Profit) as SumProfit	
				FROM  tblSales
				GROUP BY ${xregion.groupby},  ${xproduct.groupby} ,  Ship_Year, Ship_Month
			UNION ALL
			`;
   document.writeln (M_SQL );
		}
}

//======================================================================================================

for (var xregion of regions) {
		for (var xproduct of products) {
			let Q_SQL = `
			  --================================================================================
			  -- Period:  Q Q                                                          
			  -- Region:   ${xregion.db_field}
			  -- Product:  ${xproduct.level}  
			  --================================================================================
			  SELECT
				'${xregion.level}'  as GeoLevel,
				 ${xregion.db_field} as Geo,
				 Ship_Year as SaleYear,
				 Ship_Quarter as SalePeriod,
				 datefromparts(Ship_Year, Ship_Quarter, 1) as SalePeriodDate,  --Store quarter in month field
				 dbo.prevQuarter ( datefromparts(Ship_Year, Ship_Quarter, 1) ) as PrevSalePeriodDate,
				'Q' as PeriodType,
				'Q' as PeriodSubType,
				'${xproduct.level}' as ProductLevel,
				${xproduct.db_field} as ProductName,
				sum(Sales) as SumSales,	  
				sum(Quantity) as SumQuantity,
				sum(Profit) as SumProfit	
				FROM  tblSales
				GROUP BY ${xregion.groupby},  ${xproduct.groupby} , Ship_Year, Ship_Quarter 
			UNION ALL
			`;
   document.writeln (Q_SQL );
		}
}

/* Function for quarter math in SQL Server
CREATE FUNCTION [dbo].[prevQuarter] ( 
   @CurrQuarter as Date
   )
     RETURNS Date AS 
     BEGIN
	  RETURN 
       CASE month(@CurrQuarter)
          WHEN 1 THEN    DATEADD(month, -9, @CurrQuarter ) 
          else  DATEADD(month, -1, @CurrQuarter ) 
	  END

END
*/

</script>

Create in SQL Server management studio View generated by this script (last UNION ALL should be removed), and it gives you an OLAP cube in form of a fact table which contains sales on all levels of flattened hierarchies. Note, that if you have a situation when the same item like city can be simultaneously in two states with the same name, you have to make it unique like I did in script. The nature of sales data doesn't allow to have the same product in multiple categories. So no problem when we are flattening hierarchy. Otherwise, we would have to store separate field with grouping path.

For quarterly periods, I store quarter in month field of a date. To calculate prev quarter, I use UDF dbo.prevQuarter for quarter math.

Year to Date Month Quarter

To calculate YTD periods, we have to loop through all 12 months in extra cycle. In order to remove periods greater than current, there is an extra condition in where section datefromparts( Ship_Year, ${xmonth}, 1) <= datefromparts(year(GETDATE()), month(GETDATE()),1).

YTD month quarter generation script sales_YTD_M_Q.zip YTD script results

<script>
 
var  regions = [ 
                   {level:'Country', db_field: 'Country', groupby : 'Country'}
                   ,{level:'Region',  db_field: 'Region', groupby : 'Region'}
                   ,{level:'State',   db_field: 'State', groupby : 'State'}
     			   ,{level:'City',    db_field: " City + ' [' + State + ']'" , groupby : 'State, City '}
               ];

var products = [
					{level:'Category',     db_field: 'Category',     groupby : 'Category'}
					,{level:'Sub_Category', db_field: 'Sub_Category', groupby : 'Category, Sub_Category'}
					,{level:'Product_Name', db_field: 'Product_Name', groupby : 'Category, Sub_Category, Product_Name'}
  ];
  
document.writeln('<pre>');
document.writeln('ALTER VIEW cubeq.CUBE_Sales_YTD_M_Q as<br>');
var xdate = Date(Date.now()); 
document.writeln('-- ' + Date(Date.now()).toString()+ '<br>');
//======================================================================================================
for (var xmonth of [1,2,3,4,5,6,7,8,9,10,11,12]) {
   for (var xregion of regions) {
		for (var xproduct of products) {
			let YTD_M_SQL = `
			  --================================================================================
			  -- Period:  YTD M M                                                          
			  -- Region:   ${xregion.db_field}
			  -- Product:  ${xproduct.level}  
			  --================================================================================
			  SELECT
				'${xregion.level}'  as GeoLevel,
				 ${xregion.db_field} as Geo,
				 Ship_Year as SaleYear,
				 ${xmonth} as SalePeriod,
				 datefromparts(Ship_Year, ${xmonth} , 1) as SalePeriodDate,
				 DATEADD(month, -1, datefromparts(Ship_Year, ${xmonth} , 1) ) as PrevSalePeriodDate,
				'YTD' as PeriodType,
				'M' as PeriodSubType,
				'${xproduct.level}' as ProductLevel,
				${xproduct.db_field} as ProductName,
				sum(Sales) as SumSales,	  
				sum(Quantity) as SumQuantity,
				sum(Profit) as SumProfit	
				FROM  tblSales
			    WHERE  Ship_Month between 1 and ${xmonth} 
				and datefromparts( Ship_Year, ${xmonth}, 1) <= datefromparts(year(GETDATE()), month(GETDATE()),1)
				GROUP BY ${xregion.groupby},  ${xproduct.groupby} ,  Ship_Year
			UNION ALL
			`;
   document.writeln (YTD_M_SQL );
		}
   }
}
//======================================================================================================
 for (var xquarter of [1,2,3,4]) {
   for (var xregion of regions) {
		for (var xproduct of products) {
			let YTD_Q_SQL = `
			  --================================================================================
			  -- Period:  YTD Q Q                                                          
			  -- Region:   ${xregion.db_field}
			  -- Product:  ${xproduct.level}  
			  --================================================================================
			  SELECT
				'${xregion.level}'  as GeoLevel,
				 ${xregion.db_field} as Geo,
				 Ship_Year as SaleYear,
				 ${xquarter} as SalePeriod,
				 datefromparts( Ship_Year, ${xquarter} ,1) as SalePeriodDate,
				 dbo.prevQuarter (  datefromparts( Ship_Year, ${xquarter} ,1)  )  as PrevSalePeriodDate,
				'YTD' as PeriodType,
				'Q' as PeriodSubType,
				'${xproduct.level}' as ProductLevel,
				${xproduct.db_field} as ProductName,
				sum(Sales) as SumSales,	  
				sum(Quantity) as SumQuantity,
				sum(Profit) as SumProfit	
				FROM  tblSales
			    WHERE 	Ship_Quarter between 1 and ${xquarter} 
				and datefromparts( Ship_Year, ${xquarter}, 1) <= datefromparts(year(GETDATE()), DATEPART(QUARTER, GETDATE()),1)
				GROUP BY ${xregion.groupby},  ${xproduct.groupby} ,  Ship_Year
			UNION ALL
			`;
   document.writeln (YTD_Q_SQL );
		}
   }
}

</script>

Moving Annual Total Month Quarter

To generate MAT, we've got to have 3 year trend in our database. In my script, I took only one last year. I loop through all periods in list and calculate 12-month aggregation. The condition cuts future periods the same way as in YTD script ${xperiod} <= datefromparts(year(GETDATE()), month(GETDATE()),1). As in previous scripts, I store quarter in month field. This will allow to calculate PPG% in further. MAT Script sales_MAT_M_Q.zip
MAT Script results.

<pre><script>
 
var  regions = [ 
                   {level:'Country', db_field: 'Country', groupby : 'Country'}
                   ,{level:'Region',  db_field: 'Region', groupby : 'Region'}
                   ,{level:'State',   db_field: 'State', groupby : 'State'}
     			   ,{level:'City',    db_field: " City + ' [' + State + ']'" , groupby : 'State, City '}
               ];

var products = [
					{level:'Category',     db_field: 'Category',     groupby : 'Category'}
					,{level:'Sub_Category', db_field: 'Sub_Category', groupby : 'Category, Sub_Category'}
					,{level:'Product_Name', db_field: 'Product_Name', groupby : 'Category, Sub_Category, Product_Name'}
  ];
  

 
var periods = [ //----CY
   'datefromparts(year(GETDATE()), 1  ,1)',
   'datefromparts(year(GETDATE()), 2  ,1)',   
   'datefromparts(year(GETDATE()), 3  ,1)',
   'datefromparts(year(GETDATE()), 4  ,1)',
   'datefromparts(year(GETDATE()), 5  ,1)',
   'datefromparts(year(GETDATE()), 6  ,1)',
   'datefromparts(year(GETDATE()), 7  ,1)',
   'datefromparts(year(GETDATE()), 8  ,1)',
   'datefromparts(year(GETDATE()), 9  ,1)',
   'datefromparts(year(GETDATE()), 10 ,1)',
   'datefromparts(year(GETDATE()), 11 ,1)',
   'datefromparts(year(GETDATE()), 12 ,1)'
   //---------PY
   //'datefromparts(year(GETDATE())-1, 1  ,1)',
   //'datefromparts(year(GETDATE())-1, 2  ,1)',   
   //'datefromparts(year(GETDATE())-1, 3  ,1)',
   //'datefromparts(year(GETDATE())-1, 4  ,1)',
   //'datefromparts(year(GETDATE())-1, 5  ,1)',
   //'datefromparts(year(GETDATE())-1, 6  ,1)',
   //'datefromparts(year(GETDATE())-1, 7  ,1)',
   //'datefromparts(year(GETDATE())-1, 8  ,1)',
   //'datefromparts(year(GETDATE())-1, 9  ,1)',
   //'datefromparts(year(GETDATE())-1, 10 ,1)',
   //'datefromparts(year(GETDATE())-1, 11 ,1)',
   //'datefromparts(year(GETDATE())-1, 12 ,1)'
];

document.writeln('<pre>');
document.writeln('ALTER VIEW cubeq.CUBE_Sales_MAT_M_Q as<br>');
var xdate = Date(Date.now()); 
document.writeln('-- ' + Date(Date.now()).toString()+ '<br>');
//======================================================================================================
for (var xperiod of periods ) {
   for (var xregion of regions) {
		for (var xproduct of products) {
			let MAT_M_SQL = `
			  --================================================================================
			  -- Period:  MAT M M                                                          
			  -- Region:   ${xregion.db_field}
			  -- Product:  ${xproduct.level}  
			  --================================================================================
			  SELECT
				'${xregion.level}'  as GeoLevel,
				 ${xregion.db_field} as Geo,
				 year(  ${xperiod} ) as SaleYear,
				 month( ${xperiod} )  as SalePeriod,
				 ${xperiod} as SalePeriodDate,
				 DATEADD(month, -1, ${xperiod} ) as PrevSalePeriodDate,
				'MAT' as PeriodType,
				'M' as PeriodSubType,
				'${xproduct.level}' as ProductLevel,
				${xproduct.db_field} as ProductName,
				sum(Sales) as SumSales,	  
				sum(Quantity) as SumQuantity,
				sum(Profit) as SumProfit	
				FROM  tblSales
			    WHERE  ${xperiod} <= datefromparts(year(GETDATE()), month(GETDATE()),1) and 
				Ship_MonthDate between  DATEADD(month, -11, ${xperiod} ) and ${xperiod} 
				GROUP BY ${xregion.groupby},  ${xproduct.groupby} 
			UNION ALL
			`;
   document.writeln (MAT_M_SQL );
		}
   }
}
 
//======================================================================================================

var periodsQ = [ //----CY
   {value:'datefromparts(year(GETDATE()), 3  ,1)',quarter:'1'},
   {value:'datefromparts(year(GETDATE()), 6  ,1)',quarter:'2'}, 
   {value:'datefromparts(year(GETDATE()), 9  ,1)',quarter:'3'},
   {value:'datefromparts(year(GETDATE()), 12 ,1)',quarter:'4'}
];

for (var xperiod of periodsQ ) {
   for (var xregion of regions) {
		for (var xproduct of products) {
			let MAT_Q_SQL = `
              --================================================================================
			  -- Period:  MAT Q Q                                                          
			  -- Region:   ${xregion.db_field}
			  -- Product:  ${xproduct.level}  
			  --================================================================================
			  SELECT
				'${xregion.level}'  as GeoLevel,
				 ${xregion.db_field} as Geo,
				 year(  ${xperiod.value} ) as SaleYear,
				 ${xperiod.quarter} as SalePeriod,
				 datefromparts( year(${xperiod.value}), ${xperiod.quarter} ,1) as SalePeriodDate,
				 dbo.prevQuarter (   datefromparts( year(${xperiod.value}), ${xperiod.quarter} ,1)   )  as PrevSalePeriodDate,
				'MAT' as PeriodType,
				'Q' as PeriodSubType,
				'${xproduct.level}' as ProductLevel,
				${xproduct.db_field} as ProductName,
				sum(Sales) as SumSales,	  
				sum(Quantity) as SumQuantity,
				sum(Profit) as SumProfit	
				FROM  tblSales
			    WHERE  ${xperiod.value} <= datefromparts(year(GETDATE()), month(GETDATE()),1) and 
				Ship_MonthDate between  DATEADD(month, -11, ${xperiod.value} ) and ${xperiod.value} 
				GROUP BY ${xregion.groupby},  ${xproduct.groupby} 
			UNION ALL
			`;
   document.writeln (MAT_Q_SQL );
		}
   }
}


</script>

Resulting Cube

After generating all 3 queries, you can create resulting query and do SELECT INTO tblCube. Then, you may add some indexes on dimensions to increase a performance.

SQL
select * from [cubeq].[CUBE_Sales_M_Q]
UNION ALL
select * from [cubeq].[CUBE_Sales_YTD_M_Q]
UNION ALL
select * from [cubeq].[CUBE_Sales_MAT_M_Q]

Conclusion

This approach allows calculating sales analytics free wiothout using expensive systems.

Source INSERT script is about 3MB. The resulting cube is 31MB. It's 10 time growth. This happens because of combinatory data explosion.

In other project, 50 MB flat file turned in 500MB SQL Server database. This is not a significant volume for modern databases.

Already at this stage, you can draw a dashboard basing on these data in SSRS or MS Report Builder. Of course, you will be limited with flat filtering Excel autofilter way without hierarchy metainformation, but data will be applicable for analysis. You can do the same trick in SQLite, MySQL or any free database.

I used this approach in two sales analytics projects and it worked well. First is KPI kalculation for Pharma company, second is reporting portal for B2B application. In future posts, I will show how to build free OLAP web app.

History

  • 17th June, 2019: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)