Power BI Api approach for dynamic report generation

[ms92ita]

Hi to all,

I am developing a solution to my software that creates a dedicated Azure SQL server database for each customer and binds a dataset for each DB in DirectMode query mode with the connection string for the DB.
In order to test it, I have already created dynamically the dataset from API with relations and tabes defined with DefaultMode as Streaming (I am using the official nuget library), but when I am trying to use it from my local Power BI Desktop instance I have this error:

Dataset creation code is the following: 

var createDataSet = new CreateDatasetRequest();
createDataSet.Name = "azuredb";
var clientCnt = new SqlConnectionStringBuilder();
clientCnt.DataSource = "tcp:azureserver.database.windows.net,1433";
clientCnt.InitialCatalog = "azuredb";
clientCnt.UserID = "XXXXXXXXXXXX";
clientCnt.Password = "XXXXXXXXXXXX";
clientCnt.Encrypt = true;
clientCnt.ConnectTimeout = 30;
clientCnt.PersistSecurityInfo = true;
clientCnt.MaxPoolSize = 6000;
createDataSet.DefaultMode = DatasetMode.Streaming;

createDataSet.Datasources = new List<Datasource>()
{
	new Datasource()
	{
		DatasourceType = "Sql",
		ConnectionString = clientCnt.ToString(),
		Name = "azuredb",
		ConnectionDetails = new DatasourceConnectionDetails
		{
			Server = "azuredb",
			Database = "tcp:azureserver.database.windows.net,1433",
		},
	}
};

//code for tables and relationships

client.Datasets.PostDataset(groupid, createDataSet);

[SpartaBI]

Hi @ms92ita,

I believe you mean a slicer:

Not a filter:

In any case, you have a couple of options:
1. Programatically set the initial slicer selection for each user in your embedded application. See:
Set Slicer State
Update Filters
2. Save the initial report with a "select all" option. For a slicer, you can add it in the visualization pane:

3. Add the same slicer value to all SQL DBs (such as "Default", "Choose a Value" etc...) and save the report with this value. You can adjust the model so that selecting the value presents nothing or presents actual default data relevant to each customer.
4. Technically, you can also save the report with a slicer value that will not appear in any SQL DBs (like "Choose a Value"). No data will be presented and once the user will unselect the value it will disappear from the slicer. I wouldn't recommend this approach since it is kind of a hack and harder to maintain.

Showcase Report – Contoso By SpartaBI

      

[SpartaBI · Accepted solution]

Hi @ms92ita,

Yes. You should create a dataset for each customer and then generate an embed token with the right DatasetID.
In addition, you should add the following to the config object:

datasetBinding: {
   datasetId: "<DatasetId>"
}

See dynamic binding and bind datasets dynamically to a report

Showcase Report – Contoso By SpartaBI

      

[ms92ita]

Hi,

I have done these steps:

1) create a copy of the dataset from a .pbix template (PostImportWithFileInGroup from c# API)

2) update datasource settings of new dataset in order to point to the right database (UpdateDatasourcesInGroup from c# API): at the moment, the databases are in the same server so credentials are valid

3) generate a token for the report with both original and new dataset with this code:

                var pbiReport = client.Reports.GetReport(groupid, reportid);

                EmbedToken embedToken;
                // Create list of dataset
                var datasetIds = new List<Guid>();

                // Add dataset associated to the report
                datasetIds.Add(Guid.Parse(pbiReport.DatasetId));

                // Append additional dataset to the list to achieve dynamic binding later
                datasetIds.Add(Guid.Parse(newreportid));

                // Get Embed token multiple resources
                var tokenRequest = new GenerateTokenRequestV2(
                    reports: new List<GenerateTokenRequestV2Report>() { new GenerateTokenRequestV2Report(reportid) },
                    datasets: datasetIds.Select(datasetId => new GenerateTokenRequestV2Dataset(datasetId.ToString())).ToList(),
                    targetWorkspaces: groupid != Guid.Empty ? new List<GenerateTokenRequestV2TargetWorkspace>() { 
                        new GenerateTokenRequestV2TargetWorkspace(groupid)
                    } : null
                );
                // Generate Embed token
                embedToken = client.EmbedToken.GenerateToken(tokenRequest);

4) create report view from JS with the correct report id

        const config = {
            type: 'report',
            tokenType: models.TokenType.Embed,
            accessToken: accessToken,
            embedUrl: embedUrl,
            id: embedReportId,
            datasetBinding: {
                datasetId: "newdatasetid", // The dataset id that you want the report to use 
            },
            datasetId: "newdatasetid",
            permissions: models.Permissions.All,
            settings: {
                // Enable this setting to remove gray shoulders from embedded report
                // background: models.BackgroundType.Transparent,
                panes: {
                    filters: {
                        expanded: false,
                        visible: true
                    },
                    pageNavigation: {
                        visible: true
                    }
                }
            }
        };

        // Embed the dashboard and display it within the div container.
        const report = powerbi.embed(embedContainer, config);

The report is blank. Also, filter visuals are empty

From console, I have seen that a call has 403 result

UPDATE:

i have refreshed credentials from the gateway with UpdateDatasource and not it seems to work fine. I will try later with multiple binding but probably the circle has been closed

[SpartaBI]

@ms92ita great, so please accept all the messages that helped you as solutions, and will appreciate your kudos on them

[ms92ita]

Last question related to the initial visual of a report: in some pages, I have a dropdown filter that that acts on the graphs and is populated by a table.
I have noticed that in first stage the dropdown views the "default" value even if I am using an another dataset. Dropdown values after loading are correct. How can I avoid default value or overwrite it in the first instance (for example empty value)?

[SpartaBI]

Hi @ms92ita,

I'm not sure that I understand your question.
Do you have a couple of reports, published to the Power BI Service, connected to a DQ dataset that points to a sample customer?
I assume you mean reports (not dashboards). right? Dashboards are much more limiting from a programmatic point of view.

You'll need to create the additional datasets using the Post Import API and then change the connections using the Update Datasources or Update Parameters APIs.
This will allow customers to connect using Power BI Desktop and allow you to use dynamic binding in order to present the reports with the right data content to users connecting to your application (using the dataset ID).

Showcase Report – Contoso By SpartaBI

      

[ms92ita]

Hi,

my request was for reports. Since that a report can be accessible from several customers at the same time, in order to accomplish my requirements do I have to prepare multiple datasets (one for each customer because of different connection credentials) and then generate the embed token with the correct dataset ID at runtime per customer?
Probably using a single dataset and update credentials from API is not the best solution.

[ms92ita]

Hi,

now I have created 2-3 reports from Power BI Desktop and I have already retrieved visuals and dashboards from the javascript library and token generation from the c# library in order to get the correct EmbedUrl.
Now, can I create multiple datasets dynamically (Direct Query and SQL server) with Power BI API library and then view the reports' dashboard with the correct dataset ID depending on the customer?

[SpartaBI]

Hi @ms92ita,

In theory, you can try to create the PBIX dynamically by hacking the file, but this is not supported nor documented.
An easier way will be to use Tabular Editor to manipulate PBIX/PBIT files using C# (it has CLI support).

Note that the Post Import API allows you to overwrite all the existing copies of a dataset. If you plan to create multiple models per customer- This is usually redundant since you should be able to consolidate the logic in a single model (you can consider creating perspectives in the model for a better user experience during report authoring).

If you use a PPU or a premium capacity, I strongly suggest you use the AMO-TOM Assemblies
This will allow you to create/change any dataset structure dynamically (You'll have access to the object model).

If you are implementing Embedding for your organization without PPU or premium capacity, you can consider using an embedded capacity (A SKU) just to have programmatic access during the datasets changes. You can start/stop the embedded capacity and assign/unassign it to a workspace programmatically.

Showcase Report – Contoso By SpartaBI

     

[ms92ita]

For, us the better approach is the shared template with the "Embedding for your organization" mode. Basically, we do not want to allow access to the database so the second approach is better and all our acesses to the API are with the app registration identity from AD (we are already using it for ARM management in Azure).

Anyway, can we create dynamically a PBIX file with all tables/relations without Power BI Desktop? This is beacuse, if we need to change table definitions, this is not easy to change multiple datasets one by one

[SpartaBI]

Hi @ms92ita,

In order to allow users to see shared reports in the PBI Service and connect to shared datasets using PBI desktop, each user must have a user-principal in AAD.
A user-principal can be created in the AAD tenant, or invited from outside the AAD tenant (using Azure B2B which has some limitations- Those will lead to a suboptimal user experience with Power BI Service & Desktop, so you should avoid them).
For this approach you have two options:
1. Each user-principal must have a Power BI license (Pro/PPU).
2. You'll need to purchase a premium dedicated capacity which allows free users access to the reports and datasets.

Since you need to create DQ datasets, you should use a template PBIX file, upload it with the Post Import API then change the connection using the Update Datasources or Update Parameters APIs.

You also have APIs for managing permissions, creating workspaces (one per customer for example. you can use the same premium dedicated capacity for all of them), duplicating reports, and binding reports to datasets. You also have some API control over dashboards, but they are more difficult to manage.

If you need to use a different dataset per customer, you should consider Power BI Embedded. It may better suit your needs (e.g, dynamic binding).

Showcase Report – Contoso By SpartaBI

     

[SpartaBI]

Hi @ms92ita,

I never used this SDK, but I believe you are calling the Post Dataset API.
This API creates a live connections dataset / push dataset / streaming dataset.

You will not be able to connect to a Streaming Dataset from Power BI Desktop.
This dataset is not an actual tabular model. It is a temporary cache, used only with Power BI Dashboards, that should be actively fed with pushed data.

In order to create a Direct Query (DQ) dataset, you will need to implement another approach.

If you use Power BI Premium (dedicated capacity / PPU license), the best course of action will be to use the AMO-TOM Assemblies to create the DQ dataset as an Analysis Services database.

If you do not use Power BI Premium, you will need to call the Post Import and Get Import APIs to upload an actual PBIX file (you may prefer to save it as a serialized base64 string). After the upload, you can use the Update Datasources or Update Parameters APIs to change the connection strings.

[ms92ita]

Hi,

I am using a Power Bi Premium license, but the scope of my project is to create a model (o more than one if necessary), link SQL sources to the model and generate reports/dashboard for various customers in order to view the processed data. Customer must view data from their related DB connection (they are isolated, not a common one).

Also, I want to make available the model (or a copy of that) to the customer in order to use it in his own Power BI Desktop. The connection details will be sent separately if necessary.

In this moment I am using the Post Dataset API to generate model and connection, but since that his model cannot be shareable between API and power BI Desktop, can i generate the model dynamically in PBIX format, upload it in the solution with Post Import API and use it in both ways (dashboard API and Desktop)?

Also, last question: for this approach, is it required to use a Power BI Premium license for our account that will contain the workspace for all application' models and connections?