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.
Note that this method is available with Reporting V3.4 or later.
During this manual you need to download and install various prerequired libraries:
- The Forecast Templates, fitting to your installed version of LUCS
- Python libraries which extend the templates with graphing abiligies
- Necessary Microsft Visual C (VC) dependencies
- GIT to grant you access to and sync with the the Luware python file repository necessary to work with the forecast templates.
All files and setups will be explained in the steps below.
Why is this necessary? By default Power BI does not come with the necessary features to display all reporting eventualities and scenarios.
Update Microsoft Visual C++
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.
- If necessary, update here https://support.microsoft.com/en-ca/help/2977003/the-latest-supported-visual-c-downloads,
- For 64-bit Systems download and install: vc_redist.x64.exe
- For 32-bit Systems download and install: vc_redist.x86.exe
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.
- Go to python.org
- Download the Python Python 3.7.9 - Aug. 17, 2020 version in the menu "Downloads" → A python.exe is being downloaded.
Newer versions may work, but are untested. We experienced issues with the most recent versions.
Start the install wizard by opening the executable.
Add to Enviroment Variables
Important: Make sure "Add Python to PATH" (Windows Variables) is enabled.
Otherwise Power BI and GIT might not find Python automatically. → You will have to add the PATH later manually or reinstall.
- Click through the remaining install wizard until "Install Now".
Precondition: Step 1 must be completed successfully.
- Go to gitforwindows.org
- Download the Installer files
- Start install wizard by opening the file. We recommend the following settings:
- Default branch name for new repositories: "main"
- Terminal Emulator: "Use Windows default console window"
- Choose setup default settings for anything else during installation (options, path command line, openSSL library)
Once installed, open GitBash via your Start menu → A command prompt opens.
Note that GIT looks similar to a DOS Prompt, with some key differences:
- you can paste your clipboard (e.g. commands shown below) via right click into the GIT bash window.
- GIT will pause operation when you click anywhere within the window itself. To resume, click on the window and move the cursor with a direction key.
- Create an SSH Key to get the credentials for the Luware Python package. You may also refer to → documentation on Generating a new SSH Key.
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.
Within GitBash enter:
ssh-keygen -t ed25519 -C "email@example.com"BASH
Important: Substitute the Mail address with your own. This is required to generate a random key unique to you.
- Continue pressing Return until you see an "randomart" image as follows:
Next is verifying your key and getting it ready for transfer:
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’ or other number
Now add the SSH private key to the ssh-agent by typing
→ Identity added: <Your Info> will be shown.
Copy the key to your clipboard via:
clip < ~/.ssh/id_ed25519.pubBASH
→ The key is now in your clipboard and can be pasted anywhere with CTRL+V.
Tip: by default the generated key is also stored in C:\Users\<YourUserName>\.ssh in a .pub file which can be opened in any text editor.
The result will look like this, of course with a key unique to you.
ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAIPaqFG9B0PLynvcDOxufWC0341WClag4F7/GdWVgXfLu firstname.lastname@example.orgCODE
Paste this Key into an email and send it to: email@example.com
Install and Update Luware Library
Precondition: Previous steps must be completed successfully. Luware must also have confirmed your SSH key so your local GIT repository can connect to ours.
First time Code Repository download
- Open GIT Bash
Install the Luware library with
$ pip install git+ssh://firstname.lastname@example.org/luware/bi-forecasting#egg=luware_mlBASH
You can paste your current clipboard by right clicking into the command window
- There may be a popup asking for permission. → Type "yes" and click on OK.
Existing Repository Update
- Open GIT Bash
Update the Luware library with
$ pip install --upgrade git+ssh://email@example.com/luware/bi-forecasting#egg=luware_mlBASH
You can paste your current clipboard by right clicking into the command window
- You should see a response like:
→ Ended with "Successfully installed <Library names>
- For this Step Power BI should recognize the Python PATH automatically (→ See "Python Setup" above).
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".
You can find these settings also under: File > Options > Data sources settingsWhen 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
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:
- Open the visual variant of the report.
- CTRL+Click on the "Filter" Icon (1) on the top left
- Select any Filter of choice (2)
- Click on the filter again to close the "Filter Overview" again.
- Wait for the Graphs to refresh → a small loading indicator briefly appears while this is happening.
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:
- Within Power BI go to "Transform Data"
- To add additional data, go to the "Home" Ribbon > "New Source" and select "Text/CSV". Then select the file to import
Your input CSV file should be structured as follows. The columns must have "Feature_" in the title.CSV example
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
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:
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.
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.
In the popup you can add filter requirements in the "Basic" section, or go to "Advanced". See example below.
In this example we grouped rows to count the total # of tasks per day.
Then we sorted rows from the beginning to now.
In order to merge the imported file with the current "Applied Step", click on "Merge Queries".
In the Popup, select the Dataset to import:
click on the "date" columns, in order to match the two datasets.
Then choose "full outer join" and click "OK".
Click on the two arrows at the column's header (data_inc_feature).
Select the Feature to merge
Untick the "Use original column name as prefix".
Continue with "OK".
- 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".
Click on "ci_df" to select the dataset with the forecast, this creates automatically the last step.
After the query and code preparations are done the visualizations can now be created.
- Choose the forecast results from "ServiceTask", prediction, down and up (confidence intervals), and the date.
No need to add an additional "feature" anymore as is already included in the forecast
- Add additional data as described in chapter "Data Import" above, then exit ans save.
Now create Visualizations, with Python not Power BI. Click on "Python visual" and add the Values "DateId", "Totals" and the imported Feature.
Choose which visualization should be shown. Note that ...
... there are 4 visualizations to choose from (see lines beginning with "plot_")
... 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.
- Don"t forget ot Save and Close your Report to not having to make the changes again next time.
If you want to keep different visualizations, save multiple variants of your prediction report.