In the Power BI Model, the model and data reside within the .pbix file. When published to PowerBI.com, the data can be refreshed from the source. Advantages of the Power BI Model approach include Cortana integration and natural language querying. The model data volume is limited to 250 MB (although this is the compressed figure – this restriction only becomes a factor for large CRM implementations).
Prerequisites
The following software must be available:
- A Power BI Pro subscription.
- Salesforce data source requirements:
An Informatica account.
An on premise Informatica Agent.
Microsoft Visual C++ 2008 Redistributable Package (x64) is required.
Dynamics CRM data source requirements:
Scribe Online account is required.
On premise Scribe Agent must be installed.
Scribe API access range must include the server's IP Address (Organization tab -> Security).
Scribe Dynamics CRM and SQL Server Database connectors must be enabled.
A destination database - either:
Azure SQL DB, or
SQL Server 2012 service pack 3 or later.
PowerShell version 3 or later must be available on the machine where the installation script is run.
Step 1: Configure the base INI file settings
A sample INI file has been provided (Release\Scripts\sample.ini) alongside the setup.ps1 script to configure the Solution.
Step 2: Configure the ETL INI file settings
Refer to the addendums depending on the ETL tool selected - Informatica or Scribe.
Step 3: Run setup.ps1
Open a new PowerShell console running as administrator.
Run the following PowerShell command to enable scripting for this session: Set-ExecutionPolicy Bypass
Navigate to the directory containing setup.ps1
Execute setup.ps1 with the following command: .\setup.ps1
Step 4: Post Deployment
If using Informatica, create a new Task using the newly created connections and select only the following entities to pull:
account
lead
opportunity
OpportunityLineItem
OpportunityStage
product2
user
UserRole
If using Scribe, edit the newly created Scribe Solution to pull only the following entities:
account
businessunit
lead
opportunity
opportunityproduct
product
systemuser
systemusermanagermap
territory
Step 5: Configure the Enterprise Gateway
Power BI Enterprise Gateway must be installed and configured if the database resides on an on premise server or in a VM (either on premise or Azure).
Step 6: Setting up the Dashboards using Power BI
Open Release\PowerBIReport\SalesManagementReportwithData.pbix
To refresh the report with data from the source CRM application, the data sources in the Power BI file must first be updated.
From the task bar, select "Edit Queries".
For each and every table in the model, repeat the following steps:
Select "Advanced Editor".
In the Advanced Editor, update the M script to refer to the data source instance and database name by replacing the highlighted areas: let Source = Sql.Databases("[server name]"), CRM = Source{[Name="[database name]"]}[Data], Smgt_AccountView = CRM{[Schema="Smgt",Item="AccountView"]}[Data] in Smgt_AccountView
After all tables' datasources have been updated, select "Close and Apply".
From the Report Editor, select "Refresh".
Step 7: Publish the report to PowerBI.com
To publish the report, select the "Publish" icon in the task bar. For on premise data sources or data sources in Azure virtual machines, the Enterprise Gateway must be installed and configured.
Congratulations - you have successfully deployed the solution template!
