Skip to content

Instantly share code, notes, and snippets.

@mthierba
Last active June 2, 2020 18:14
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save mthierba/33ee04af989562edb08b534755350a9a to your computer and use it in GitHub Desktop.
Save mthierba/33ee04af989562edb08b534755350a9a to your computer and use it in GitHub Desktop.
XMLA Endpoint: Create Power BI Premium Dataset via TOM
<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();
}
}
}
<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