Storage Tables
Storage tables are used to store data from Power BI. Storage tables are simply SQL Server tables with fields. Storage tables can physically reside within the Aimplan SaaS service, or if agreed with Aimplan, within your own Azure SQL database in your Azure tenant.
When creating a new storage table you will be asked which storage table template or type you want to create. Here are a description of the current tables types;
- Dimension Table - Is a table type that should store dimensional data not already part of the Power BI models you are using. I.e. if you do already have all your needed dimension tables within your existing Power BI models, you do not need to create any new dimension table using Aimplan. But if you need extra dimensions tables this kind of table is designed using a best practise dimension table design. The data in this kind of table is maintained using the 'Data Input Table' visual
- Fact Table - This table stores planning and forecasting write-back data edited using the Planning and Reporting Visual. It contains 11 mandatory fields that cannot be modified or updated. If these fields are missing, data from the visual cannot be stored in the table. Custom fields added to Fact Tables does not allow null values, as they serve as key fields and cannot be joined if they contain nulls. To ensure data integrity, it is recommended to set a default value, such as “N/A,” for each custom field to prevent null entries.
- Other - is a table type that will be used to store any other type of data that is not dimensional or fact above. Here you can define your own fields. The data in this kind of table is maintained using the 'Data Input Table' visual
Creating storage tables consist of two main steps; 1) to design the table with fields, 2) process the table and physically create the table within Azure SQL
To create a new table, click the 'Create Table' button in the toolbar and choose 'Create New'. To edit the design of an existing table, click the blue name link.
Fact Table
- Give the new storage table a name. Preferably starting with "Fact..." something to indicate to Power BI design users that this table contains fact data
- Select 'Fact Table' in the drop-down where you select table type
A table will be created, and by default it will contain some mandatory fields. You are not allowed to change or delete these fields.
To your new fact table you will probably like to add dimension 'key-fields' or 'Id-fields' such as AccountKey, CostCenterKey, ProductKey, CustomerGroupKey and similar.
- Click the 'Add new' button in the toolbar
- In the list of fields a new field will be added where you will edit the properties of the field. These properties correlates to SQL Server field properties.
When adding a dimension fields, examine your existing model to decide which data type you want to use for the dimension. If you have an INT key in your "Account Table", use an INT here also and the same for NVARCHAR.
In the menu 'Create Table', you'll find the 'Duplicate' option. This option will let you copy the field definitions and possible Indices from another storage table.
In the dialog you will need to enter a name of the new storage table, the name cannot alreay exists. Select the table you want to copy the definitions from and click on 'Create'. When the table is created you will automatically be naviaged to the newly created table.
If an event is attached to the source table, it will not be copied for security reasons. You must manually add an event on the newly created table if you wish.
See Organization and Categories to learn about how to manage organizations and categories.
The objective of a dimension table is to provide a template for how a best practice dimension table should be configured. This type of table is only used in combination with the Aimplan Data Input Table visual, not the Planning and Reporting visual. The table structure can be changed and there are no mandatory fields (more then having a primary key).
The objective of a this table template is to provide a template for how any other type of table should be configured. This type of table is only used in combination with the Aimplan Data Input Table visual, not the Planning and Reporting visual. The table structure can be changed and there are no mandatory fields (aside from having a primary key).
When you have added/edited the table and saved it, you'll need to process the table. The "Save" button is only saving the definition of the table. In the menu you have the "Process" button. Click on the button and the table will be created/updated.
If you are running your own Azure SQL database with a SLA2 agreement you will find more information about configuration of storage tables here: Customer managed Azure SQL
If you for some reason wants to delete a table, it's possible to do that by clicking on the "Delete Table" in the menu. When you click on this button a verification popop is showed where you need to fill in the table name since when the table is deleted then it's gone forever.
Restoring a table is available when you have changed one or more columns and clicked on "Save" but you have not processed the table. As long you have not yet proceessed the table, you can restore all fields to the same definition as the physical table has.
In the menu you have a "Data Button". When you click on this button a new grid is showed where you can see the first 8000 records in the table. In this grid, you can delete some records by checking the checkbox on the left on each row and then click "Delete Selected(xx)".
If the 8000 rows you are viewing don't display the specific rows you're looking for, you can utilize the column header to filter the data. This server-side filtering ensures that new rows will be fetched while you're in the process of filtering. It's worth noting that if you select certain rows by checking their checkboxes and subsequently modify the filter, causing the selected rows to disappear, those rows will persist in their selected state (as indicated by the caption on the delete button).
Additionally, there's a dedicated button in the menu labeled "Selection" with three options:
- Select All: This option will choose all rows currently visible in the grid.
- Deselect All: Use this option to unselect all rows that are currently visible in the grid.
- Clear All: This option removes all filters, even those that are not currently visible in the grid.
You can also truncate the complete table by clicking "Truncate Table". When you click on this button, you'll need to fill in the name of the table to continue the truncation.
There is also an option to import data from a CSV-file by clicking on the "Import" button in the menu. Please read the Data Import section that describes the import functionality.