Project REAL分析服务技术探讨
来源: 数据库 SQL Server | 作者: landluo | 发布: 2009-7-08 10:09
程序包使用如下两种连接对象。
◆AS 数据库
这个连接对象包括SQL Server 2005 分析服务数据库，在数据库库中如果找不到度量组分割表，系统会选中这个分割表，并创建它们。
◆RDBMS 数据库
这个连接对象包含了SQL Server 2005关系型数据库，也就是查找每周实际表的地方。
最后，我们想在评论以下集合的设计。在你运行完程序包后，你将看到那里并没有为了集合设计而设计的集合。一次为多个分割表创建集合确实很简单。我们已经实现一次创建几百个分割表。
首先，在Cube窗口中选择多个分割表，只需要按住Ctrl或者Shift，然后再选择即可。鼠标右键单击，运行Design Storage Wizard用来创建集合。向导可以在所有选中的分割表中定义集合。把这个操作应用于Store Inventory、Store Sales、DC Inventory各一遍，就完成工作了。
注意：这个程序包假定关系型数据库分割表模式是基于每周的数据的，并且是根据表名称来编码的，很容易根据这个例子来修改程序包使其符合其它模式的要求。简单的修改Get Partition List任务(或者将它扩展成一系列任务)，然后基于不同的模式构建一个Tables行集。只要这个Tables行集(不管它是怎么被构建的)在格式上是一致的，程序包的余下部分就能顺利的被重用。
下面的源代码就是Partition Already There?脚本的一个范例。
' Microsoft Data Transformation Services (DTS) Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
' NOTE:
' In order to get AMO to be imported you must copy the following
' file from:
' C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\
' Microsoft.AnalysisServices.dll
' to the folder
' \Microsoft.NET\Framework\v2.0.
' And then you must add a reference to the assembly in the project
Imports Microsoft.AnalysisServices
Public Class ScriptMain
Public Sub Main()
' Get Server and Database name from DTS connection object
Dim oConn As ConnectionManager
oConn = Dts.Connections("AS database")
Dim sServer As String = _
CStr(oConn.Properties("ServerName").GetValue(oConn))
Dim sDatabase As String = _
CStr(oConn.Properties("InitialCatalog").GetValue(oConn))
' By convention, we know that the database, data source and
' cube are the same name
Dim sDataSource As String = sDatabase
Dim sCube As String = sDatabase
Dim oTable As Variable = Dts.Variables("Table")
Dim sTable As String = CStr(oTable.Value)
Dim sPartition As String = GetPartition(sTable)
Dim sMeasureGroup As String = GetMeasureGroup(sTable)
' We have all of the information about the partition -- use AMO
' to see if it is present. Save in a variable for later reference
Dts.Variables("IsNotPresent").Value = _
Not IsPartitionThere(sServer, sDatabase, sCube, _
sMeasureGroup, sPartition)
' Generate and save the XMLA script (will be executed in a
' Execute Analysis Services DDL task later on). Save in a
' variable for later reference
Dim sXMLA As String = GenerateXMLAScript(sDatabase, sDataSource, _
sCube, sMeasureGroup, sPartition, sTable)
Dts.Variables("XMLA_Script").Value = sXMLA
MsgBox(sXMLA, MsgBoxStyle.OkOnly, "XMLA Script")
Dts.TaskResult = Dts.Results.Success
End Sub
Private Function GetMeasureGroup(ByVal sMG As String) As String
' All tables are in the format: vTbl_Fact__WE_YYYY_MM_DD
' e.g. vTbl_Fact_Store_Sales_WE_2003_12_27
' Measure group names (from this) are: , e.g. Store Sales
' and the name may have embedded undorscores (_) which need to
' be replaced with spaces
Dim i_WE_location As Integer = InStr(sMG, "_WE_")
Dim i_FACT_location As Integer = Len("vTbl_Fact_")
sMG = Left(sMG, i_WE_location - 1)
sMG = Right(sMG, (Len(sMG) - i_FACT_location))
sMG = Replace(sMG, "_", " ")
Return sMG
End Function
Private Function GetPartition(ByVal sPart As String) As String
' All tables are in the format: vTbl_Fact__WE_YYYY_MM_DD
' e.g. vTbl_Fact_Store_Sales_WE_2003_12_27
' Partition names (from this) are: WE YYYY MM DD,
' e.g. Store Sales WE 2003 12 27
' and the name may have embedded undorscores (_) which need to
' be replaced with spaces
Dim i_FACT_location As Integer = Len("vTbl_Fact_")
sPart = Right(sPart, (Len(sPart) - i_FACT_location))
sPart = Replace(sPart, "_", " ")
Return sPart
End Function
Public Function GenerateXMLAScript(ByVal sDatabase As String, _
ByVal sDataSource As String, ByVal sCube As String, _
ByVal sMeasureGroup As String, ByVal sPartition As String, _
ByVal sTable As String) As String
Dim sX As String
sX = ""
'
' XMLA script is missing the following clauses:
' 1) annotations (not needed)
' 2) physical storage, e.g. proactive caching settings, etc.
' Note: not needed since default values used
' 3) linkage to aggregation designs (TBD) -- we need a management
' utility to control aggregation usage
'
' Note: because of quoting rules conflict between VB.NET and
' XMLA scripts, all double-quotes (") are replaced with
' uparrows (^)
'
sX = sX & "" & vbCrLf
sX = sX & "
" & vbCrLf
sX = sX & " " & sDatabase & _
"" & vbCrLf
sX = sX & " " & sCube & "" & vbCrLf
sX = sX & " " & sMeasureGroup & _
"" & vbCrLf
sX = sX & " " & vbCrLf
sX = sX & " " & vbCrLf
sX = sX & "
sX = sX & "2001/XMLSchema^ xmlns:xsi=^http://www.w3.org/2001/"
sX = sX & "XMLSchema-instance^>" & vbCrLf
sX = sX & " " & sPartition & "" & vbCrLf
sX = sX & " " & sPartition & "" & vbCrLf
sX = sX & " " & vbCrLf
sX = sX & " " & sDataSource & _
"" & vbCrLf
sX = sX & " dbo" & _
vbCrLf
sX = sX & " " & sTable & _
"" & vbCrLf
sX = sX & " " & vbCrLf
sX = sX & " " & vbCrLf
sX = sX & " " & vbCrLf
sX = sX & "" & vbCrLf
sX = sX & " " & vbCrLf
' replace all up-arrows with double-quotes
sX = Replace(sX, "^", """")
Return sX
End Function
Public Function IsPartitionThere(ByVal sServer As String, _
ByVal sDatabase As String, ByVal sCube As String, _
ByVal sMeasureGroup As String, _
ByVal sPartition As String) As Boolean
' By default, we will assume that it isn't there
' Only if we get all of the way to the end and everything is
' found, will we set it true
Dim bIsPartitionThere As Boolean = False
Dim oServer As New Microsoft.AnalysisServices.Server
' connect to the server and start scanning down the
' object hierarchy
oServer.Connect(sServer)
Dim oDB As Database = oServer.Databases.FindByName(sDatabase)
If oDB Is Nothing Then
MsgBox("Did not find expected database: " & sDatabase, _
MsgBoxStyle.OkOnly, "Error looking for partition")
GoTo Done
Else
Dim oCube As Cube = oDB.Cubes.FindByName(sCube)
If oCube Is Nothing Then
MsgBox("Did not find expected cube: " & sCube, _
MsgBoxStyle.OkOnly, "Error looking for partition")
GoTo Done
Else
Dim oMG As MeasureGroup = _
oCube.MeasureGroups.FindByName(sMeasureGroup)
If oMG Is Nothing Then
MsgBox("Did not find expected Measure Group: " & _
sMeasureGroup, _
MsgBoxStyle.OkOnly, _
"Error looking for partition")
GoTo Done
Else
'-- This is the real test -- to see if the partition
' is really there
Dim oPart As Partition = _
oMG.Partitions.FindByName(sPartition)
If Not oPart Is Nothing Then
' This is the only place to set the value to TRUE
bIsPartitionThere = True
End If
End If
End If
End If
Done:
oServer.Disconnect() ' disconnect from the server -- we are done
NoConnection:
Return bIsPartitionThere
End Function
End Class
（责任编辑 火凤凰 sunsj@51cto.com QQ：34067741 TEL：（010）68476636-8007）
共20页: 20
【内容导航】
原文：Project REAL分析服务技术探讨(20)