Reverse Engineer an OLAP Database
This feature is only available for OLAP databases on SQL 2005 or later.
Overview
There is functionality that exists in the Jet Data Manager (JDM) that allows an organization to reverse engineer existing OLAP databases that reside on Analysis Services so that the data structures are created in the JDM. This can be useful when the organization already has OLAP cubes that have been previously built but, going forward, they will to be able to maintain the cubes in the JDM.
The feature to reverse engineer OLAP cubes using the JDM is not part of the standard licensing package of Jet Analytics. This feature is available for purchase.
Process
-
1 To use the Reverse OLAP functionality the user will navigate to the Cubes tab in the JDM, right-click the OLAP Server node, and select Reverse Olap Server 2005 and above.
-
2 This will open the Reverse OLAP Server dialogue box:
The user will then fill in the following fields:
Name : This will be the name of the OLAP node in the JDM
Server Name : This is the server name that the OLAP database to be imported resides on
Database : This is the OLAP database to be imported
The Create Data Structure box will be checked by default. This will read in the source tables and fields used in the OLAP cubes and create these data structures in a matching data warehouse database. If the box is unchecked the user will need to specify the data warehouse to be used as the data source for the cubes.
While the Jet Data Manager will create the cube structure and data warehouse structure it does not have knowledge of any intermediate steps or transformations used by the original system to get the data into the format that is used in the original data source. The user will still need to map the original data source into the provided data warehouse structure created during this process and apply data transformations as necessary.
Once the data has been entered, click the Reverse button to begin the process of reverse engineering the specified OLAP database. -
If there are computed columns in the original OLAP database, a window may appear asking if these should be converted to derived measures:
Selecting Yes will convert them to derived measures and selecting No will leave them as computed columns.
It will then ask if Jet Data Manager Server adjustments should be removed:
The default selection is Yes which will clean up naming conventions used by the Jet Data Manager when creating the data structures.
-
The resulting structure on the OLAP tab will recreate the following from the original OLAP database:
- Cube names
- Standard Measure names and formatting
- Derived Measure names, formulas, and formatting
- Calculated Measure names, formulas, and formatting
- Dimension names
- Dimension levels and formatting
- Dimension hierarchies
- Dimension keys
- Dimension joins on cubes
In addition, the necessary data structures will be add to the data warehouse node on the Data tab:
The user can now focus on mapping the original data source to the new data warehouse structure without the need to completely start from the beginning of the development process.