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 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
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
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 = [
{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('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 );
}
}
</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 = [
'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)'
];
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 = [
{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
- 17th June, 2019: Initial version