This post was co-written by Chaitanya Uday Kulkarni and Manish Garg.
As organizations embrace the power of self-service analytics and interactive dashboards, transitioning from SSRS to Power BI becomes an enticing and oftentimes necessary decision.
The migration of SSRS (SQL Server Reporting Services) reports to Power BI Service marks a significant shift in data visualization and reporting capabilities.
During the migration process, existing .rdl reports are pointed to a Snowflake Data Cloud database from an existing data source in Power BI Report Builder. The Power BI Service further enhances collaboration by enabling easy sharing and embedding reports in web pages and applications.
In this blog, we’ll walk you through how to successfully migrate SSRS reports to Power BI Service with Snowflake as the datasource. Once complete, your organization will unlock better data-driven decision-making, improved data governance, and a more engaging and interactive reporting environment for users.
Why Migrate?
One of the standout advantages of migrating through Power BI Report Builder is the familiarity it offers to SSRS users. The tool’s interface and functionality resembles SSRS, ensuring a smoother transition for report authors and users. This similarity streamlines the learning curve, accelerates adoption, and minimizes disruptions during the migration process.
Through this migration, organizations can empower their users with enhanced data insights, improved data governance, and the ability to make data-driven decisions. Furthermore, the Power BI Service offers seamless collaboration and sharing options, enabling teams to work together efficiently and distribute reports to a broader audience.
Prerequisites
Below are the software requirements for migrating paginated reports to Power BI Service:
Power BI Service Subscription
You must have a Power BI Pro or Premium Per User license.
You should have write access to the workspace.
How to Migrate (SSRS) Reports to Power BI Service with Snowflake
Step 1
To start, you should ensure that you have Power BI Report Builder installed on your computer. You can download it from the Microsoft website if you don’t already have it.
Step 2
Download the latest version of Snowflake ODBC 64 bit driver.
Click on the Windows button and search for ODBC Data Sources 64-bit.
Select the ‘dBASE Files’ under the ‘User DSN’ tab and click the ‘Add’ button.
- Select ‘SnowflakeDSIIDriver’ and then click the ‘Finish’ button.
Configure this Snowflake Configuration Dialog with your username and the role assigned to you. Other things will remain the same. In the Authenticator, we pass the authentication method as an external browser, so that it uses user SSO login to Snowflake.
- After clicking ‘OK,’ you will be redirected to the external browser for the authentication process. You will then be alerted as to if the test connection was successful.
Step 3
Download the paginated reports (.rdl files) from your SSRS server or the report repository on your system.
Launch Power BI Report Builder and open the paginated report (.rdl file) you want to migrate to Power BI Service.
Change your database from the existing MS SQL server to Snowflake.
Right-click on the DataSource and then name it under the “Name” selection.
Click on the ‘Select Connection’ dropdown and select ODBC.
Click the ‘Build’ button -> Select the user or system source name -> Select your User DSN (here in my case, it is “Snowflake_ODBC_Prod”) -> Click on ‘Test Connection’ to check whether it is connecting successfully or not.
If the step above is successful, you should see MS SQL and Snowflake as two data sources.
Step 4
Copy the source T-SQL (Source database) query and open SQL Translation from the phData Toolkit to convert the SQL into Snowflake query automatically.
Copy the translated Snowflake query (check for any errors on the Snowsight interface and try running it successfully) and paste it on the Dataset section, and click ‘Validate Query.’
After that, check for the parameters, if any, and assign them values based on the query.
Click ‘OK’ and delete the previous datasource (i.e. connected to the SQL Server) and save the report.
Run the report to see the results.
Step 5
Ensure you are logged in to Power BI Service account.
To publish the report on Power BI Service, click the ‘Publish’ button as shown below.
Select the workspace where you want to publish the report and enter the file name.
Once the report is successfully published, you should be prompted with the link for the report on Power BI Service.
Click on the link or navigate to the report from Power BI Service GUI.
Conclusion
Migrating paginated reports to Power BI Service using Power BI Report Builder brings numerous advantages to organizations seeking to leverage the power of self-service analytics and interactive reporting.
Embracing Power BI’s capabilities not only streamlines the reporting process but also opens the door to a more engaging and data-centric approach to decision-making, ultimately leading to improved business outcomes.
