Connecting to Aimplan Data
Aimplan is storing data in Azure SQL tables. To access and retrive this data to Power BI (or other systems), you should have received login information from Aimplan.
The data you need in Power BI consist of two types of tables;
- Aimplan system tables, i.e. Scenario, User etc (where data is managed in the Aimplan admin portal)
- Custom 'storage tables' that you have yourself for your organization defined and created within the Aimplan admin portal.
The storage tables, you normally want to connect to using a 'direct queries' to get a feed of live data in Power BI. I.e. when you change some planning data in the Aimplan Planning and Reporting visual you want an immediate refresh of data back to Power BI. However, you might not update your Aimplan system tables that often, therefore you should always consider if a table sould instead be in Import mode.
In Power BI desktop, click the bottom 'Model' button on the left side to manage your model
Click the 'Get data' button in the toolbar
In the Get data window that appears showing all data sources Power BI can connect to, search for 'Azure SQL'
Mark the Azure SQL database, and click the 'Connect' button
Enter the Server and Database login information that you have received from Aimplan when signing up. Select if you want to import the table, or use live DirectQuery. Click OK.
In the next window, enter your login credentials. Under "Database" enter the user name / SQL Login and password you received from Aimplan.
Import the tables, and connect to the 'live tables' using direct queries
When you have got the tables into the model, clean up the model by
Changing the name of the tables from 'I000X DimUser' to 'DimUser' etc
Change the default 'Summarize by' property of all number fields except 'Value' to None (multi-select fields with a sum symbol by pressing ctrl, and in the Properties pane go to Advanced->Summarize by)
The final result will look something similar to the picture below. You have a few dimension tables such as Scenario and User that has been imported, and you have at least one fact planning table that is using DirectQuery (which is in Power BI indicated with a blue bar at the top of the table).
