免费 OLAP
如何在您的项目中实现免费的 OLAP Cube
- 下载 res_ytd.zip - 3.7 KB
- 下载 res_mq.zip - 1.4 KB
- 下载 res_mat.zip - 3.6 KB
- 下载 insert_script.zip - 369.9 KB
- 下载 sales_YTD_M_Q.zip - 385 KB
- 下载 sales_MAT_M_Q.zip - 1.6 KB
- 下载 sales_M_Q.zip - 1.5 KB
免费OLAP分步指南
在本篇文章中，我将展示如何完全免费实现OLAP。您无需为MSAS、Cognos或类似产品花费一分钱。如果您查看OLAP系统的价格，会发现它们成本高达数万美元，还不包括每个报告用户的年度费用。另一方面，如今硬件价格非常便宜，而且有许多免费的数据库。对于中型项目，在许多情况下，最好购买RAD或HDD，并以硬件资源为代价来解决问题。
OLAP立方体生成器以表格数据为输入，并生成特殊格式的立方体文件，其中包含聚合数据。如果我们从SQL Server数据库中提取表格数据，对其进行聚合，然后将其放回SQL Server表中，会怎么样？我将使用Tableau Superstore文件作为示例数据源。该文件可在Tableau论坛上找到。Superstore文件
我稍微修改了一下。我用`替换了引号和撇号。并将标题中的空格和连字符替换为下划线符号。我还将发货日期增加了2年，并且只取2018年和2019年。所有OLAP查询都会返回大量数据，所以我只提供了一个摘录来理解数据结构，而不是全部数据。如果您想要全部数据，请自行运行。我将Year、Month和Quarter放入单独的列。我还添加了格式为日期的Shipment月份。所有这些都使SQL查询更简单。
源数据样本（Tableau Superstore摘录）
（点击放大表格）
您可以使用SQL脚本生成我使用的完整表。Superstore的SQL INSERT脚本
最原始的分析查询如下所示...
简单的分析查询
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
此查询的结果是按月份、按类别、按州划分的销售额。
查询1结果（不包含全部数据）
| 状态 | Ship_Year | Ship_Month | 类别 | 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 | 技术栈 | 979.95 | 5 | 274.386 |
| Alabama | 2018 | 7 | 技术栈 | 29 | 2 | 7.25 |
| Alabama | 2018 | 10 | 技术栈 | 209.97 | 3 | 71.3898 |
| Alabama | 2018 | 11 | 技术栈 | 4359.96 | 12 | 1987.184 |
| Alabama | 2019 | 4 | 技术栈 | 25.98 | 2 | 1.5588 |
| Alabama | 2019 | 11 | 技术栈 | 239.92 | 8 | 23.992 |
| Alabama | 2019 | 12 | 技术栈 | 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 |
为了按月计算OLAP立方体，我们必须稍微修改此查询。源立方体包含层次结构。
- 地理层次结构：Country->Region->State->City
- 产品层次结构：Category->Sub_category->Product
- 期间：Month, Quarter
让我们为每个维度（geography、product、period）添加层次结构级别字段，以明确标识层次结构级别。
查询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
查询2结果（不包含全部数据）
| Geo 信号强度 | Geo | Sale 年份 | Sale Per iod | Sale 周期 日期 | Prev Sale 周期 日期 | Per iod 类型 | Per iod Sub 类型 | 产品 信号强度 | 产品 名称 | Sum 销售 | Sum Quan tity | Sum Profit |
| 状态 | Alabama | 2018 | 9 | 01.09.2018 | 01.08.2018 | M | M | 类别 | Furniture | 350.98 | 1 | 84.2352 |
| 状态 | Alabama | 2019 | 10 | 01.10.2019 | 01.09.2019 | M | M | 类别 | Furniture | 10.16 | 2 | 3.4544 |
| 状态 | Alabama | 2019 | 12 | 01.12.2019 | 01.11.2019 | M | M | 类别 | Furniture | 141.96 | 2 | 35.49 |
| 状态 | Alabama | 2018 | 5 | 01.05.2018 | 01.04.2018 | M | M | 类别 | Office Supplies | 4.98 | 1 | 2.4402 |
| 状态 | Alabama | 2018 | 6 | 01.06.2018 | 01.05.2018 | M | M | 类别 | Office Supplies | 247.65 | 9 | 75.6134 |
| 状态 | Alabama | 2018 | 8 | 01.08.2018 | 01.07.2018 | M | M | 类别 | Office Supplies | 197.05 | 7 | 59.115 |
| 状态 | Alabama | 2018 | 9 | 01.09.2018 | 01.08.2018 | M | M | 类别 | Office Supplies | 913.16 | 6 | 123.0272 |
| 状态 | Alabama | 2018 | 10 | 01.10.2018 | 01.09.2018 | M | M | 类别 | Office Supplies | 88.86 | 12 | 37.9872 |
| 状态 | Alabama | 2018 | 11 | 01.11.2018 | 01.10.2018 | M | M | 类别 | Office Supplies | 269.77 | 22 | 117.3024 |
| 状态 | Alabama | 2019 | 1 | 01.01.2019 | 01.12.2018 | M | M | 类别 | Office Supplies | 56.37 | 16 | 23.1341 |
| 状态 | Alabama | 2019 | 4 | 01.04.2019 | 01.03.2019 | M | M | 类别 | Office Supplies | 492.18 | 7 | 16.4172 |
| 状态 | Alabama | 2019 | 5 | 01.05.2019 | 01.04.2019 | M | M | 类别 | Office Supplies | 26.16 | 4 | 12.8184 |
| 状态 | Alabama | 2019 | 6 | 01.06.2019 | 01.05.2019 | M | M | 类别 | Office Supplies | 16.24 | 4 | 6.7064 |
| 状态 | Alabama | 2019 | 12 | 01.12.2019 | 01.11.2019 | M | M | 类别 | Office Supplies | 728.8 | 10 | 349.281 |
| 状态 | Alabama | 2018 | 6 | 01.06.2018 | 01.05.2018 | M | M | 类别 | 技术栈 | 979.95 | 5 | 274.386 |
| 状态 | Alabama | 2018 | 7 | 01.07.2018 | 01.06.2018 | M | M | 类别 | 技术栈 | 29 | 2 | 7.25 |
| 状态 | Alabama | 2018 | 10 | 01.10.2018 | 01.09.2018 | M | M | 类别 | 技术栈 | 209.97 | 3 | 71.3898 |
| 状态 | Alabama | 2018 | 11 | 01.11.2018 | 01.10.2018 | M | M | 类别 | 技术栈 | 4359.96 | 12 | 1987.184 |
| 状态 | Alabama | 2019 | 4 | 01.04.2019 | 01.03.2019 | M | M | 类别 | 技术栈 | 25.98 | 2 | 1.5588 |
| 状态 | Alabama | 2019 | 11 | 01.11.2019 | 01.10.2019 | M | M | 类别 | 技术栈 | 239.92 | 8 | 23.992 |
| 状态 | Alabama | 2019 | 12 | 01.12.2019 | 01.11.2019 | M | M | 类别 | 技术栈 | 90.48 | 2 | 23.5248 |
| 状态 | Arizona | 2018 | 2 | 01.02.2018 | 01.01.2018 | M | M | 类别 | Furniture | 14.368 | 2 | 3.9512 |
| 状态 | Arizona | 2018 | 4 | 01.04.2018 | 01.03.2018 | M | M | 类别 | Furniture | 2229.726 | 15 | -702.3924 |
| 状态 | Arizona | 2018 | 5 | 01.05.2018 | 01.04.2018 | M | M | 类别 | Furniture | 111.888 | 7 | 22.3776 |
| 状态 | Arizona | 2018 | 9 | 01.09.2018 | 01.08.2018 | M | M | 类别 | Furniture | 393.165 | 3 | -204.4458 |
| 状态 | Arizona | 2018 | 10 | 01.10.2018 | 01.09.2018 | M | M | 类别 | Furniture | 477.488 | 7 | -34.641 |
| 状态 | Arizona | 2018 | 12 | 01.12.2018 | 01.11.2018 | M | M | 类别 | Furniture | 455.97 | 6 | -218.8656 |
| 状态 | Arizona | 2019 | 1 | 01.01.2019 | 01.12.2018 | M | M | 类别 | Furniture | 83.413 | 3 | -19.9054 |
| 状态 | Arizona | 2019 | 2 | 01.02.2019 | 01.01.2019 | M | M | 类别 | Furniture | 455.614 | 11 | -169.3996 |
| 状态 | Arizona | 2019 | 5 | 01.05.2019 | 01.04.2019 | M | M | 类别 | Furniture | 209.979 | 7 | -356.9643 |
| 状态 | Arizona | 2019 | 6 | 01.06.2019 | 01.05.2019 | M | M | 类别 | Furniture | 280.792 | 1 | 35.099 |
| 状态 | Arizona | 2019 | 7 | 01.07.2019 | 01.06.2019 | M | M | 类别 | Furniture | 1275.144 | 10 | -481.6947 |
| 状态 | Arizona | 2019 | 8 | 01.08.2019 | 01.07.2019 | M | M | 类别 | Furniture | 120.576 | 8 | 33.1584 |
| 状态 | Arizona | 2019 | 11 | 01.11.2019 | 01.10.2019 | M | M | 类别 | Furniture | 1126.592 | 8 | -141.8228 |
| 状态 | Arizona | 2019 | 12 | 01.12.2019 | 01.11.2019 | M | M | 类别 | Furniture | 1033.47 | 9 | -27.8676000000001 |
| 状态 | Arizona | 2018 | 3 | 01.03.2018 | 01.02.2018 | M | M | 类别 | Office Supplies | 272.318 | 9 | 17.1947 |
| 状态 | Arizona | 2018 | 4 | 01.04.2018 | 01.03.2018 | M | M | 类别 | Office Supplies | 396.813 | 20 | -32.6021 |
| 状态 | Arizona | 2018 | 7 | 01.07.2018 | 01.06.2018 | M | M | 类别 | Office Supplies | 95.424 | 13 | -30.1926 |
在后续操作中，我将使用JavaScript生成的动态SQL。让我们参数化查询2，以展开所有层次结构（地理、产品和期间）的级别。我在此处使用JavaScript的here-docs进行模板化和变量插值。感谢上帝，它现在支持here-docs，您无需处理Perl的here-docs。我们必须生成所有层次结构成员的笛卡尔积，并在其上计算度量聚合。
Month Quarter
要生成SQL，请将JavaScript代码复制到HTML文件中，然后在Chrome中打开它。在SQL Server Management Studio中运行之前，为季度算术创建一个标量函数dbo.prevQuarter。
Month Quarter生成脚本 sales_M_Q.html
Month Quarter查询生成脚本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>
在SQL Server Management Studio中创建由此脚本生成的视图（应删除最后的UNION ALL），它将为您提供一个OLAP立方体，形式为一个事实表，其中包含所有已展平层次结构级别的销售额。请注意，如果您遇到同一个项目（如city）同时出现在两个同名州的情况，您需要像我在脚本中那样使其唯一。销售数据的性质不允许同一产品出现在多个类别中。因此，在展平层次结构时没有问题。否则，我们就必须存储一个单独的字段来表示分组路径。
对于季度期间，我将季度存储在date的month字段中。为了计算上一个季度，我使用UDF dbo.prevQuarter进行季度数学计算。
Year to Date Month Quarter
为了计算YTD期间，我们必须在额外的循环中遍历所有12个月。为了删除大于当前日期的期间，where部分有一个额外的条件datefromparts( Ship_Year, ${xmonth}, 1) <= datefromparts(year(GETDATE()), month(GETDATE()),1)。
YTD month quarter生成脚本 sales_YTD_M_Q.zip YTD脚本 结果
<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
要生成MAT，我们必须在数据库中有3年的趋势。在我的脚本中，我只取了最后一年。我遍历列表中的所有期间并计算12个月的聚合。条件以与YTD脚本相同的方式截断未来期间${xperiod} <= datefromparts(year(GETDATE()), month(GETDATE()),1)。与之前的脚本一样，我将季度存储在month字段中。这将允许在后续计算PPG%。MAT脚本 sales_MAT_M_Q.zip
MAT脚本结果.
<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>
结果立方体
在生成所有3个查询后，您可以创建最终查询并执行SELECT INTO tblCube。然后，您可以为维度添加一些索引以提高性能。
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]
结论
这种方法允许免费计算销售分析，而无需使用昂贵的系统。
源INSERT脚本大约为3MB。生成立方体为31MB。这是10倍的增长。这是由于组合数据的爆炸式增长。
在另一个项目中，50MB的平面文件变成了500MB的SQL Server数据库。对于现代数据库来说，这不算很大的量。
即使在这一阶段，您也可以在SSRS或MS Report Builder中基于这些数据绘制仪表板。当然，您会受到类似于Excel自动筛选的平面筛选的限制，而没有层次结构元信息，但数据将适用于分析。您也可以在SQLite、MySQL或任何免费数据库中执行相同的技巧。
我在两个销售分析项目中使用过这种方法，效果很好。第一个是为制药公司进行KPI计算，第二个是为B2B应用程序构建的报告门户。在以后的文章中，我将展示如何构建免费的OLAP Web应用程序。
历史
- 2019年6月17日：初始版本