Last active
June 2, 2020 18:14
-
-
Save mthierba/33ee04af989562edb08b534755350a9a to your computer and use it in GitHub Desktop.
XMLA Endpoint: Create Power BI Premium Dataset via TOM
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<Query Kind="Statements"> | |
<NuGetReference>Microsoft.AnalysisServices.retail.amd64</NuGetReference> | |
<Namespace>AMO = Microsoft.AnalysisServices</Namespace> | |
<Namespace>TOM = Microsoft.AnalysisServices.Tabular</Namespace> | |
</Query> | |
var dbName = "AdventureWorksDW2014"; | |
var model = new TOM.Model { | |
DefaultPowerBIDataSourceVersion = TOM.PowerBIDataSourceVersion.PowerBI_V3, | |
Description = $"Deployed at {DateTime.UtcNow:s}" | |
}; | |
var table = new TOM.Table { Name = "Customers" }; | |
table.Columns.Add(new TOM.DataColumn { | |
Name = "CustomerKey", | |
DataType = TOM.DataType.Int64, | |
IsHidden = true, | |
SourceColumn = "CustomerKey", | |
SummarizeBy = TOM.AggregateFunction.None | |
}); | |
table.Columns.Add(new TOM.DataColumn { | |
Name = "FirstName", | |
DataType = TOM.DataType.String, | |
SourceColumn = "FirstName", | |
SummarizeBy = TOM.AggregateFunction.None | |
}); | |
table.Columns.Add(new TOM.DataColumn { | |
Name = "LastName", | |
DataType = TOM.DataType.String, | |
SourceColumn = "LastName", | |
SummarizeBy = TOM.AggregateFunction.None | |
}); | |
table.Measures.Add(new TOM.Measure { | |
Name = "Number of Customers", | |
Expression = @"COUNTROWS(Customers)", | |
FormatString = "0" | |
}); | |
table.Partitions.Add(new TOM.Partition | |
{ | |
Name = "Customers-0001", | |
Mode = TOM.ModeType.Import, | |
Source = new TOM.MPartitionSource | |
{ | |
Expression = String.Join("\n", new[] { | |
"let", | |
" Source = Sql.Database(\"YOUR_SERVER_NAME_HERE\", \"AdventureWorksDW2014\"),", | |
" dbo_DimCustomer = Source{[Schema=\"dbo\",Item=\"DimCustomer\"]}[Data],", | |
" #\"Select Columns\" = Table.SelectColumns(dbo_DimCustomer,{\"CustomerKey\", \"FirstName\", \"LastName\"})", | |
"in", | |
" #\"Select Columns\"" }) | |
} | |
}); | |
model.Tables.Add(table); | |
using (var server = new TOM.Server()) | |
{ | |
var connStrBldr = new SqlConnectionStringBuilder | |
{ | |
DataSource = "powerbi://api.powerbi.com/v1.0/myorg/YOUR_WORKSPACE_NAME", | |
UserID = "YOUR_AAD_USER_NAME", | |
Password = "YOUR_AAD_PASSWORD" | |
}; | |
server.Connect(connStrBldr.ConnectionString); | |
server.Dump(); | |
if (!server.Databases.ContainsName(dbName)) | |
{ | |
var db = new TOM.Database(dbName) | |
{ | |
CompatibilityLevel = 1520, | |
StorageEngineUsed = AMO.StorageEngineUsed.TabularMetadata, | |
Model = model | |
}; | |
server.Execute(Microsoft.AnalysisServices.Tabular.JsonScripter.ScriptCreate(db)).Dump(); | |
} | |
else | |
{ | |
using (var serverDb = server.Databases.GetByName(dbName)) | |
{ | |
model.CopyTo(serverDb.Model); | |
serverDb.Model.SaveChanges(); | |
} | |
} | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<Query Kind="Statements"> | |
<NuGetReference>Microsoft.AnalysisServices.retail.amd64</NuGetReference> | |
<Namespace>AMO = Microsoft.AnalysisServices</Namespace> | |
<Namespace>TOM = Microsoft.AnalysisServices.Tabular</Namespace> | |
</Query> | |
var dbName = "AdventureWorksDW2014"; | |
var model = new TOM.Model { | |
DefaultPowerBIDataSourceVersion = TOM.PowerBIDataSourceVersion.PowerBI_V3, | |
Description = $"Deployed at {DateTime.UtcNow:s}" | |
}; | |
var table = new TOM.Table { Name = "Customers" }; | |
table.Columns.Add(new TOM.DataColumn { | |
Name = "CustomerKey", | |
DataType = TOM.DataType.Int64, | |
IsHidden = true, | |
SourceColumn = "CustomerKey", | |
SummarizeBy = TOM.AggregateFunction.None | |
}); | |
table.Columns.Add(new TOM.DataColumn { | |
Name = "FirstName", | |
DataType = TOM.DataType.String, | |
SourceColumn = "FirstName", | |
SummarizeBy = TOM.AggregateFunction.None | |
}); | |
table.Columns.Add(new TOM.DataColumn { | |
Name = "LastName", | |
DataType = TOM.DataType.String, | |
SourceColumn = "LastName", | |
SummarizeBy = TOM.AggregateFunction.None | |
}); | |
table.Measures.Add(new TOM.Measure { | |
Name = "Number of Customers", | |
Expression = @"COUNTROWS(Customers)", | |
FormatString = "0" | |
}); | |
table.Partitions.Add(new TOM.Partition | |
{ | |
Name = "Customers-0001", | |
Mode = TOM.ModeType.Import, | |
Source = new TOM.MPartitionSource | |
{ | |
Expression = String.Join("\n", new[] { | |
"let", | |
" Source = Sql.Database(#\"[SQL Server]\", \"AdventureWorksDW2014\"),", | |
" dbo_DimCustomer = Source{[Schema=\"dbo\",Item=\"DimCustomer\"]}[Data],", | |
" #\"Select Columns\" = Table.SelectColumns(dbo_DimCustomer,{\"CustomerKey\", \"FirstName\", \"LastName\"})", | |
"in", | |
" #\"Select Columns\"" }) | |
} | |
}); | |
model.Tables.Add(table); | |
model.Expressions.Add(new TOM.NamedExpression { | |
Name = "[SQL Server]", | |
Kind = TOM.ExpressionKind.M, | |
Expression = "\"YOUR_SERVER_NAME_HERE\" meta [IsParameterQuery=true, Type=\"Text\", IsParameterQueryRequired=true]" | |
}); | |
using (var server = new TOM.Server()) | |
{ | |
var connStrBldr = new SqlConnectionStringBuilder | |
{ | |
DataSource = "powerbi://api.powerbi.com/v1.0/myorg/YOUR_WORKSPACE_NAME", | |
UserID = "YOUR_AAD_USER_NAME", | |
Password = "YOUR_AAD_PASSWORD" | |
}; | |
server.Connect(connStrBldr.ConnectionString); | |
server.Dump(); | |
if (!server.Databases.ContainsName(dbName)) | |
{ | |
var db = new TOM.Database(dbName) | |
{ | |
CompatibilityLevel = 1520, | |
StorageEngineUsed = AMO.StorageEngineUsed.TabularMetadata, | |
Model = model | |
}; | |
server.Execute(Microsoft.AnalysisServices.Tabular.JsonScripter.ScriptCreate(db)).Dump(); | |
} | |
else | |
{ | |
using (var serverDb = server.Databases.GetByName(dbName)) | |
{ | |
model.CopyTo(serverDb.Model); | |
serverDb.Model.SaveChanges(); | |
} | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment