In this case, we will rename it to “ SQLShackDemo_QA” and click OK.įinally, click on the Apply Changes button to go ahead and refresh the Power BI dataset: In the Edit Parameters dialog box, alter the name of the catalog to point it to the QA environment. Head over to the Transform data tab on the menu bar and select Edit Parameters from the drop-down menu: Now that our initial report from the development database is built and we can visualize the data, let us move aheadĪnd try to change the connection parameters and point the data source to the QA database. Which is visible in the report as follows.įigure 9 – Capturing data from Development Environment Changing the parameters
In the development environment, we have inserted the value as “ 100” Since we have only one field in the table ( TotalSalesAmount), let us create a card visual Since we have only one table, we will go ahead and fetch it into Power BI. Once the connection is established with the SQL Server database, you will see and option to import the tables that This will bring in the data from the SQL Server into the parameterized Power BI report: Click OK once done:įigure 6 – Specifying Catalog Name as the parameterįinally, select Data Connectivity mode as Import and click on OK. Provide the name of the development database as the Current Value. Repeat the same for specifying the catalog name as a parameter. Click OK once done:įigure 5 – Specifying the Data Source Parameter Need to provide the name of your server on which the database is created. Since we are going to parameterize our data source, let us name this as “ DataSource” and enter the Current Value as “ localhost”. In this, you can name your parameters and provide valuesįor the connection string as well. The Manage Parameters dialog box opens up. Since we want to parameterize our data source, we will go ahead and select the New Parameter from the drop-down menu and click OK:įigure 4 – Selecting New Parameter for data source Now as you can see, we have a drop-down box available to specify if the data source is a simple text value or it isĪ parameter. Click on Get Data and select SQL Server from the drop-down menu:įigure 3 – Fetching data from SQL Server into Power BI Once the parametrization of data sources is enabled, let start to bring in the data from the SQL Server. Click on OK once done:įigure 2 – Enabling the parametrization in Power BI Desktop Under the Global pane on the left, click on Power Query Editor, and under the Parameters heading, select the checkbox which says, “ Always allow parameterization in data source and transformation dialogs”. On the menu that appears, select Options and settings, and then select Options.
Now that our database environment has been set up, we are ready to start working on the Power BI report. Now let us move to the Power BI Report development. With this, we have completed our database development. You can run the following script to create the databases on your machine: “ SQLShackDemo_QA” and “ SQLShackDemo_PROD”. I am going to call the databases as “ SQLShackDemo_DEV”, Although in a real scenario, the servers and the databases will be in complete isolation, however, for the sake of this tutorial, we will keep the databases in the same server itself. In the database server, we are going to create three different databases, one for each development, QA, and productionĮnvironments. Let us now go ahead and prepare our database environment first. To develop parameterized Power BI reports, we would need to have three different data sources from which we canįetch data into the Power BI environment. Within the schema then the Power BI reports will fail and no longer work as intended. This means that the structure of the tables and relationships in the databases must be the same in all the environments. Is to have the source database schema the same across all the environments. Another important thing to keep in mind for this Reports while establishing the connection to the source databases. To develop parameterized Power BI reports, we would need to parameterize our data source connection in the Power BI Provided to an administrator who can publish it to the production workspace. To the QA who will test it on a separate environment and finally when the testing is completed, it can then maybe Parameterized Power BI reportsĪre essential because we can use the same report to develop the charts and the KPIs and then provide the same report The same report across multiple environments, like development, QA, and production. In this article, I am going to explain how we can develop parameterized Power BI reports locally and then migrate
0 kommentar(er)
