The following manual explains: 

  • How to install Python and necessary Librarries via GIT on your PC to enable graphical charts in your BI Reporting.
  • How to import and merge data rows and modify the queries to include the data
  • How to use the forecast variants included with your BI Report template by importing CSV data.

(question) Why is this necessary? By default Power BI does not come with the necessary features to reflect all reporting eventualities and scenarios. With the steps below you always will have access to the latest files. 

(lightbulb) Note that this method is available with Reporting V3.3 or later.

Python Setup

  1. Go to python.org
  2. Download the latest Python version in the menu "Downloads" → python.exe is being downloaded.
  3. Start the install wizard by opening the executable.

    Important: Make sure "Add Python to PATH" (Windows Variables) is enabled. Otherwise Power BI might not find Python automatically. → You will have to add the PATH later manually or reinstall.

  4. Click through the remaining install wizard until "Install Now".

Install GitHub

(tick) Precondition: Step 1 must be completed successfully.

  1. Go to gitforwindows.org 
  2. Download the Installer files
  3. Start install wizard by opening the file. We recommend the following settings: 
    1. Default branch name for new repositories: "main"
    2. Terminal Emulator: "Use Windows default console window"
    3. Choose setup default settings for anything else during installation (options, path command line, openSSL library)
  4. Once installed, open GitBash via your Start menu → A command prompt opens.
  5. Create an SSH Key to get the credentials for the Luware Python package. (lightbulb) You may also refer to → documentation on Generating a new SSH Key
    (question) Why is this necessary? With this key we uniquely identify your contact and repository to connect you to the Luware GIT repository for the latest files. This procedure only needs to be done once, unless your reporting user's contact address changes.
    1. Within GitBash enter: 

      ssh-keygen -t ed25519 -C "your_email@example.com"
      BASH

      (tick)  Substitute the Mail address with your own.
      (lightbulb) You can paste your current clipboard by right clicking into the command window

    2. Continue pressing Return until you see the "randomart" image as follows:


  6. Next is verifying your key and getting it ready for transfer:

    1. Check if the key is now active and running by typing: 

      eval `ssh-agent -s`
      BASH

      You will see a response like ‘Agent pid 1234’

    2. Now add the SSH private key to the ssh-agent by typing

      ssh-add ~/.ssh/id_ed25519
      BASH

      Identity added: <Your Info> will be shown.

    3. Copy the key to your clipboard via:

      clip < ~/.ssh/id_ed25519.pub
      BASH

      → The key is now in your clipboard and can be pasted anywhere with CTRL+V. 

  7. The result will look like this, of course with a key unique to you. 

    ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAIPaqFG9B0PLynvcDOxufWC0341WClag4F7/GdWVgXfLu your_email@example.com
    CODE

    Paste this Key into an email and send it to: christoph.odermatt@hotmail.com

Install and Update Luware Library

(tick) Precondition: Previous steps must be completed successfully. Luware must also have confirmed your SSH key so your repository can connect to ours.

First time Code Repository download

  1. Open GIT Bash
  2. Install the Luware library with

    pip install git+ssh://git@github.com/ccasimiro9444/luware#egg=luware_ml
    BASH

    (lightbulb) You can paste your current clipboard by right clicking into the command window

  3. (info) There may be a popup asking for permission. → Type "yes" and click on OK.

Existing Repository Update

  1. Open GIT Bash
  2. Update the Luware library with

    pip install --upgrade git+ssh://git@github.com/ccasimiro9444/luware#egg=luware_ml
    BASH

    (lightbulb) You can paste your current clipboard by right clicking into the command window

  3. You should see a response like: 

    → Ended with "Successfully installed <Library names>

Update Microsoft Visual C++

(tick) Precondition: This step may be optional, depending on previously installed .NET and Visual C packages on your computer. We added it as a safety precaution in case you encounter unexpected errors in the previous or upcoming steps.

  1. If necessary, update here https://support.microsoft.com/en-ca/help/2977003/the-latest-supported-visual-c-downloads,
    1. For 64-bit Systems download and install: vc_redist.x64.exe
    2. For 32-bit Systems download and install: vc_redist.x86.exe

(lightbulb) In most cases you can simply execute and perform the setup. It should automatically detect if you need any updates and can be installed "over" existing packages.

Data Import

Precondition

  • For this Step Power BI should recognize the Python PATH automatically (→ See "Python Setup" above).
    (question) Encountered problems? Check out the Python section of the official Microsoft Power BI documentation.
  • For the Python scripts to work properly in the Power BI service, all data sources need to be set to "public".
    (lightbulb) You can find these settings also under: File > Options > Data sources settings 

    When you see this pop-up, choose "Public"

The next step varies depending on which template you want to use: 

  • A: Visual Variant: Filename "LUCS_Forecast_3.4_Visual.pbix" - Simple to use with predefined filter sets, but doesn't allow data export.
  • B: Transform Variant:  Filename "LUCS_Forecast_3.4_Transform.pbix" - Allows to export data, but there are no predefined filter sets. The only way to filter data is the "Transform Data" ribbon in Power BI.

A: Visual Variant

(info) This variant is recommended for most users as it directly uses the graphs without special BI knowledge required. This variant is mutually exclusive to → Variant B:

  1. Open the visual variant of the report. 
    1. Click on the "Filter" Icon (1) on the top left 
    2. Select any Filter of choice (2)
    3. Click on the filter again to close the "Filter Overview" again.
  2. Wait for the Graphs to refresh → a small loading indicator briefly appears while this is happening.

    (tick) At this point you are done. You may want to read on if you want to add your own data to the forecast.

Optional Step: Expand Forecast with your personal Company data

In cases you want to add personal data to the forecast template, perform the following steps:

  1. Within Power BI go to "Transform Data"
  2. To add additional data, go to the "Home" Ribbon > "New Source" and select "Text/CSV". Then select the file to import
  3. Your input CSV file should be structured as follows. The columns must have "Feature_" in the title. 

    CSV example
  4. When the file is ok, continue with "OK". → Data is now imported to "Fact"

    CSV example

    5. Your data fields will now appear in the "Fields" sidebar.

B: Transform Variant

(info) This variant is recommended for advanced users who want to transform and export data to their needs. This variant is mutually exclusive to → Variant A:

  1. Go back to the main "Fact", "Service Task", and "Applied Steps" entries list.

    Until we add the additional features, the two entries "Merged Queries" and "Expanded data (1)" do not exist yet.
  2. Between the Applied Steps "Source" - which is the final step loading the dataset -  and the "Grouped Rows" step you can filter rows for the tasks to forecast.

  3. In the popup you can add filter requirements in the "Basic" section, or go to "Advanced". See example below.

  4. In this example we grouped rows to count the total # of tasks per day.

  5. Then we sorted rows from the beginning to now.

  6. In order to merge the imported file with the current "Applied Step", click on "Merge Queries".

  7. In the Popup, select the Dataset to import:

    1. click on the "date" columns, in order to match the two datasets.

    2. Then choose "full outer join" and click "OK".

  8. Click on the two arrows at the column's header (data_inc_feature).

    1. Select the Feature to merge

    2. Untick the "Use original column name as prefix".

    3. Continue with "OK".

  9. The next step is the Python code. This step should be inserted for the customer already. In the "Transform" menu tab, go to "Run Python script", copy/paste the code. Click "OK".
  10. Click on "ci_df" to select the dataset with the forecast, this creates automatically the last step.

Create Visualization

(tick) After the query and code preparations are done the visualizations can now be created.

  1. Choose the forecast results from "ServiceTask", prediction, down and up (confidence intervals), and the date.
    (lightbulb) No need to add an additional "feature" anymore as is already included in the forecast
  2. Add additional data as described in chapter "Data Import" above, then exit ans save.
  3. Now create Visualizations, with Python not Power BI. Click on "Python visual" and add the Values "DateId", "Totals" and the imported Feature.

  4. Choose which visualization should be shown. Note that ... 

    1. ... there are 4 visualizations to choose from (see lines beginning with "plot_")

    2. ... only one of the last 4 lines can be shown at the same time. 
      A hashtag in front of the statement will "comments" a line out as seen in the example below.
      Comment 3 lines out (or delete those lines) and leave one line with the visualizatuib function.

  5. Don"t forget ot Save and Close your Report to not having to make the changes again next time.
    (lightbulb) If you want to keep different visualizations, save multiple variants of your prediction report.