Hello, I would like to automate the DataSet generation (using C#). In this context, I have encountered a problem when I create the DataSet in Power BI Service.
My requirements:
- PPU (Premium Per User) Trial License
- On Premise SQL Server
- Microsoft.AnalysisServices Package
To generate my DataSet I complete the following steps:
- Connect to Power BI Service
- Create DataSet
- Connect DataSet with data source (SQL server)
- Bring tables from the data source into the DataSet
The code for this:
string workspaceConnection = "powerbi://myWorkspaceURL";
string ConnectionString = $"DataSource={workspaceConnection};";
using (Server server = new Server())
{
server.Connect(ConnectionString);
// Database Creation
string newDatabaseName = server.Databases.GetNewName("CSharpDataSet_Test");
var dbWithDataSource = new Microsoft.AnalysisServices.Tabular.Database()
{
Name = newDatabaseName,
ID = newDatabaseName,
CompatibilityLevel = 1500,
StorageEngineUsed = StorageEngineUsed.TabularMetadata
};
// Adding Data Source
string dataSource = "LocalSQLServer";
dbWithDataSource.Model.DataSources.Add(new ProviderDataSource()
{
Name = dataSource,
Description = "Description Example",
ConnectionString = "Provider=SQLNCLI11;Data Source=LocalSQLServer;Initial Catalog=MyDatabase;Integrated Security=SSPI;Persist Security Info=false;",
ImpersonationMode = Microsoft.AnalysisServices.Tabular.ImpersonationMode.ImpersonateAccount,
Account = @"MyAccount",
Password = "password",
});
// Adding Table/Columns
dbWithDataSource.Model.Tables.Add(new Table()
{
Name = dbWithDataSource.Model.Tables.GetNewName("Individual Customers"),
Description = "Table Example Description.",
Partitions = {
new Microsoft.AnalysisServices.Tabular.Partition() {
Name = "All Customers",
Mode = ModeType.Import,
Source = new QueryPartitionSource() {
DataSource = dbWithDataSource.Model.DataSources[dataSource],
Query = "SELECT city, street FROM customers"
}
}
},
Columns =
{
new DataColumn() {
Name = "City",
DataType = Microsoft.AnalysisServices.Tabular.DataType.String,
SourceColumn = "city",
},
new DataColumn() {
Name = "Street",
DataType = Microsoft.AnalysisServices.Tabular.DataType.String,
SourceColumn = "street",
}
}
});
server.Databases.Add(dbWithDataSource);
dbWithDataSource.Model.RequestRefresh(Microsoft.AnalysisServices.Tabular.RefreshType.Full);
dbWithDataSource.Update(UpdateOptions.ExpandFull);
}
With this code, I am getting the following error in the last line of my code (dbWithSource.Update(UpdateOptions.ExpandFull)):
{"error":{"code": "Premium_ASWL_Error", "pbi. error":{"code": "Premium_ASWL_Error", "parameters":{}, "details":[{"code": "Premium_ASWL_Error_Details_Label", "detail":{"type":1, "value": "An unexpected internal error happened while obtaining the credentials for the data source '<ccon>13</ccon>': data source information is not found in Power BI service."}}]}}
Upon further investigation, I notice that the error only occurs when the Column part is present during table creation. If I comment the column part, then the error does not occur.
My Questions:
- Is it possible that I have the problem with my code that I connect to my Power BI Workspace at the beginning, but then I specify a local(on premise) database as data source and because of that it says "Data source information is not found in Power BI service"?
- Could I also connect to a Local Power BI Report Server instead of a Power BI Workspace?
Because of my described problem, I am also trying now to generate the DataSet within a Power BI file in an open Power BI Desktop instance. In doing so, I am running into an issue with the enhanced metadata format (See How do I use the enhanced metadata format in C#? ).
In addition, I have revised this thread and merged information from my reply post for @v-shex-msft into my initial post for improved understanding and clarity.
