Now we can connect our Power BI reports to the data sources hosted in the Azure VM and schedule refresh on the report. After the Personal Gateway is installed, we will be asked to sign in the Power BI online service with our Power BI account to setup the connection between the Personal Gateway and the Power BI services. The installation process is very simple, and we should not meet any problem here. However, to allow the online Power BI services to refresh the data sources hosted in the Azure VM, we also need to setup the Personal Gateway.įirstly, we need to download the Personal Gateway from the Power BI web site:, and install the app on the Azure VM. Install and configure Power BI Personal GatewayĪs we have our SQL Server engine and sample databases up running in the Azure VM, we can now connect to those databases to build Power BI reports and publish them to the online Power BI services. Now, we are ready to pin the SSRS charts to Power BI dashboard.Ħ. Then, we go to the Reporting Services Configuration Manager and register the Report server with the Power BI service.Īfter the report server is registered with Power BI services, we go to Report Manager, locate the report we want to pin to Power BI dashboard, and check whether stored credentials are used in the report, if not, we need to use stored credentials in the report. We can take the following steps to enable this feature.įirstly, we need to enable the SQL Server Agent. The 2016 version of SQL Server Reporting Services comes with a very cool feature that allows SSRS developers to pin SSRS charts onto Power BI dashboard. Adventure Works DW database 2014 (compatible for SQL server 2016).Adventure Works OLTP database 2014 (compatible for SQL Server 2016).The new Microsoft Wide World Imports Sample database for SQL Server 2016: (v=sql.1).aspx.Install Databases SampleĪ number of sample databases can be deployed in the VM as test data sources to support BI development: Some dev tools we need to install on the VM, including SSDT, Power BI Desktop and DAX Studio: After the Reporting Services is configured, we should be able to access the reports web portal to manage SSRS reports. The only configuration specific for SQL Server 2016 is related to the Power BI integration which will be covered later in the blog post. We should be able to find tons of guides on the Google. The steps to configure SQL Server 2016 reporting services are pretty similar to the configuration of previous versions. Next, we need to configure the reporting services through the Reporting Services Configuration Manager wizard. Before the change takes effect, we need to restart the SSAS services on the SQL Server Configuration manager.Īfter the SSAS services is restarted, we can see the SSAS is running using Tabular model. Open the file, and change the DeploymentMode from 0 to 2. If we need to work with the SSAS Tabular model, we can change the deployment mode in the SSAS configuration file which is located at C:\Program Files\Microsoft SQL Server\MSAS13.MSSQLSERVER\OLAP\Config\msmdsrv.ini. In the VM we have created from the SQL 2016 developer image, the SSAS is deployed in the Multidimenional mode by default. Configure the SSAS/SSRS services shipped with the imageĢ.1 (Optional) Change SSAS from Multidimensional model to Tabular model I have set the SQL connectivity as “Public (internet)” and enabled SQL Authentication in order to access the SQL server from client computers.Īfter the VM is provisioned, we can log onto the VM and we will find that the SQL Server 2016 database engine along with SSIS, SSAS and SSRS components have all been installed and set to run.Ģ. Lastly, we need to configure the SQL Server settings. We then need to configure the settings for Storage, Network, etc., we can use the default settings if there is no specific requirement applied. I chose DS11_V2 personally for my environment. Microsoft recommends DS3 or above for a production environment, but as we are going to use this VM only for development and testing, we can choose something smaller. Next, we need to select the size of the virtual machine from a broad range of options offered by Azure platform. Provision the Azure VM using SQL Server 2016 developer imageĪssuming we already have an Azure account, we can log on the Azure portal and select to create a new Virtual Machine using SQL Server 2016 RTM Developer image. On the Basics tab of the “Create virtual machine” wizard, we need to provide the name of the VM we aims to create and the username/password of the local administrator of the VM. (Optional) Install and configure Enterprise Power BI Gateway to support Direct Query and live SSAS connectionġ.Install and configure Personal Power BI Gateway.Provision the Azure VM using SQL Server 2016 developer image.This blog post walks through the steps to setup an Azure dev vm for testing Power BI SQL Server 2016 integration:
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |