OLAP for Free

• Download res_ytd.zip - 3.7 KB
• Download res_mq.zip - 1.4 KB
• Download res_mat.zip - 3.6 KB
• Download insert_script.zip - 369.9 KB
• Download sales_YTD_M_Q.zip - 385 KB
• Download sales_MAT_M_Q.zip - 1.6 KB
• Download sales_M_Q.zip - 1.5 KB

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)

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

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)

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

 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)

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.

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

• 17^th June, 2019: Initial version