This document describes the internal structure of the parts of the SQL database necessary to load dimension members and hierarchy structures through the backend using SSIS.
Each dimension definition has 2 sets of tables with 3 different versions of each table. Normally these tables are maintained by the Modeler, but it is possible to load members and hierarchy structures from the backend and Deploy the changes using SSIS or other tools that can update SQL tables and call a Web Service. Each dimension contains a member table and a hierarchy table for each hierarchy defined on the dimension.
The member table contains a record for each member in the dimension. The table contains the following fields:
- MemberId – a bigint value containing a unique identifier within the dimension for each member. The unique values can be generated any way you choose, but one an id is assigned and data is loaded for the member, the id can NOT be changed or the existing data will be lost.
Label – an nvarchar value containing a unique name for the member. This value is used to reference the values in reporting tools. The Label can be changed without losing existing data, as long as the MemberId is not changed.
Description – a meaningful and more complete description for the member for use in reporting.
Properties – a field for each property defined on the dimension. The field name is the same as the property name. The type and size of the field is determined by the property definition.
There are 3 versions of each Member table.
DS_DimensionName – this is the deployed version that should NEVER be changed directly. This version of the member MUST always match the live model in use for reporting.
O_DS_DimensionName – this is the work version of the table when it is checked out for changes by the Modeler. Normally you would not change it from the backend.
S_DS_DimensionName – this contains the most recent version of the members waiting to be deployed. To update dimension members from the backend, you should leave the dimension Checked In within the Modeler to prevent changes by other administrators, and update this table with your backend process.
Each hierarchy table contains a record for each Parent-Child relationship with a hierarchy. The table contains the following fields:
- MemberId – the bigint MemberId for the child.
ParentMemberId – the bigint MemberId for the parent. Each top level member of a hierarchy should have a ParentMemberId of 0.
SequenceNumber – bigint values used to keep the records in the correct order for the hierarchy. These values can be any values you chose to keep each set of children below each parent in the desired order.
There are 3 versions of each Hierarchy table.
HS_DimensionName_HierarchyName – this is the deployed version that should NEVER be changed directly. This version of the hierarchy table MUST always match the live model in use for reporting.
O_ HS_DimensionName_HierarchyName – this is the work version of the table when it is checked out for changes by the Modeler. Normally you would not change it from the backend.
S_ HS_DimensionName_HierarchyName – this contains the most recent version of the hierarchy table waiting to be deployed. To update the hierarchy from the backend, you should leave the dimension Checked In within the Modeler to prevent changes by other administrators, and update this table with your backend process.
After making changes to the appropriate dimension and hierarchy tables, a record in the Dimensions table must be updated to insure the Excel clients are notified to update the appropriate locally cached files. The ChangeDatetime field should be updated with a sql statement as follows:
update [S_Dimensions] set [ChangeDatetime]=GETDATE() where [Label]='DimensionName'
Deploying the changes
After making changes to the appropriate dimension and hierarchy tables, the application must be deployed with the CallistoAdmin Web Service. This web service creates additional SQL tables and rebuilds the Analysis Services database with all the latest information.
CallistoAdmin.asmx – this web service is contained in the Callisto_Server virtual directory of the web server.
DeployApplication2 – call this method to deploy the application. It expects one parameter which is the name of the Application to deploy.
SSIS Web Service Task
This standard task can be used to call the Web Service method to deploy the application.
Use the “Download WSDL” button to create the WSDLFile required by the task.
Enter the name of the Application to be Deployed as the Value for the sAppLabel parameter.
Enter the name of a text filename connection to capture any messages from the Deployment.
No entries necessary here.