Understanding Power BI
Power BI is Microsoft’s interactive data visualization and analytics tool for business intelligence (BI). As a business executive, you can use it to pull data from a wide range of systems in the cloud, such as Sellercloud. As well as create dashboards that track the metrics you care about the most, or drill in and ask questions about your data. You can create rich reports or embed dashboards and reports into reporting portals you already use. The dashboards, reports, and visualizations you can create go far beyond bar and pie charts.
The primary goal of a self-service BI is to give Business executives the power to quickly access the data they need and generate an easy-to-understand report with dynamic visualizations.
Understanding the roles needed to implement a Power BI solution
There are 3 major skill sets or roles needed to implement a successful Power BI project.
- Data Visualization (graphic designer) role. This developer needs to be able to design and develop rich interactive reports that clearly communicate the message in your data and quickly help you gain insights into your business data. (PBI Reports have a built Filtering Pane that can be used for Report filtering. There is no need to create custom filters on the report itself).
- Data Modeling role, including Data Load (accessing the data from one or more data sources) and Relational Schema Definition (defining table relationships). You find data sources, connect to them, import what you need, and then add any required calculations before creating dashboards.
- Solutions Architect role to design and architect the overall solution.
Understanding how to connect to your data
There are 2 ways to connect to your data source. You can import a copy of the data into the Power BI Desktop or connect directly to the data source using DirectQuery. You can see a comparison between Import and DirectQuery Models in the table below. You can expand the table by clicking on the accordion.
| CAPABILITY | IMPORT | DIRECTQUERY |
| Size | Up to 1 GB per dataset | No limitation |
| Data Source | Import data from multiple sources | Data must come from a single source |
| Performance | High-performance query engine | Depends on the data source response for each query |
| Data Change in the underlying data | Not Reflected. Required to do a Manual refresh in Power BI Desktop and republish the report or Schedule Refresh |
Power BI caches the data for better performance. So, it is necessary to Refresh to ensure the latest data |
| Schedule Refresh | Maximum 8 schedules per day | Schedule often as every 15 mins |
| Power BI Gateway | Only required to get the latest data from On-premise data sources | Must require to get data from On-premise data sources |
| Data Transformations | Supports all transformations | Supports many data transformations with some limitations |
| Data Modelling | No limitation | Some limitations such as auto-detect relationships between tables and relationships are limited to a single direction. |
| Built-in Date Hierarchy | Available | Not available |
| Clustering | Available | Not available |
| Calculated Tables | Available | Not supported |
| Quick Insights | Available | Not available |
| Q&A | Available | Not available |
| Change Data Connectivity mode | Not possible to change Import to DirectQuery | Possible to change DirectQuery to Import |
Advantages and disadvantages of DirectQuery model
The DirectQuery model’s advantages are as follows:
- Data is changing frequently, and near ‘real-time’ reporting is needed.
- Data is very large. The 1 GB dataset limitation does not apply to DirectQuery.
- Security rules are defined in the underlying data source.
- Data cannot leave the organization.
The DirectQuery model’s disadvantages are as follows:
- Your BI report performance depends greatly on the performance of the underlying data source. If you distribute your reports to a massive number of users, each request and each periodically refreshed dashboard tile will be sending at least one query per visual to the underlying source via the gateway. If your network/database/server becomes too bogged down, any query that takes longer than a few minutes will time out.
- When importing data, we can combine data from multiple sources into a single data model, mixing your own Excel or CSV files and SQL Server tables. But for DirectQuery, all tables must come from a single database.
- No built-in date hierarchy for DirectQuery. To counter that, you need to have a Date table.
- Very limited functions in calculated columns. Yes, the simple FirstName + LastName, in all examples given, can be done easily but anything a bit more elaborated will leave you scratching your head such as masking a partial Customer/Account ID or putting a period somewhere in the DeptID and append DeptName for display. Not impossible but you will end up finding yourself spending so much time looking up DAX or M language just to do simple things like that. My advice in this case: do it in the backend.
- The ability to define a calculated table using a DAX expression is not supported in DirectQuery mode.
- When using DirectQuery, it is not possible to use the Clustering capability, to automatically find groups.
Implementation recommendations
For small to medium-sized organizations you should probably use the Import model. Each client will need to create their own tenant on office 365 and buy the appropriate PBI licenses for each user. Users can sign up on powerbi.com. They will automatically appear in office 365. The client can either install Power BI Desktop on their dedicated SC server and connect to their SC database on the local server or connect through a VPN connection if they do not have access to their dedicated server. They will need to read rights to the SC database. They can either create their own pbit and pbix files or use a standard SC predefined template or report file.
Understanding the distribution of reports to clients (Templates)
Reports can be developed and packaged as templates and distributed to clients on dedicated servers. Templates consist of the data model and (optional) visualizations. It does not include actual data (unlike a pbix file that includes data also). The clients would then need to update the SQL connection to point to their SQL server with their credentials. Clients can be set up with VPN connectivity to their dedicated server and import data into power bi desktop on their office workstation or laptop.
Below is an example screenshot from a Sales Report:
Understanding publishing to Power BI
Power BI Desktop can publish the pbix file to a workspace in the Power BI service. The name of the pbix file is the Report and Dataset name, so a file named Sales.pbix will appear as Sales in the Reports and Datasets tabs of the workspace. See below.
A workspace can have many Reports and Datasets created from multiple pbix files with different names.