Model Architecture
To understand how Aimplan works, it’s helpful to break it down into two main parts:
- Input
- Output
We’ll start by examining the output, as it provides useful context for setting up the input effectively.
*Note that the Data Input Table works in a different way, as a data management tool. Read more on that here.
The outputs generated by your inputs are stored in an Azure SQL database, creating a standard fact table that you can connect to your Power Bi model as you would with any other table. The table will always reflect only the latest input for each unique key combination. In other words, for every unique combination of key fields, you’ll see a single, updated row in your storage table. If you need historical input data, the audit table stores all input data, organized by commit ID, allowing you to view previous entries.
Each fact table will include the following base fields:
- Meta data:
- RowID, Row Status: That tracks each row's identity and status.
- Created/Updated at: Timestamps for changes.
- Created/Updated by: User information for tracking edits.
- CommitId: Unique key for each saved input to enable tracking
- Value Fields:
- String value: Holds potential row comments.
- Comment value: Contains comments on individual cells.
- Value: Stores the actual numeric data.
- Key Fields: Fields that define the data context
- MeasureKey: Defines what each value represents.
- ScenarioKey: Allows the same data context to be represented under different scenarios (e.g., “optimistic” or “pessimistic”).
- Date Dimension: A necessary component, as planning data is typically time-sensitive.
- Custom Key Fields: These user-defined dimensions allow for flexible data segmentation and filtering in Power BI.
Each fact table can be visualized with the following color-coded schema:
- Orange: Table metadata
- Yellow: Input metadata
- Green: System dimensions
- Blue: Value fields
- Gray: Custom key fields
When planning your solution, keep this output structure in mind. Consider what data and structure will best support your end-users' requirements.
Once you have a clear understanding of the output structure, setting up the Aimplan solution becomes straightforward.
- Create the Storage Table: Start by creating a storage table that aligns with your desired output structure. Initially, this table will be empty until populated by data from custom visuals.
- Set up Scenarios and Measures: In the Portal, add the measures and scenarios you need to separate your input values.
- Design the Visuals: Once your storage tables are set up, you can proceed to develop the Aimplan Planning and Reporting Visual that will enable you to write data to your table. Look at this page for help: Step-by-step Reporting or reach out to [email protected] to get access to the Training.
With this in mind, the following chapters aim to help with common difficulties when setting up a data model in Power BI that is suitable for both output and input values.