Dynamic Connection String in Embedded Dashboard
Bold BI helps you quickly embed dashboards based on your business requirements while avoiding the burden of building an analytics or BI solution yourself, saving significant development time. You can explore Bold BI and its features here. In this blog post, we are going to look into the dynamic binding of data sources feature which helps you switch between data connectors for different user views in embedded dashboards.
The dynamic connection string feature enables you to modify the connection string of data sources in dashboard view mode. This feature, available only to embedded dashboards, allows you to view a dashboard with a modified connection string in the Bold BI application itself. Dashboards in the Bold BI application will be rendered using the connection string used while creating the data source.
We will see how dynamically binding a data source works, and how to change the connection string dynamically for an embedded dashboard by looking at a simple use case.
When it is needed
If you have different clients whose data resides in different databases but has the same schema, and one common dashboard embedded in your application, you can change the connection string dynamically in the embedded dashboard based on the client. This allows you to easily change the dashboard’s data source connection from work to production without modifying the dashboard.
How it works
You can enable the dynamic connection string in any live-mode data connection such as MSSQL, MySQL, PostgreSQL, etc. Just configure a web API while creating a live mode connection. This web API will return the modified connection string.
While rendering a dashboard, the configured API will be triggered for each data source created with the dynamic connection string configuration. Based on the response from the API, the connection string will be updated in the data source and the dashboard will be rendered.
How to enable dynamic connection string change
To change the connection string in the data source of a dashboard dynamically, you need to:
- Integrate a dashboard into an application.
- Create web API services.
- Configure the web API services in the data source of the dashboard.
Adding a dashboard to an ASP.NET Core application
Let create an ASP.NET Core application and embed the team management dashboard which helps project leads and managers track the status of various projects. This blog provides a brief explanation of how to integrate dashboards with the Embed SDK into ASP.NET MVC and ASP.NET Core applications. You can get the sample code from our sample browser.
With the team management dashboard embedded successfully in our ASP.NET Core application, let’s see how to create web API services.
How to create the web API services
You need to create a web API service in the HomeController.cs file of the application.
Create an ApiResponse class in the Models folder and declare the Data, Status, and Message properties for the dynamic connection string instance.
Now add the code in the HomeController for the web API response with database details and identity values.
Two different databases are used in the team management dashboard. The Manager1 database has two projects, Project A and Project B. The Manager2 database has two different projects, Project C and Project D, for managers. So, we define customIdentity values as 1 and 2 for Manger1 and Manager2, respectively, and define the value 0 to show all project databases. Then, we create the API response object with connection string details as shown in the following screenshots.
The database variable is set to InitialCatalog of the Data object in the API response. Based on the identity value passed, the database name will be changed dynamically. With that, we have completed the initial steps of creating the web API.
Let’s run the service and see if the web API service is running properly. The API should return the response shown in the following image.
With the web API service running properly in our application, we need to configure it in the data source of a Bold BI dashboard.
Now, let’s see how to configure the dynamic connection string in the data source of a Bold BI dashboard.
Configuring web API services in the data source of the dashboard
To configure web API services in the data source, first we need to edit the team management dashboard. Navigate to the Data Sources list and click the data source edit icon to edit the data source.
Click the Edit connection button in the toolbar and enable the Dynamic Connection String option in the Edit Connection window. Click the Configure button to open the Dynamic Connection String Configuration dialog.
In the Dynamic Connection String Configuration window, provide the created web API service in the URL text box and click Save. Then click Reconnect and save the data source.
Now, the dynamic connection string is configured successfully in the data source. Click the Publish button to save the dashboard.
Next, let’s see how to pass the custom identity to the viewer service.
Passing the custom identity to the viewer service
Before running the application, we need to add the dynamic connection string property in the Bold BI instance in your application as shown in the following image.
Now I have set the identity value to 0 to track all projects, so all projects are bound to the dashboard.
The following screenshot shows the admin view, which shows all projects’ data.
Note the Total Resources card widget shows 46, and the Project combo box widget is set to All, meaning that all the projects, from A to D, are shown in the dashboard.
The following screenshot shows the Manager1 view, which shows the data for Project A and Project B.
You can see Project A and Project B are selected in the Project combo box widget, and the resource count is 27.
The following screenshot shows the Manager2 view. This view shows only the data for Project C and Project D.
You can see Project C and Project D are selected in the Project combo box widget, and the resource count is 19.
Conclusion
Now you know how to change the dynamic connection string of a data source for your embedded dashboards based on dynamic connection string configuration with Bold BI. Refer to this documentation page to know more about dynamically binding a connection string in a dashboard. With Bold BI, you can embed dashboards for your clients into any web application. If you have different databases for different customers but the same schema, then you can maintain a single dashboard for all of them. Instead of recreating a dashboard for each customer, you can change the connection string dynamically and monitor the current data from a single page through interactive data visualizations.
Get started with Bold BI by signing up for a free 15-day trial and create more interactive business intelligence dashboards. You can also contact us by submitting your questions through the Bold BI website or, if you already have an account, you can log in.
Originally published at https://www.boldbi.com on February 24, 2021.
