Sign Up for Training |
Jet Express Support |
Jet Reports Company Site
Jet 365 Financials
Jet Professional
Jet Enterprise
Downloads
Community
Submit a Request
Give Feedback

How to Reverse Engineer an Existing OLAP Database


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 Enterprise. This feature is available for purchase.

Process

To use the Reverse OLAP functionality the user will navigate to the Cubes tab in the JDM, right-click on the OLAP Server node, and select "Reverse Olap Server 2005 and above".

This feature is only available for OLAP databases on SQL 2005 or later.


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 the user will 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.

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments