PowerBI and AS Model Report

Author: Rory Gotz – Senior SQL Developer

The following report serves to provide detail on the BI development and changes that have occurred at a client site when upgrading the environment to SQL server 2016 Enterprise Edition.

Previous verses current BI Environment

The client had the following BI environment:

A SQL 2012 that provided reports using a tabular model which allowed users to connect to it via connection documents stored on a file server. This opens in excel with a connection to the model and analysis is then performed in an excel pivot table. In addition, SSRS reports and PowerBI Desktop which connected to a local model for data were made use of to view reports. The PowerBI Desktop software would have to be installed and managed and the report files hosted on a shared resource such as a file server share.

The client undertook a project to upgrade their MS SQL environment to SQL Enterprise 2016 which EOH assisted with. The SQL 2016 Enterprise license allows the installation of an on premise PowerBI report server to deliver PowerBI reports using a web browser, the same as standard SSRS, thus, allowing the reports to be easily available to consumers.

If one does not have an Enterprise license, then the on premise PowerBI Report Server can be licensed using a cloud PowerBI Premium license as illustrated below:

  SQL Server
Reporting Services
Power BI
Report Server
Power BI
Cloud Service
Deployment On-premise On-Premise Cloud
Power BI dashboards X
Power BI apps X
Power BI reports X X
Paginated reports (RDL) X X
Mobile reports X X X
How to buy SQL Server Std or Enterprise Power BI Premium or SQL Server Enterprise with Software Assurance Power BI
Feature updates SQL Server release cycle Rapid release cycle Rapid release cycle
Support lifecycle SQL Server Support policy Modern Lifecycle Policy Managed Service

This link provides a more comprehensive list.

Model and PowerBI Development

The model was developed according to design recommendations provided through internal training at EOH. Amongst other areas it helped the team to develop custom time intelligence functions. Due to the client making use of a custom calendar, the team could not use DAX’s native time intelligent functions such as TOTALYTD, SAMEPERIODLASTYEAR and PARALLELPERIOD.

The model is kept tidy and more easily consumed by grouping measures in measure tables and providing perspectives to users instead of the entire model. Now that the client is on SQL Enterprise 2016, access to grouping attributes in folders (previously supplied by BIDS Helper) and making the DAX measure more readable and versatile using variables are now possible.

The development and consumption of the PowerBI reports is performed as shown in the diagram below:

The original PowerBI visualizations were jazzed up and standardized using design principles provided by Enterprise DNA.

See a webinar of a Dashboarding Makeover Session using Enterprise DNA via Youtube here.

Leave a Reply

Your email address will not be published. Required fields are marked *