You can set varying attributes for members. When you work with varying attributes, you should first prepare a table called the “history” table. Once you have set up the history table, you are ready to complete the procedure in Setting Varying Attributes for Members.
The history table may contain a column (or more for outlines that support duplicate members) for varying attribute members, and several columns whose values can be used by Essbase to locate members in independent dimension.
For example:
Product Table | ProductID | SKU | Other product columns | Fact Table (can also be used to build the Time Dimension) | ProductID | Transdate | Other fact table columns | Product Package Table (history table for varying attributes [VA]) | ProductID | Pkg Type (VA) | From Date | To Date | | 1 | Plastic Bag | Jan | Jul | | 1 | Paper Bag | Aug | Dec |
The above example involves a fact table that contains the column “Transdate,” which can be used to build a Time dimension in Essbase Studio.
However, most Essbase Studio users do not build a Time dimension from the fact table. In fact, Oracle recommends that users NOT build any dimensions directly from the fact table. Therefore, the table schema would look like this:
Employee Table | EmpID | EmpName | SS# | Time Table (to be used to build the Time dimension | Columns for Date Time... | TimeID | Fact Table (can also be used to build Time dimension) | EmpID | Hire Date (TimeID) | Other fact table columns | Position Table (history table for varying attributes) | EmpID | Position (VA) | From Date | To Date |
The “Position” table has joins to the Employee and Time tables.
The dimension build query for the “Position” dimension involves the Time table. Since it is much smaller than fact table, it should not have a major impact on build performance.
If the “To” column is empty, the value of the “From Date” column can be the TimeID column.
Position Table (the history table for varying attributes)
| EmpID | Position (VA) | From Date (TimeID) |