Author: Etienne Oosthuysen
Businesses are increasingly embracing an empowered regime when it comes to data analytics and business intelligence. Subject matter experts inside business units are increasingly at the forefront of the creation of data models on which reports and dashboards rely. Various technologies (such as Tableau, Qlik and Power BI), now facilitate user-access to a wide variety of data sources and makes the task of data modelling easier than ever before.
Until now, these technologies did not draw a clear separation between modelling and reporting and dashboards. It meant that businesses locked themselves into one technology and that usually had costly licencing implications. One way businesses could overcome this inflexibility was to create data models in more advanced ICT based semantic model technology such as SQL Server Analysis Services, Oracle Essbase or IBM Cognos TM1. But authoring models in these technologies were often not within the skill-set of business based subject matter experts.
In an ideal world, data workers (including the business based subject matter experts) want easy and cost-effective environments to create and deploy data models (data acquisition, data transformation, enhancements and relationships) without having to learn very complex coding and technical skills. And then for the business to leverage such deployed models, either in a related visual technology or in another technology they may prefer altogether.
We are happy to announce that this is becoming increasingly possible. Microsoft recently introduced the ability to import Power BI Desktop files into Analysis services, and this is a serious game changer.
In this article, I will:
- Discuss what this means for businesses.
- Briefly delve into why tools such as Power BI, Qlik and Tableau lacked modularity and how this is now changing with the play between Power BI Desktop and Analysis Services.
- Walk the reader through deploying the Power BI Desktop authored model and how to make it an Analysis Services model.
- Describe some examples of what is possible with a deployed model.
- Use Tableau to connect to my new model for reporting and dash-boarding.
What does this mean for businesses (business benefits)
The user creates his/her model using Power BI Desktop; a free, easy to use business analytics tool provided by Microsoft. It has both comprehensive semantic modelling and reporting and dash-boarding capabilities, but for this article, we focus on the data modelling rather than the visual capabilities. Once created, the user can deploy their solution to a Power BI environment, or import it into an Azure Analysis Service model.
Deploy to a Power BI environment – In this deployment model, there is only limited the only separation between models, reports and dashboards. This applies to both Power BI Service (cloud) and Power BI Report Server (on-premise). The deployed models remain available mostly to Power BI visualisations, and to some extent, Excel.
Deploy to Analysis Services – if the solution is imported to Analysis Services then separation of model vs reports and dashboards are maximised. The advantages of this are:
- The Analysis Services model (that started life in Power BI Desktop) can now be accessed through other BI tools the business may choose to use for visualisation and self-service (reports and dashboards), for example, Power BI, Excel, Tableau and Qlik.
- It becomes easier for businesses to change their BI tools from one technology to another as the underlying data model (now in Analysis Services) remains in place.
- The business can control performance by changing the pricing tier of Analysis Services and scale up during peak workloads, and scale down when there is less demand for the data model.
The business can better control cost by pausing Analysis Services during zero demand periods. This is typically a much more compelling cost model compared to conventional annual licences.
A question of modularity
Ever since Microsoft introduced Power Query in Power BI version 1 a few years back, data workers found a powerful data modelling ally that gave them modelling capabilities (data acquisition, transformations, relationships, calculated column and measure, and hierarchies) without having to understand complex coding or data modelling skills. Competitors such as Qlik and Tableau, have similar capabilities, so a business’ preference for Power BI vs Qlik or Tableau (etc.) came down to factors such as familiarity, loyalty, perception and cost.
The problem with this was a lack of modularity (a lack of separation between the model itself, and how great the interactive visual report and dashboard capabilities the tools provide). If you created a model in Power BI Desktop or Qlik or Tableau, you were pretty much stuck with visualizations within your selected tool. There was no logical separation between the model and the visuals.
It is now possible to achieve modularity and separation of the model and visuals through the close relationship between Power BI and Analysis Services:
- The data worker creates his/her model using Power BI Desktop.
- The Power BI Desktop file (a PBIX file) is then imported into Analysis Services, and it becomes an Analysis Services model.
- The Analysis Services model can then be accessed for development and enhancement by the business and ICT.
- The Analysis Services model can be accessed by creators of self-service reports and dashboards through BI tools of their choice.
Gotcha – “Please note that for PBIX import, only Azure SQL Database, Azure SQL Data warehouse, Oracle, and Teradata are supported as model data sources. Also, Direct Query models are not yet supported for import. Microsoft will be adding new connection types for import every month and add additional functionality” – Read more here
Caveat – I am not saying modularity is a prerequisite to great BI solutions! Some businesses are pretty happy with whole adopting a comprehensive BI technology that includes the data model, visuals, and many other capabilities, but some of the business benefits achieved with modularity just won’t be available in such deployments.
I have a Power BI model; I want to maximize its use in other BI technologies, how do I do that?
If you do not already have an Azure Analysis Services service provisioned, one needs to be created:
- Log into your Azure tenant.
- Select New > Data + Analytics > Analysis Services.
- Complete all the required settings, and Create.
Add your Power BI model
- Open your Analysis Services service, and if it’s not already started, click on Start (please note you pay for this service for every minute it runs, cost stops when paused).
- In the Overview pane, open the Web Designer (as at 7th October 2017, this feature is still in preview, so functionality is still a work in progress).
- “Microsoft Azure Analysis Services Web Designer” is a new Analysis Services experience, currently still in Preview, that allows developers to create and manage Azure Analysis Services (AAS) semantic models quickly and easily. SQL Server Data Tools and SQL Server Management Studio remain the primary tools for development, but this new experience is intended to make simple changes fast and easy (including the ability to import Power BI Desktop authored models quickly).
- Click on Add a new Model.
- Select an appropriate name, and select the source of your data (this is either Azure SQL Database, Azure SQL Data warehouse, or Power BI Desktop file). For this article, we will select a Power BI Desktop file.
- Navigate to your Power BI Desktop file and select Import.
- If your Power BI model uses a data source not yet supported then you will receive an error as per below, and you will have to wait until your data source becomes available. See the gotcha earlier in this article.
• If your Power BI model uses available data sources and functionality, then your Power BI Desktop file (PBIX) is converted to Analysis Services.
Quick access to Web Designer to edit and query the model
You can immediately access your model right here in Web Designer and perform simple drag and drop queries, basic development changes, and edit relationships:
- Perform simple query drag and drop:
• Perform some basic development changes:
- Edit relationships:
More comprehensive editing and querying
You can alternatively also open the model in one of the following technologies:
- Visual Studio – for comprehensive editing
- Power BI Desktop – for comprehensive editing and visualisations
- Excel – for visualisations
Build Visualisations from your Model (in this example via Tableau)
- Open your BI tool of choice, for example, Power BI, Excel or Tableau. In this example, I am using Tableau Desktop (10.4 Profesional). Each BI technology may have slightly different ways of doing so.
- Connect to a server.
- Select Microsoft Analysis Services.
- Navigate to your Analysis Services service and copy the server information from the overview page:
- Paste the server information into the Server field.
- Select “Use a specific username and password.
- Use your Azure Active Directory (O365) credentials used for your Azure Services
- Select Sign-in.
- Now select the specific Database and Cube.
- The “Premiums and Claims” database and Model originated as a Power BI Desktop file, which was imported into Azure Analysis Services as explained earlier in this article.
- To start authoring your Visual reports, click on “Sheet 1”.
- Here is an example of my Tableau report created over my Analysis Services model that originated in Power BI Desktop:
Scale the performance within minutes (up/ down)
In case performance needs to be improved, navigate back to Analysis Services.
- Select the Pricing Tier, and scale the tier up to a higher performing service level (note this will mean higher pay-as-you-go costs.
- During the scale, up/ down process queries won’t be able to run.
- I observed significant performance improvements through the Tableau client when I scaled Analysis Services from tier S0 to S2.
Conclusion
As the Web Designer and its ability to import PBIX files as Analysis Services models go into general availability and mature after that, business will, if they want to leverage the advantages of modularity, have to keep an eye on this functionality.
This is game-changing as it will put pressure on competitors (such as Qlik and Tableau) to open up their models to other BI vendors.