Articles in this section

Integration: Microsoft Power BI

Avatar
Jason Klein
  • Updated
Follow

The Logic Forte system can generate a variety of data files for download and analysis in Business Intelligence (BI) tools such as Microsoft Power BI. Contact us if you would like to setup integration with Microsoft Power BI.

Introduction

If you have never used Microsoft Power BI, you can follow these steps to connect your first data sources and setup your first interactive report. In this example, we will use Product Mix data file exports.

Before you begin, complete the following steps:

  • Download and install the free Microsoft Power BI Desktop application (download here)
  • Download all of your current Product Mix export files to a folder named "product".
    IMPORTANT: Do not copy any other data files into your "product" folder. The folder should ONLY contain a single Product Mix export file for each week you wish to analyze (e.g. 12 weeks = 12 data files).
  • Download your current Store List export file to a file named "store.csv"
  • Open Microsoft Power BI Desktop. If you see a screen similar to the following, you are ready to configure your data sources.

windows-power-bi-desktop.png

Configure Data Sources

Power BI needs to be aware of your data sources. In this example, we will:

  • Import records from the "store.csv" file into a table named "store".
  • Import records from all of the files in the "product" folder into a single table named "product".

Configuring Data Source for your Store List (aka "store")

  1. Click "Get Data" (on the black/yellow welcome screen OR on the "Home" ribbon menu)
  2. Choose "Another Source"
  3. Choose "CSV"
  4. Click "Connect"
  5. Select your Store List file named "store.csv". Not sure where to find your "store.csv" file? See "Before you Begin" in "Introduction" above.
  6. Preview your CSV Records. If they do not appear to be a Store List, cancel and start over.
  7. Click "Load" to import the records
  8. Wait several seconds for Power BI to import your Store List.

NOTE: If the data file containing your Store List was named something other than "store.csv", Power BI will create a table in your Model with a different name. You should go to the Model view and rename the new Store List table to "store".

 

Configuring Data Source for your Product Mix (aka "product")

  1. Click "Get Data" (on the black/yellow welcome screen OR on the "Home" ribbon menu)
  2. Choose "Another Source"
  3. Choose "Folder"
  4. Click "Connect"
  5. Select your Product Mix folder named "product". Not sure where to find your "product" folder? See "Before you Begin" in "Introduction" above.
  6. Preview your CSV File List. If they do not appear to be Product Mix files, cancel and start over.
  7. Click the "Combine" dropdown button.
  8. Click the "Combine and Transform Data" option in the dropdown menu.
  9. Wait several minutes for Power BI to import your Product Mix history.

NOTE: After you have configured your data sources, go to the Model view and confirm that a relationship was automatically established between the "store" table and the "product" table. Power BI should automatically recognize that both tables include a "Store" column containing your Store Numbers.

NOTE: If the folder containing your Product Mix data files was named something other than "product", Power BI will create a table in your Model with a different name. You should go to the Model view and rename the new Product Mix table to "product".

 

Create Report

Power BI is an excellent tool for building interactive reports. In this example, we will use the Q&A tool to easily add these 6 visuals to the first page of your Power BI report:

  1. Card showing Total Sales
  2. Line Chart showing Total Daily Sales
  3. Table showing Total Sales by Product
  4. Treemap showing Total Sales by Category
  5. Treemap showing Total Sales by Category and Sub Category
  6. Table showing Total Sales by Store

Add Visualization

  1. Go to the Report view
  2. Go to the "Insert" ribbon
  3. Click the "Q&A" button on the ribbon
  4. Ask a question about your data in the Q&A tool (see examples below)
  5. Click the small “Turn this Q&A result into a standard visual” button
  6. Repeat for each Visualization

Sample Q&A Questions

  • show total sales amount
  • show graph of daily sales
  • show treemap of sales by category
  • show treemap of sales by category and sub category
  • show table of sales totals by product name
  • show table of sales totals by store

After you create your visualizations, you can edit the interactions between your visualizations so that clicking on a visualization will filter the information shown in other visualizations. See example below:

Clicking a Category in the Sales by Category Treemap will filter Sub Categories shown in Sales by Sub Category Treemap

  1. Go to the "Format" ribbon
  2. Click the "Edit Interactions" button
  3. Click a Category in your "Sales by Category" Treemap
  4. Hover over your "Sales by Sub Category" Treemap and click the small "Filter" icon above the Treemap

Contact Logic Forte if you are unable to complete this Introduction to Power BI. We would be glad to walk through these steps with you and help you import your data from Logic Forte and help you create your first interactive report in Power BI.

Comments

0 comments

Article is closed for comments.