Microsoft Power BI Best Practices
I've been creating Power BI dashboards and wanted to share some of the best practices I've learned. Considering each of the below recommendations will add significant value to your CRM business intelligence implementation.
- Make datasets user friendly. Rename tables and columns to descriptive terms, remove unnecessary columns, hide columns not needed by users (such as index or surrogate keys) and make sure data types and formats are correctly assigned. Remember, we're using this tool to put business intelligence into the hands of analysts and power users so usability must be a top consideration.
- Reduce data model sources and columns/rows to only what data is necessary (for performance and too simplify data selection for users).
- Use the DateTime filter when a dataset includes data for multiple years. In Query view, you can apply DateTime filter options (This month, This quarter, etc.) or select Date Filters > Custom Filter and apply Boolean logic.
- On Dashboards, apply one or both types of report filters. Visualization filters apply to individual charts or visualizations. View filters (aka page level filters) apply to entire page.
- Data types should be specified in order to improve performance and usability. For example, names should be strings, revenue should be a decimal, headcount should be an integer and close date should be a date. Note that if Power BI detects a null value or error in a column record, it will not specify the correct data type for the entire column so you will need to manually make these corrections.
- When merging tables and applying primary/foreign key relationships, if possible, use single column integer keys for maximum performance. This could include CustomerID, ContactID, ItemID, OpportunityID, etc. or you may use or create a surrogate key for this purpose. This method will deliver a big performance improvement. If for some reason you don't want to use an integer key, date is the next best option.
- Try to always use a separate date table. This will likely better normalize the data and there are several (period-based) DAX formulas (TOTALQTD, TOTALYTD) which require a date table. CRM schemas generally have one date table while ERP schemas often have many (i.e. for order date, ship date, invoice date, payment date, etc.)
- Data models should be filtered and normalized. This is particularly important if datasets are sourced from high data volume repositories such as enterprise data warehouses.
- Normalize your data when building the dataset using the Designer Relationships view. Normalization separates unrelated table data (i.e. no need for Case and Campaign data to be in same table) and parses grouped data in a single column to multiple columns (date/time, names and addresses are prime examples) in order to optimize query performance.
- Use a star schema when designating table relationships. This consists of a central fact table (i.e. transactions) encircled by dimension (i.e. master) tables. A common CRM example is an opportunities fact table surrounded by the account, contact, salesperson (user), activity and campaign tables. Fact tables will normally include many transactions and have a date/time component. Dimension tables normally possess fewer rows and contain (generally descriptive) attributes about the transactions. When interrogating data, the dimensions are normally the row and column headers while the fact elements are the values in the body cells. Star schemas improve query and aggregation performance.
Star Schema
- CRM software is also prone to snowflake schemas. This topology has lookup tables which link to other lookup tables instead of linking back to the Fact table. This can limit scalability, but where data volumes are not large it also improves usability.
Snowflake Schema
- Denormalizing the data may be helpful for reporting purposes. Denormalization can simplify report or dashboard creation and improve read performance by adding redundant data or grouping data. It's possible that to create a star schema you will need to do some data denormalization. The optimal method is to create a database View that pulls from multiple tables, however, you can also do this in Power BI by merging queries or creating a query that combines data into a single dimension table.
- The Query tool accesses tables and views (along with many other types of files). A view is a saved database query to simplify the dataset or hide the query complexity. Views can also be used to hide or remove columns for security purposes.
- Power BI Q&A performs a keyword search on dataset tables, columns and calculated field names. It reforms your question with auto-completion and restatement, and picks the best visualization to display your answer. This type of simplified information accessibility is what drives ROI from business intelligence and CRM systems. The visualization also changes dynamically as you modify the question. Here's several tips to improve Q&A effectiveness.
- When you type a Q&A query, Power BI performs a keyword search and looks for an answer in any dataset that has a tile on that dashboard. Datasets not represented with a tile are not considered. Therefore, it's important to anticipate the types of questions that may be asked and design the dashboard with tiles from datasets which may respond to user questions. The Q&A algorithm then relies on the names of the tables, columns, and calculated fields. Therefore, make sure dataset, tables, column heading labels and calculated field names are illustrative of their data.
- User questions which require data from multiple entities require the dataset to join those entities. For example, if a user asks for the "sale opportunities by region", the opportunities entity and user entity must be joined for Power BI to recognize the relationship and return the answer.
- After using the Get Data function or importing data, you will often discover that the data types are incorrect. For example, revenues and company headcount may be imported as strings instead of decimal and integer. These data types should be resolved so the user Q&A is correctly interpreted by Power BI.
- Parse your data after an import. For example, break out name fields (first name last name), date fields (month year) and address fields (streets, city, state, zip, country). This will improve performance and permit more specific questions to be asked by users with the Q&A function.
- Designate columns which should not sum with the Do Not Summarize function (in the Reports view). Power BI enables column aggregation by default. However, certain types of data such as entity IDs (Customer ID, Opportunity ID, etc.), primary keys, dates, addresses and descriptive elements need not sum.
- Use the Data Category function (in the Reports view) to append the data type with additional meta data or semantic data. For example, you can designate a city as a city or a place or you can designate a number as a year, latitude, longitude or a postal code. In addition to getting more relevant Q&A responses, Power BI uses these additional designations with its geo and map visualizations.
- To improve the Natural Language Processing (NLP) Q&A results, add synonyms to your queries and columns. However, try not to add the same synonym to multiple tables or columns as that may dilute the query response. At the time of this writing, this feature was only available in Excel Power View however is expected to be delivered in the Power BI Designer in the near term.
- Inserting your cursor into the Q&A field will display a dropdown list of questions used to create tiles that are already pinned to the dashboard. This is an example of repurposing prior questions. In a similar way, when you recognize FAQs, you can also create featured questions for Power BI Q&A.
- Normalize your Power BI datasets. It's a good idea to have one dataset for each topic a user may pose a Q&A query. For example, it's good practice to break out (and create joined intersections for) the salesperson (user), account information and contact information from the opportunity entity.