SSAS 通过 ETL 自动建立分区
2024-10-09 05:01:06
一、动态分区的好处就不说了，随着时间的推移，不可能一个度量值组都放在一个分区中，处理速度非常慢，如何动态添加分区，如何动态处理分区，成为了很多新手BI工程师一个头痛的问题，废话不多说，分享一下我的经验。
二、首先讲一下大致的流程,主要是通过SSIS进行任务的处理，本文主要是按照月进行分区，当然分区的规则大家可以根据自己的需求制定。
该包用到的所有变量
三、对上面四个步骤分别讲解一下。
1、得到所有分区：
①、主要设置如下图
②、输出的结果集应该传给变量Partitions
③、SQLStatement为：（主要依据创建分区的语句中需要的参数的值）
1 SELECT 'RmyyHisDW' AS DataSoureID,--数据源
2 'RmyyMZ' AS CubeName,--分区来自哪一个cube
3 'RmyyMZ' AS CubeID,
4 'Fact Mz Visit Table' AS MeasureGroup,--指定是一个度量值组
5 'Fact Mz Visit Table' AS MeasureGroupID,
6 'Fact Mz Visit Table' + Cast(MonthInfo.YearMonth AS VARCHAR(6)) AS Partition,--分区名称=度量值组名称+年月
7 'SELECT [dbo].[fact_mz_visit_table].[patient_id],
8 [dbo].[fact_mz_visit_table].[times],
9 [dbo].[fact_mz_visit_table].[name],
10 [dbo].[fact_mz_visit_table].[age],
11 [dbo].[fact_mz_visit_table].[ampm],
12 [dbo].[fact_mz_visit_table].[charge_type],
13 [dbo].[fact_mz_visit_table].[clinic_type],
14 [dbo].[fact_mz_visit_table].[contract_code],
15 [dbo].[fact_mz_visit_table].[visit_dept],
16 [dbo].[fact_mz_visit_table].[doctor_code],
17 [dbo].[fact_mz_visit_table].[gh_date],
18 [dbo].[fact_mz_visit_table].[gh_date_time],
19 [dbo].[fact_mz_visit_table].[gh_opera],
20 [dbo].[fact_mz_visit_table].[haoming_code],
21 [dbo].[fact_mz_visit_table].[icd_code],
22 [dbo].[fact_mz_visit_table].[icd_code1],
23 [dbo].[fact_mz_visit_table].[icd_code2],
24 [dbo].[fact_mz_visit_table].[icd_code3],
25 [dbo].[fact_mz_visit_table].[response_type],
26 [dbo].[fact_mz_visit_table].[visit_date],
27 [dbo].[fact_mz_visit_table].[visit_date_time],
28 [dbo].[fact_mz_visit_table].[visit_flag]
29 FROM [dbo].[fact_mz_visit_table]
30 WHERE visit_flag <> 9 and where_clause' AS SQL,--要进行分区的SQL
31 cast(MinDateKey as varchar(8)) as MinDateKey,--最小datekey
32 cast(MaxDateKey as varchar(8)) as MaxDateKey--最大datekey
33 FROM (SELECT t1.YearMonth,
34 (SELECT Min(datekey)
35 FROM dim_date t2
36 WHERE CONVERT(VARCHAR(6), t2.Date, 112) = t1.YearMonth) AS MinDateKey,
37 (SELECT Max(datekey)
38 FROM dim_date t2
39 WHERE CONVERT(VARCHAR(6), t2.Date, 112) = t1.YearMonth) AS MaxDateKey
40 FROM (SELECT DISTINCT CONVERT(VARCHAR(6), Date, 112) AS YearMonth
41 FROM dim_date) AS t1) MonthInfo
42 WHERE EXISTS(SELECT *
43 FROM fact_mz_visit_table
44 WHERE visit_date BETWEEN MonthInfo.MinDateKey AND MonthInfo.MaxDateKey)
注意：SQL字段中最后面有个where_clause ，在“判断分区脚本任务”中的C#脚本中会替换成后面的where条件，也就是将MinDateKey和MaxDateKey加入条件限制，进行分区。
④、步骤③执行的结果为
2、Foreach 循环容器（主要循环执行上面的sql语句执行的结果）相关设置如下图
注意：变量映射按照sql语句中的字段名的顺序
3、判断分区是否存在，主要是通过步骤2中传出的参数判断cube中是否有该分区，有则不创建，无则通过Anaysis Services执行DDL任务来创建。
①、具体设置如下:
②、点击编辑脚本任务
需要引用AMO
③主要代码为
/*
Microsoft SQL Server Integration Services Script Task
Write scripts using Microsoft Visual C# 2008.
The ScriptMain is the entry point class of the script.
*/ using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using Microsoft.AnalysisServices; namespace ST_f33f263fa3864817a3291fc4715774d3.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{ #region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion /*
The execution engine calls this method when the task executes.
To access the object model, use the Dts property. Connections, variables, events,
and logging features are available as members of the Dts property as shown in the following examples. To reference a variable, call Dts.Variables["MyCaseSensitiveVariableName"].Value;
To post a log entry, call Dts.Log("This is my log text", 999, null);
To fire an event, call Dts.Events.FireInformation(99, "test", "hit the help message", "", 0, true); To use the connections collection use something like the following:
ConnectionManager cm = Dts.Connections.Add("OLEDB");
cm.ConnectionString = "Data Source=localhost;Initial Catalog=AdventureWorks;Provider=SQLNCLI10;Integrated Security=SSPI;Auto Translate=False;"; Before returning from this method, set the value of Dts.TaskResult to indicate success or failure. To open Help, press F1.
*/ public void Main()
{
// TODO: Add your code here
// Dts.TaskResult = (int)ScriptResults.Success;
//将参数赋给变量
String sPartition = (String)Dts.Variables["Partition"].Value;
String sCubeName = (String)Dts.Variables["CubeName"].Value;
String sMeasureGroup = (String)Dts.Variables["MeasureGroup"].Value;
String sServer = "localhost";
String sDataBaseID = (String)Dts.Variables["DatabaseID"].Value; String sCubeID = (String)Dts.Variables["CubeID"].Value;
String sMeasureGroupID = (String)Dts.Variables["MeasureGroupID"].Value;
String sDataSoureID = (String)Dts.Variables["DataSoureID"].Value;
String sSQL = (String)Dts.Variables["SQL"].Value;
String sMaxDateKey = (String)Dts.Variables["MaxDateKey"].Value;
String sMinDateKey = (String)Dts.Variables["MinDateKey"].Value;
string aSql = sSQL.Replace("where_clause", "visit_date >=" + sMinDateKey + " and visit_date <=" + sMaxDateKey); ConnectionManager cm = Dts.Connections.Add("MSOLAP100");
cm.ConnectionString = "Provider=MSOLAP.4;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=" + sDataBaseID; Microsoft.AnalysisServices.Server aServer = new Server();
aServer.Connect(sServer);
Microsoft.AnalysisServices.Database aDatabase = aServer.Databases.FindByName(sDataBaseID);
Microsoft.AnalysisServices.Cube aCube = aDatabase.Cubes.FindByName(sCubeName);
Microsoft.AnalysisServices.MeasureGroup aMeasureGroup = aCube.MeasureGroups.FindByName(sMeasureGroup);
//判断分区是否存在
if (aMeasureGroup.Partitions.Contains(sPartition))
{
Dts.Variables["IsNetePresent"].Value = false;
Dts.Variables["Xmla_Script"].Value = "";
Dts.TaskResult = (int)ScriptResults.Success;
}
else
{
Dts.Variables["IsNetePresent"].Value = true;
Dts.Variables["Xmla_Script"].Value =
"<Create xmlns=\"http://schemas.microsoft.com/analysisservices/2003/engine\">"
+ "<ParentObject>"
+ "<DatabaseID>" + sDataBaseID + "</DatabaseID>"
+ "<CubeID>" + sCubeID + "</CubeID>"
+ "<MeasureGroupID>" + sMeasureGroupID + "</MeasureGroupID>"
+ "</ParentObject>"
+ "<ObjectDefinition>"
+ "<Partition xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" "
+"xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xmlns:ddl2=\"http://schemas.microsoft.com/analysisservices/2003/engine/2\" xmlns:ddl2_2=\"http://schemas.microsoft.com/analysisservices/2003/engine/2/2\" xmlns:ddl100_100=\"http://schemas.microsoft.com/analysisservices/2008/engine/100/100\" xmlns:ddl200=\"http://schemas.microsoft.com/analysisservices/2010/engine/200\" xmlns:ddl200_200=\"http://schemas.microsoft.com/analysisservices/2010/engine/200/200\">"
+ "<ID>" + sPartition + "</ID>"
+ "<Name>" + sPartition + "</Name>"
+ "<Source xsi:type=\"QueryBinding\">"
+ "<DataSourceID>" + sDataSoureID + "</DataSourceID>"
+ "<QueryDefinition>" + aSql + "</QueryDefinition>"
+ "</Source>"
+ "<StorageMode>Molap</StorageMode> <ProcessingMode>Regular</ProcessingMode>"
+ "<ProactiveCaching> <SilenceInterval>-PT1S</SilenceInterval> <Latency>-PT1S</Latency> <SilenceOverrideInterval>-PT1S</SilenceOverrideInterval> <ForceRebuildInterval>-PT1S</ForceRebuildInterval>"
+ "<Source xsi:type=\"ProactiveCachingInheritedBinding\" /> </ProactiveCaching>"
+ "</Partition>"
+ "</ObjectDefinition>"
+ "</Create>";
Dts.TaskResult = (int)ScriptResults.Success;
}
}
}
}
④、判断是否执行下一步
4、不存在创建分区（主要执行步骤3传过来的Xmla_Script），具体设置如下：
5、执行任务，查看结果：
标签: SSAS
最新文章
- 细说websocket - php篇
- css js 的引入方式和书写位置
- C# 中的"yield"使用
- 用jmap分析java程序
- [Android]天气App 3 网络数据的请求和Json解析
- js之replace实现简单模板替换引擎
- struts2操作数据库
- 用Python制作markdown编辑器
- SQL Server 触发器的修改与删除
- 使用Xib添加自定义View
- 第2阶段——编写uboot之编译测试以及改进(3)
- 使用.NET开发AutoCAD——设计师不做画图匠（一）
- Flask 蓝图，数据库链接
- 正则简单操作cookie、url search
- c++ string去除首尾 空格、\n、\r、\t
- BZOJ 3174 拯救小矮人(贪心+DP)
- RGB转LAB色彩空间
- 如何手动关闭tomcat服务，不在Eclipse中的server里按那个红色按钮关
- AVL树Python实现
- 调用父类构造器：super