1
A Practical Approach to using the Microsoft.AnalysisServices.Tabular .Net Library (C#)
PREMIER SPONSOR
GOLD SPONSORS
SILVER SPONSORS
BRONZE SPONSORS
SUPPORTERS
3
Product led
World’sleading banking
software company
World class delivery
No.1 3.2bn USDMarket
Capitalization
4,000+employees in
65 internationaloffices
137 go lives in 2015
Strength and depth: 2,000+ consultants, 100 concurrent projects
Community of 6000+certified partner consultants
Highest level of R&D in the industry to drive innovation
Regular software upgrade strategy
Passion for standards and openness
Temenos
545m USDrevenuesin 2015
4
AboutMe
In Software Development since 1988
Contributions: SQLRelay, SSAS-ASSP, SSIS-BidsHelper
Languages: SQL, MDX, DAX, C# & several defunct ones
JohnMathews
Technologies: RDBMS, MD, Tabular, SSIS, Rule Based Reasoning, State Machines Pipeline Processors
5
SSAS2016
TabularObjectModel
Understand the TOM Building Blocks (.NET)
Dynamically create ALL or PART of a Tabular Model
How to use Builder Patterns (C#)
6
Marco Russo
“Everything that you know about MDX/Multi-Dimensional
FORGET!”
“Mastering DAX Workshop”Source:
7
.NET
Namespace: Microsoft.AnalysisServer.Tabular
C:\Windows\assembly\GAC_MSIL\Microsoft.AnalysisServer.Tabular
https://msdn.microsoft.com/en-us/library/mt707783.aspx
https://msdn.microsoft.com/en-us/library/microsoft.analysisservices.tabular.aspx
8
What do you think are the principal TOM compnents?
TOMComponents
9
Sort By
TOMComponents
Server
Measure
Calculated Column
Data Column
ModelDatabase
Column
*
1+
Composed of
Type of
Table
*Data
SourceProviderDataSource
KPI
*
Partition
Partition Source
QueryPartitionSource
From
To RelationshipSingleColumnRelationship
Level** Hierarchy
*
*
*
**
10
“Code should read like well written prose”
Robert C. Martin (Uncle Bob)
Source: “Clean Code”
Coding
11
Server
Composed of
Type of
Server
12
Server
public static void BuildTabularModel(ModelOptions modelOptions){
using (var server = ConnectToServer(modelOptions.ConnectionString)){
// do the magic!}
}
Elsa – Disney’s “Frozen”
13
Server
using TOM = Microsoft.AnalysisServices.Tabular;
namespace SqlRelayTomExample{
public static class ServerFunctions{
public static TOM.Server ConnectToServer(string connectionString){
var server = new TOM.Server();server.Connect(connectionString);return server;
}}
}
Connection string ONLY specifies the “Data Source”
14
Database Collection
using TOM = Microsoft.AnalysisServices.Tabular;
TOM.DatabaseCollection server.Databases
int server.Databases.Count;
TOM.Database server.Databases[idx];
TOM.Database server.Find(string id);TOM.Database server.FindByName(string name);
sever.Databases.Add(TOM.Database database)
!
15
Server Database*
Composed of
Type of
Database
16
Database
Property Access Description
Name RW Name of the database
Server R The server hosting the database
CompatibilityLevel RW The SSAS compatibility level 1200
ModelType RW MD, Tabular or Default
Model RW Tabular model
StorageEngineMode RW InMemoryMixedTraditionalTabularMetaData
17
Builder Patterns
Hide the construction of complex objects
Easily set default or inferred values
Immutable
18
Properties
using AS = Microsoft.AnalysisServices;using TOM = Microsoft.AnalysisServices.Tabular;
namespace SqlRelayTomExample{
public class DatabaseBuilder{
private TOM.Server server;private string name;private int compatibilityLevel;private TOM.Model model;private AS.ModelType modelType;private AS.StorageEngineUsed storageEngineUsed;
}}
19
Defaults
public class DatabaseBuilder{
private TOM.Server server;private string name;private int compatibilityLevel;private TOM.Model model;private AS.ModelType modelType;private AS.StorageEngineUsed storageEngineUsed;
public DatabaseBuilder(){
this.compatibilityLevel = 1200;this.storageEngineUsed = AS.StorageEngineUsed.TabularMetadata;this.modelType = AS.ModelType.Default;
}
20
Cloning
public class DatabaseBuilder{
private TOM.Server server;private string name;private int compatibilityLevel;private TOM.Model model;private AS.ModelType modelType;private AS.StorageEngineUsed storageEngineUsed;
private DatabaseBuilder(DatabaseBuilder bldr){
this.name = bldr.name;this.server = bldr.server;this.compatibilityLevel = bldr.compatibilityLevel;this.model = bldr.model;this.modelType = bldr.modelType;this.storageEngineUsed = bldr.storageEngineUsed;
}
21
Custom-ising
public class DatabaseBuilder{
private TOM.Server server;private string name;private int compatibilityLevel;private TOM.Model model;private AS.ModelType modelType;private AS.StorageEngineUsed storageEngineUsed;
public DatabaseBuilder Name(string value){
return new DatabaseBuilder(this) { name = value };}
public DatabaseBuilder Server(TOM.Server value){
return new DatabaseBuilder(this) { server = value };}
22
Creating
public class DatabaseBuilder{
public static implicit operator TOM.Database(DatabaseBuilder bldr){
var database = new TOM.Database(bldr.modelType, bldr.compatibilityLevel)
{Name = bldr.name,StorageEngineUsed = bldr.storageEngineUsed,Model = bldr.model
};
bldr.server?.Databases.Add(database);
return database;}
23
Using
public static void BuildTabularModel(ModelOptions modelOptions){
using (var server =ConnectToServer(modelOptions.ConnectionString))
{
}}
TOM.Database database =new DatabaseBuilder()
.Server(server)
.Name(modelOptions.DatabaseName);
24
Server ModelDatabase*
Composed of
Type of
Model
25
Model
Property Access Description
Name RW Name of the model
Database R The database hosting the model
DefaultMode RW How AS gets its data:-DefaultDirectQueryImport
Culture RW The language (locale) culture “en-us”
DataSources (RW) Collection of data sources
Tables (RW) Collection of tables
Relationships (RW) Collection of relationships
(RW) = Data accessed via Add(), Find() methods
26
Model
public class ModelBuilder{
private string name;private TOM.Database database;private TOM.ModeType defaultMode;private string culture;
private readonly List<TOM.DataSource> dataSources =new List<TOM.DataSource>();
private readonly List<TOM.Table> tables = new List<TOM.Table>();
private readonly List<TOM.Relationship> relationships =new List<TOM.Relationship>();
public ModelBuilder(){
this.defaultMode = TOM.ModeType.Import;this.culture = "en-us";
}
27
private ModelBuilder(ModelBuilder bldr){
this.name = bldr.name;this.database = bldr.database;this.defaultMode = bldr.defaultMode;this.culture = bldr.culture;this.dataSources.AddRange(bldr.dataSources);this.tables.AddRange(bldr.tables);this.relationships.AddRange(bldr.relationships);
}
Model
28
public ModelBuilder AddDataSource(TOM.DataSource value){
return AddDataSources(new[] { value });}
public ModelBuilder AddDataSources(IEnumerable<TOM.DataSource> value)
{var clone = new ModelBuilder(this);clone.dataSources.AddRange(value);return clone;
}
Model
29
Model
public static implicit operator TOM.Model(ModelBuilder bldr){
var model = new TOM.Model(){
Name = bldr.name,DefaultMode = bldr.defaultMode,Culture = bldr.culture
};
foreach (var ds in bldr.dataSources)model.DataSources.Add(ds);
foreach (var tab in bldr.tables)model.Tables.Add(tab);
foreach (var rel in bldr.relationships)model.Relationships.Add(rel);
if (bldr.database != null) bldr.database.Model = model;
return model;}
30
Model
public static void BuildTabularModel(ModelOptions modelOptions)
{using (var server =
ConnectToServer(modelOptions.ConnectionString)){
TOM.Database database =new DatabaseBuilder()
.Server(server)
.Name(modelOptions.DatabaseName)
.Model(new ModelBuilder()));
}}
31
Server ModelDatabase*
Composed of
Type of
*Data
SourceProviderDataSourceProvider
DataSource
32
ProviderData
Source
Property Access Description
Name RW Name of the data source
Description RW A description (rationale) of the data source
Provider RW The data source provider “SQLNCLI11”
Model R The model hosting the data source
ConnectionString RW Connection string
ImpersonationMode RW ImpersonateAccountImpersonateServiceAccount
Timeout RW Command timeout 3600
Isolation RW Command isolation ReadCommitted
Annotations (RW) Additional key-value pair properties for the data source:“ConnectionEditUISource” : “SqlServer”
(RW) = Data accessed via Add(), Find() methods
33
public DataSourceBuilder(){
this.impersonationMode =TOM.ImpersonationMode.ImpersonateServiceAccount;
this.isolation =TOM.DatasourceIsolation.ReadCommitted;
this.annotations.Add(new AnnotationBuilder()
.Name("ConnectionEditUISource")
.Value("SqlServer"));
this.provider = "SQLNCLI11";
this.timeout = 3600;}
ProviderData
Source
34
Model
public static void BuildTabularModel(ModelOptions modelOptions){
using (var server = ConnectToServer(modelOptions.ConnectionString)){
TOM.Database database =new DatabaseBuilder()
.Server(server)
.Name(modelOptions.DatabaseName)
.Model(new ModelBuilder()
.AddDataSource(new DataSourceBuilder()
.Name(modelOptions.DsName)
.Description(modelOptions.DsDescription)
.ConnectionString(modelOptions.DsConString))
);}
}
35
Server ModelDatabase*
Composed of
Type of
*Data
SourceProviderDataSource
Table
Table
*
1+
Partition
Partition Source
QueryPartitionSource
*
36
Property Access Description
Name RW Name of the table
Description RW A description (rationale) of the table
Model R The model hosting the table
IsHidden RW Should the table be hidden from clients
Columns (RW) Collection of columns within the table
Partitions (RW) Collection of partitions for the table
Measures (RW) Collection of measures
Annotations (RW) “_TM_ExtProp_QueryDefinition”
“_TM_ExtProp_DbSchemaName”
“_TM_ExtProp_DbTableName”
(RW) = Data accessed via Add(), Find() methods
Table
37
Table
Partitions Case
Partitions specified
Use partitions
Create FULL partition
default
38
Partition
Property Access Description
Name RW Name of the partition
Description RW Description of the partition
Source RW The PartitionSource to get the data
Mode RW Can override the DefaultMode in the Model. Default
Table R The parent table
39
Query Partition Source
Property Access Description
Query RW The SQL query to get the data
Partition R The parent partition
DataSource RW The data source, within the model, that is to be used
(RW) = Data accessed via Add(), Find() methods
40
Server ModelDatabase*
Composed of
Type of
*Data
SourceProviderDataSource
Column
Table
*
1+
Partition
Partition Source
QueryPartitionSource
*
Calculated Column
Data Column
Column
*
41
Property Access Description
Name RW Name of the column
Description RW A description (rationale) of the table
DataType RW The DAX data type
Table R The table that the column belongs to
FormatString RW Format string for visualisation
Is… RW AvailableInMdx, DefaultImage, DefaultLabel,Hidden, Key, Nullable, Unique
SummarizeBy RW The default aggregation to be applied to the column
SortByColumn RW The column used to sort this column
Annotations (RW) XML format annotation “Format”
(RW) = Data accessed via Add(), Find() methods
Column
42
Property Access Description
SourceColumn RW Name of the column within the source table
SourceProviderType RW The data type of the source column
Data Column
TOM.DataType SourceProviderType (for SQLNCLI11)
Boolean “Bit”
String “WChar”
Double “Real”
Int64 “SmallInt”, “Int”, “BigInt”
DateTime “DBDate”, “DBTimeStamp”
Decimal “Numeric”
43
CalculatedColumn
Property Access Description
Expression RW DAX expression
44
DataType SourcePropertyType
Value of “Format” annotation
DateTime dbdate <Format Format="DateTimeCustom"><DateTimes><DateTimeLCID="2057" Group="ShortDate" FormatString="yyyy-MM-dd"/></DateTimes></Format>
dbtimestamp <Format Format="DateTimeCustom"><DateTimes><DateTimeLCID="2057" Group="GeneralLongDateTime" FormatString="yyyy-MM-ddHH:mm:ss"/></DateTimes></Format>
otherwise <Format Format="DateTimeGeneral" />
Text n/a <Format Format="Text" />
otherwise n/a <Format Format="General" />
Column
“Format” annotation
45
Useselect *
default
Table
Extract schema from sourceTableName
“_TM_ExtProp_DbTableName”
“_TM_ExtProp_DbSchemaName”
Extract table from sourceTableName
“_TM_ExtProp_QueryDefinition” Case
sqlStatementhas a value
Use sqlStatement
columnscontains
DataColumns
UseSelect
<col-list>private string sourceTableName;private string sqlStatement;
46
Creatingthe Model
Customers
CustomerId int
FirstName nvarchar(50)
LastName nvarchar(50)
Products
ProductId int
ProductName nvarchar(50)
ProductCategory nvarchar(50)
ProductUnitPrice money
SaleDates
SaleDate date
SaleYear smallint
SaleMonthNo smallint
SaleDayOfMonth smallint
SaleMonthShortName nchar(3)
SaleYearMonth int (calc)
Sales
CustomerId int
ProductId int
SalesDate date
SalesQuantity int
SalesAmount measure
*
**
47
Creatingthe Model
public static void BuildTabularModel(ModelOptions modelOptions){
using (var server = ConnectToServer(modelOptions.ConnectionString)){
}}
TOM.DataSource dataSource = new DataSourceBuilder().Name(modelOptions.DataSourceName).Description(modelOptions.DataSourceDescription)
.ConnectionString(modelOptions.DataSourceConnectionString);
TOM.Database database =new DatabaseBuilder()
.Server(server)
.Name(modelOptions.DatabaseName)
.Model(new ModelBuilder()
.AddDataSource(dataSource)
.AddTables(CreateTables(dataSource)));
48
Creatingthe Model
private static IEnumerable<TOM.Table> CreateTables(TOM.DataSource dataSource){
return new TOM.Table[]{
new TableBuilder().DataSource(dataSource).Name("Customers").SourceTableName("dbo.Customers").Description("Details of the customers").Columns(
new TOM.Column[]{
new DataColumnBuilder().Name("CustomerId").Description("Internal id a customer").DataType(TOM.DataType.Int64).SourceColumnName("CustomerId").SourceProviderType("Int").IsNullable(false).IsKey(true).IsUnique(true),
new DataColumnBuilder().Name("FirstName").Description("The first name of the customer").DataType(TOM.DataType.String).SourceColumnName("FirstName").SourceProviderType("WChar").IsNullable(false),
new DataColumnBuilder().Name("LastName").Description("The last name of the customer").DataType(TOM.DataType.String).SourceColumnName("LastName").SourceProviderType("WChar").IsNullable(false),
}),// etc.
};}
49
Creatingthe Model
private static IEnumerable<TOM.Table> CreateTables(TOM.DataSource dataSource){
return new TOM.Table[]{
new TableBuilder().DataSource(dataSource).Name("Customers").SourceTableName("dbo.Customers").Description("Details of the customers").Columns(
new TOM.Column[]{
new DataColumnBuilder().Name("CustomerId").Description("Internal id a customer").DataType(TOM.DataType.Int64).SourceColumnName("CustomerId").SourceProviderType("Int").IsNullable(false).IsKey(true).IsUnique(true),
new DataColumnBuilder().Name("FirstName").Description("The first name of the customer").DataType(TOM.DataType.String).SourceColumnName("FirstName").SourceProviderType("WChar").IsNullable(false),
new DataColumnBuilder().Name("LastName").Description("The last name of the customer").DataType(TOM.DataType.String).SourceColumnName("LastName").SourceProviderType("WChar").IsNullable(false),
}),// etc.
};}
TOO MUCH DUPLICATION
50
Creatingthe Model
private static IEnumerable<TOM.Table> CreateTables(TOM.DataSource dataSource){
return new TOM.Table[]{
new TableBuilder().DataSource(dataSource).Name("Customers").SourceTableName("dbo.Customers").Description("Details of the customers").Columns(
new TOM.Column[]{
new DataColumnBuilder().Name("CustomerId").Description("Internal id a customer").DataType(TOM.DataType.Int64).SourceColumnName("CustomerId").SourceProviderType("Int").IsNullable(false).IsKey(true).IsUnique(true),
new DataColumnBuilder().Name("FirstName").Description("The first name of the customer").DataType(TOM.DataType.String).SourceColumnName("FirstName").SourceProviderType("WChar").IsNullable(false),
new DataColumnBuilder().Name("LastName").Description("The last name of the customer").DataType(TOM.DataType.String).SourceColumnName("LastName").SourceProviderType("WChar").IsNullable(false),
}),// etc.
};}
Can set default values
Are Immutable
Builder Patterns:-
51
Creatingthe Model
private static IEnumerable<TOM.Table> CreateTables(TOM.DataSource dataSource){
var tableBldr = new TableBuilder().DataSource(dataSource);
var stringNotNullDataColBldr = new DataColumnBuilder().IsNullable(false).DataType(TOM.DataType.String).SourceProviderType("WChar");
var decimalNotNullDataColBldr = new DataColumnBuilder().IsNullable(false).DataType(TOM.DataType.Decimal).SourceProviderType(“Numeric");
var intNotNullDataColBldr = new DataColumnBuilder().IsNullable(false).DataType(TOM.DataType.Int64).SourceProviderType("Int");
var intIdNotNullDataColBldr =intNotNullDataColBldr.SummarizeBy(TOM.AggregationFunction.None)
var dateNotNullDataColBldr = new DataColumnBuilder().IsNullable(false).DataType(TOM.DataType.DateTime).SourceProviderType(“DBDate");
var intNotNullCalcColBldr = new CalculatedColumnBuilder().DataType(TOM.DataType.Int64).IsNullable(false);
52
Creatingthe Model
private static IEnumerable<TOM.Table> CreateTables(TOM.DataSource dataSource){
var tableBldr = new TableBuilder().DataSource(dataSource);
var stringNotNullDataColBldr = new DataColumnBuilder().IsNullable(false).DataType(TOM.DataType.String).SourceProviderType("WChar");
var decimalNotNullDataColBldr = new DataColumnBuilder().IsNullable(false).DataType(TOM.DataType.Decimal).SourceProviderType(“Numeric");
var intNotNullDataColBldr = new DataColumnBuilder().IsNullable(false).DataType(TOM.DataType.Int64).SourceProviderType("Int");
var intIdNotNullDataColBldr =intNotNullDataColBldr.SummarizeBy(TOM.AggregationFunction.None)
var dateNotNullDataColBldr = new DataColumnBuilder().IsNullable(false).DataType(TOM.DataType.DateTime).SourceProviderType(“DBDate");
var intNotNullCalcColBldr = new CalculatedColumnBuilder().DataType(TOM.DataType.Int64).IsNullable(false);
STILL TOO MUCH DETAIL
53
Creatingthe Model
private static IEnumerable<TOM.Table> CreateTables(TOM.DataSource dataSource){
var bldrs = new TomBuilderModel(dataSource);
return new TOM.Table[]{
bldrs.TableBuilder.Name("Customers").SourceTableName("dbo.Customers").Description("Details of the customers").Columns(
new TOM.Column[]{
bldrs.IntIdNotNullDataColBldr.Name("CustomerId").Description("Internal id a customer").IsKey(true).IsUnique(true),
bldrs.StringNotNullDataColBldr.Name("FirstName").Description("The first name of the customer"),
bldrs.StringNotNullDataColBldr.Name("LastName").Description("The last name of the customer")
}),
54
Creatingthe Model
bldrs.TableBuilder.Name("SaleDates").SourceTableName("dbo.SaleDates").Columns(
new TOM.Column[]{
bldrs.IntIdNotNullDataColBldr.Name("SaleDate").IsKey(true).IsUnique(true),
bldrs.IntIdNotNullDataColBldr.Name("SaleYear"),
bldrs.IntIdNotNullDataColBldr.Name("SaleMonthNo"),
bldrs.IntIdNotNullDataColBldr.Name("SaleDayOfMonth"),
bldrs.StringNotNullDataColBldr.Name("SaleMonthShortName"),
})
55
Creatingthe Model
TOM.Column saleMonthNoColumn =bldrs.IntIdNotNullDataColBldr.Name("SaleMonthNo");
bldrs.TableBuilder.Name("SaleDates").SourceTableName("dbo.SaleDates").Columns(
new TOM.Column[]{
bldrs.IntIdNotNullDataColBldr.Name("SaleDate").IsKey(true).IsUnique(true),
bldrs.IntIdNotNullDataColBldr.Name("SaleYear"),
saleMonthNoColumn,
bldrs.IntIdNotNullDataColBldr.Name("SaleDayOfMonth"),
bldrs.StringNotNullDataColBldr.Name("SaleMonthShortName").SortBy(saleMonthNoColumn),
})
bldrs.IntNotNullCalcColBldr.Name("SaleYearMonth").SummarizeBy(TOM.AggregateFunction.None).Dax("(SaleDates[SaleYear] * 100) + SaleDates[SaleMonthNo]")
56
Server ModelDatabase*
Composed of
Type of
*Data
SourceProviderDataSource
Measure
Table
*
1+
Partition
Partition Source
QueryPartitionSource
*
Calculated Column
Data Column
Column
*
Measure
*
57
Property Access Description
Name RW Name of the measure
Description RW A description (rationale) of the measure
Expression RW The DAX expression
Table R The table that the measure belongs to
FormatString RW The display format of the result
IsHidden RW Is measure hidden from clients
Kpi RW The KPI associated with this measure
(RW) = Data accessed via Add(), Find() methods
Measures
Property Description
DeleteExisting Use this definition to replace an existing one
58
Creatingthe Model
bldrs.TableBuilder.Name("Sales").SourceTableName("dbo.Sales").Columns(
new TOM.Column[]{
bldrs.DateNotNullDataColBldr.Name("SaleDate"),bldrs.IntIdNotNullDataColBldr.Name("CustomerId"),bldrs.IntIdNotNullDataColBldr.Name("ProductId")
})
)
.AddMeasure(new MeasureBuilder()
.Name("SalesAmount")
.Dax("SUMX(Sales, Sales[Quantity] * RELATED(Products[ProductUnitPrice]))")
)
59
Server ModelDatabase*
Composed of
Type of
*Data
SourceProviderDataSource
Measure
Table
*
1+
Partition
Partition Source
QueryPartitionSource
*
Calculated Column
Data Column
Column
*
Measure
*
Level** Hierarchy
*
60
Property Access Description
Name RW Name of the hierarchy
Description RW Description (rationale) of the hierarchy
IsHidden RW Is the hierarchy to be hidden from clients
Table R The parent table
Levels (RW) Ordered list of levels within the hierarchy
Hierarchy
61
Property Access Description
Name RW Name of the level
Description RW Description (rationale) of the level
Column RW The column to be shown for this level
Ordinal RW The ordinal (zero-based) of the column within the hierarchy
Hierarchy R Ordered list of levels within the hierarchyLevel
62
Creatingthe Model
var model = database.Model;var saleDatesTable = model.Tables.Find("SaleDates");
salesTable.Hierarchies.Add(new HierarchyBuilder().Name("Calendar")
.Levels(new TOM.Level[]{
new LevelBuilder().Name("Year").Ordinal(0).Column(saleDatesTable.Columns.Find("SaleYear")),
new LevelBuilder().Name("Month").Ordinal(1).Column(saleDatesTable.Columns.Find("SaleMonthNo")),
new LevelBuilder().Name("Day").Ordinal(2).Column(saleDatesTable.Columns.Find("SaleDayOfMonth")),
})
);
63
*
Server ModelDatabase*
Composed of
Type of
*Data
SourceProviderDataSource
Measure
Table
*
1+
Partition
Partition Source
QueryPartitionSource
*
Calculated Column
Data Column
Column
*
Measure
* *From
To RelationshipSingleColumnRelationship
Level*
*
Hierarchy
64
Property Access Description
Name RW Name of the relationship
CrossfilteringBehavior RW The filtering mode OneDirection
FromCardinality RW The cardinality on the fact end Many
FromColumn RW The fact column – cannot be an orphan
ToCardinality RW The cardinality on the reference end One
ToColumn RW The reference column – cannot be an orphan
IsActive RW Is this relationship active True
Relation-ship
Many : Many relationships are not supported
65
Creatingthe Model
var model = database.Model;var relBldr = new RelationshipBuilder();var relationships = new TOM.Relationship[]{
relBldr..FromColumn(ModelBuilder.FindColumn(model, "'Sales'[CustomerId]")).ToColumn(ModelBuilder.FindColumn(model, "'Customers'[CustomerId]")),
relBldr..FromColumn(ModelBuilder.FindColumn(model, "'Sales'[ProductId]")).ToColumn(ModelBuilder.FindColumn(model, "'Products'[ProductId]")),
relBldr..FromColumn(ModelBuilder.FindColumn(model, "'Sales'[SalesDateId]")).ToColumn(ModelBuilder.FindColumn(model, "'SalesDates'[SalesDateId]")),
};
Parse qualified column name to:• lookup up table in model• then column in table
66
Processing
Deploying
Refresh is the new Process
public static void UpdateDatabase(TOM.Database database){
database.Update(UpdateOptions.ExpandFull, UpdateMode.UpdateOrCreate);}
RequestRefresh() on Model, Table or Partition
database.Model.RequestRefresh(TOM.RefreshType.Full);
database.Model.SaveChanges(TOM.SaveFlags.Default);
67
Summary DEMO
68
Summary
Knowledge TOM’s core classes and their properties and relationships
Thank you & Please give feedback: sqlrelay.co.uk/feedback
Builder Patterns – defaults, immutable, hide complexity
It’s not just theory