On a TechTalk session about Power BI, one participant asked me how to make sure that their data can be consumed on a Power BI. On this article, I will discuss five things that should be considered when preparing the data for Power BI. Let's discuss that five things.
#1 Understanding your data source
Please note, that not any data source can be consumed into Power BI (at least when this article is written). There are three main data that can be consumed namely cloud database (Azure), on-premise database (SQL Server with Analysis Services), and Unstructured file such as Excel or CSV. Let me give you a simple advice, Excel is a good start if you only have a simple data (I mean less than 10.000 rows and few relationship). Azure is only a good benefit if you already have the SQL Azure on production environment, SQL Server is also can be achieved if you already have SQL Server + Analysis Services (yes, you need analysis services, SQL server express is not work at this time article is written). How about, if you are using MySQL or Oracle, you should export it into an excel or csv. My recommendation is to create a regular and automatic export with your database agent and place it on a shared file/OneDrive so that the Power BI can get the latest data from your database.
#2 Start from Excel or Stick with Excel
Yes, at this time Excel is a good way to start a Power BI. Even, Microsoft 'Hero Demo' and its official demo uses Excel. So let's say Excel is a good starting point. How you structure yours excel. Here are the tips.
- A table mean a Sheet, so if you have five tables it might become a five sheets on an excel file.
- A sheet contains several columns. The columns don't need a primary key and foreign key, but you should make sure that the column is identical. Let say you want to join between Country sheet with Employee sheet. Therefore, on a Country you should have 'Country Name' column with its content and on Employee sheet you should also have 'Country Name'.
- Save the excel to a shared location such as SharePoint or OneDrive.
#3 Using Insight feature to help your Insight
One good feature on the Power BI is insight feature. On a simple word, Insight feature helps you to understand what can you do with your data. However, this feature is only available on a Power BI web. You can reach through your datasets and then choose Quick Insight
#4 Creating Dashboard Page based on Department or Business Need
One of the purpose of the quick insight feature is to understand the data and have ideas to create a dashboard. It should be good idea to create several dashboards based on department or business need. Try to design from left to right (or reading preferences). And then, creating a chart based on its importantly bigger means important, less means additional information.
#5 Enable Automatic Refresh
Automatic refresh only applicable if you have Power BI pro version. You can obtain it if you have Office 365 SKU that includes Power BI Pro. Furthermore, you can request Cortana (your personal assistance) to give you a notification when something good happens such as good sales, or business trends. You can try the Power BI Pro with 60 days trial.
So there are a five tips for Power BI environment, please stay tune for another Power BI content.