Today we’re happy to announce the public preview of solution templates for Power BI that helps customers very quickly deliver powerful analytics and visualizations on their source applications. We are launching with two templates: a sales management solution template for Dynamics CRM and a sales management solution template for Salesforce.
Solution templates simplify and accelerate building Power BI solutions on your applications. They offer a very quick guided experience to create compelling reports on an extensible, scalable, and secure architecture that you can then customize as you see fit. This means that instead of spending your time on the plumbing, you can spend it on extending and customizing the solution template to meet your organization’s needs.
A Power BI solution template includes three components:
- The ability to get data out of the source application with an initial full load and subsequent incremental loads (ie, just new and changed records) to get data into a datamart or data warehouse,
- A pre-configured database and Power BI model with extensible schema and calculations for measures and KPIs pertaining to the domain, and
- Quick start tools like a default set of Power BI reports that work out of the box and can be extended.
Note that customers will use Power BI solution templates to achieve different goals than their Power BI content pack cousins. Content packs help business users get value from their data in minutes, and are directly bound to their source application. Solution templates on the other hand help customers establish a full data mart or data warehouse that can be extended and customized to match unique data sources and business processes.
The end result is a collection of stunning reports and visualizations. The first sales management solution templates highlight sales performance over the past, and look ahead at what’s to come. One such report is reproduced below -
How to create a solution template
Building a solution template is fast and simple – just answer a few questions and provide information to the source application and a destination data store (Azure SQL Database and SQL Server are supported). We’ll take care of the rest, such as:
Build the schema in the target data store
Configure the data loads for regularly scheduled incremental processing from the Dynamics CRM or Salesforce (more source applications to come!) to the target data store
Provide pre-built Power BI reports that are bound to the data store
Solution templates provide two approaches to connect to your data from Power BI. Choose between importing the data from the data store into Power BI, or creating a live connection from Power BI to SQL Server Analysis Services (SSAS). When connecting live, no data from the tabular model is imported into Power BI Desktop, but instead every interaction with a visualization spawns a query sent to SSAS.
Live connections are required if the amount of data exceeds the Power BI model limit. This limit is currently 1GB and can be hit when creating a solution template on a Salesforce or CRM instance with 10 million or more total opportunities, accounts, and leads. Live connections are recommended under a few other conditions:
- When an organization wants their data to reside on-premises (the reports can still be published to PowerBI.com, as only query results traverse Azure under this scenario)
- When row level security is required, as solution templates create the row level security roles for SSAS models
Live connections require that SSAS be installed in tabular mode. This requires an Enterprise edition of SQL Server.
Why you need solution templates
Extensible, scalable, and secure are terms you see thrown around a lot, but we’re very intentionally using them here.
When we use the term extensible for solution templates, we’re explaining how anyone can take the solution template as a launching point to meet their own requirements. Most often, this means customizing the solution to address how your Salesforce or Dynamics CRM instance has been customized. This can also mean creating enterprise mashups by incorporating data from one or more other applications. For CRM solutions, this is often a vital requirement, as actual sales revenue and quotas are often maintained outside Salesforce and Dynamics CRM.
Solution templates are truly scalable because after the initial data load, the model only pulls new or changed records. We’ve collaborated with Scribe and Informatica for the ETL, and these partners provide the data integration and incremental data extraction capability that is needed to extract the often large data volumes from Dynamics CRM or Salesforce and send it into either Azure SQL DB or SQL Server. You can run the incremental jobs as often as you want to keep your reports fresh, even for very large CRM implementations.
And, in this context, secure means that solution templates leverage key aspects of the security model from Dynamics CRM or Salesforce and bake it into the SSAS model so that your users only see the data that they are permitted to see. In fact, extending the security model is the third most common customization, after custom objects and data mashups – something you don’t have to worry about when you use solution templates.
You can get started using Power BI solution templates here. If you want to see how everything works, or install using our scripts yourself, check us out at GitHub. Set-up is easy, but you’ll need the following:
- A Power BI account,
- Credentials for your Dynamics CRM or Salesforce, and
- An account with Scribe for Dynamics CRM or Salesforce, or Informatica for Salesforce.
We don’t quite yet have the automated setup available for Informatica – that’s coming in a month or so. There are currently special offers for Power BI solution templates accounts at both Scribe and Informatica.
Our solution integration partners have provided valuable feedback - so special thanks go out to Sikich, Hitachi Solutions, RBA Consulting, and Blue Granite for providing feedback on the sales management for the Dynamics CRM solution template, and to Blue Granite, RBA Consulting, Wicresoft, and Perficient for the Salesforce sales management solution template. These partners have deep experience both with Power BI and Dynamics CRM or Salesforce, and are recommended should you need any assistance yourself.
So check out the new solution templates, and let us know what you think! You can reach the team by asking a question or leaving a comment at our Power BI Community for solution templates .