Blog Home  Home RSS 2.0 Atom 1.0 CDF  
Mark Mrachek - Business Intelligence Blog - Programmatically Adding Relationships in Data Source Views
 
 Friday, January 13, 2006

Here is a bit of sample code for adding relationships between objects in Data Source Views in Analysis Services.

Dim strConn As String = "Provider=MSOLAP.3;Data Source=LOCAL;Initial Catalog=AS_DATABASE"
Dim objServer As New Server()
Dim objDatabase As Database
Dim objDSV As DataSourceView

objServer.Connect(strConn)
objDatabase = objServer.Databases.FindByName("AS_DATABASE_NAME")
objDSV = objDatabase.DataSourceViews.FindByName("DATA_SOURCE_VIEW_NAME")

'Delete existing relationships in dsv
If objDSV.Schema.Relations.Count > 0 Then
   objDSV.Schema.Relations.Clear()
   objDSV.Update()
End If

'Create Relationships in DSV
AddRelationship(objDSV, "dbo_PKTable1", "PKColumnName", "dbo_FKTable1", "FKColumnName")
AddRelationship(objDSV, "dbo_PKTable2", "PKColumnName", "dbo_FKTable2", "FKColumnName")
AddRelationship(objDSV, "dbo_PKTable3", "PKColumnName", "dbo_FKTable3", "FKColumnName")
AddRelationship(objDSV, "dbo_PKTable4", "PKColumnName", "dbo_FKTable4", "FKColumnName")

'Update DSV
objDSV.Update()


Sub AddRelationship(ByVal objDSV As DataSourceView, ByVal strPKTable As String, ByVal strPKCol As String, ByVal strFKTable As String, ByVal strFKCol As String)
   Dim objPKCol As Data.DataColumn = objDSV.Schema.Tables(strPKTable).Columns(strPKCol)
   Dim objFKCol As Data.DataColumn = objDSV.Schema.Tables(strFKTable).Columns(strFKCol)
   Dim strFKName As String = "FK_" & strFKTable & "_" & strFKCol

   objDSV.Schema.Relations.Add(strFKName, objPKCol, objFKCol, True)
End Sub

If you choose to use this sample code, please note that the underscore replaces the "." in the user.object naming standard used by SQL Server.  For instance, if your table owner is dbo and your table is called tbltable1, then the complete SQL Server name of the object would be dbo.tblTable1.  Analysis Services replaces the "." with and underscore.  The name that the table in the DSV would be dbo_tblTable1.

 

1/13/2006 8:09:21 PM (Pacific Standard Time, UTC-08:00)  #    Comments [0]   AMO  | 
Name
E-mail
Home page

Comment (HTML not allowed)  

Enter the code shown (prevents robots):

Copyright © 2010 Mark Mrachek. All rights reserved.
DasBlog 'Portal' theme by Johnny Hughes.