Build Secure Data Collection Apps in 1-2-3.

Power BI is an amazing reporting and dashboarding solution that everybody is talking about. With Visual Planning/Visual Modeler from Power ON, you can now develop Power BI Apps that is used for data collection. We have created a product sheet for Visual Modeler.

Use cases

Sales Forecasts, Inventory Management, Salaries and Benefits, Variance Reporting, Assets and Depreciations, Skills Review, Time Tracking, Flight Maintenance, Customer Self Service, Vendor Sourcing… Our customers come up with all different use cases.

Let’s review the end-result:

In order for Power BI to collect numbers, text and commenting on data-points from Power BI, you need a location to store meta-data, dimensions, attributes, fact data, and comments. Visual Modeler automates this process for you. The final Power BI Data Collection App will look like this:

l

Components:

  • SQL Database stores all transactions, dimensions, values, and comments
  • Tabular Model stores all Measures and KPI’s with DAX
  • Power BI Service stores the Power BI Desktop Workbook, security rules, and App definitions.
  • Power ON provides the SQL and Tabular model in sync.

Now end-users can enter data directly in Power BI and update the tabular model.

Now let’s create an App. It is done in 1-2-3 steps with Power BI tools.

Ingredients:

  • 1 Visual Modeler Solution from Power ON
  • 1 Power BI workbook with Power Queries to the data you want to include
  • 1 Power BI Workbook for end-users to enter data in

Complextiy: Low Skills: Power BI & Power Query Time: 3-4 hours

Steps:

1 – Staging – Power Query

In order to develop a Data Collection App, we need all the historical information, along with products, sales people, customer, vendors, inventory, employees, assets, bonuses, salaries…any information that the end-users will need in order to enter a forecast or projection, text comment or comment to a variance.

  • Create a new Power BI Workbook
  • Create a Power Query for each table
  • Schedule it to refresh in Power BI

Note: For the Fact tables, make sure you add a column called ‘Version’ and populate historical data as ‘Actual’ and data that users can change like, ‘Budget’, ‘Forecast1’,’Projection2’… You can also use Power BI Dataflows and do the same thing as above.

2 – Production SQL and Tabular Model – Automagically

The Visual Modeler Wizard will now create a SQL Server Database and a Tabular Model for you – Automagically!

  • Start Visual Modeler
  • Select the Staging Power BI Dataset
  • Enter Target Servers for the Data Collection Databases
  • Press ‘Create’.

This step created a SQL DB and a Tabular Model with standard measures that where you can now enter data.

3 – Develop the Power BI Workbook and the Power BI App – Power BI

The final step is to develop the Power BI workbook where end-users or ‘contributors’ will be entering their values and text comments.

  • Create a new Power BI Desktop file
  • Connect to the Tabular Model from ‘Step 2’ above.
  • Use Power ON’s Visuals to replace the Microsoft Power BI Grid visual. This will enable the write-back.
  • Create the Power BI app, and publish it to the people that should have access to it.

    Benefits:

    • Develop a new app in hours
    • Reuse data between models
    • No learning curve
    • Save $MM’s compared to TM1, Anaplan, IBM
    • Contact us to speak to some of our happy, referenceable clients.