Reverse Engineering an OLAP Database to Relational Modeling

Hello,

we will need an OLAP model transferred back to the standard tables of Relational Modeling. Since we have more than 100 dimensions, we'll not do this by hand. We already have some code that creates the tables with the meta data (cubes, dimensions, attributes, rules). But in order to publish the model, we'll also need a routine that creates staging tables for each dimension, and transfers the elements from the dimension hierarchies into them.

My impression is that lots of people need this, and there may be some initiatives to do the same thing.

Does it make sense to join forces? We will gladly share our code but would be even more glad if someone already had the missing part. Right now, we are running into a resource bottleneck to write the code ourselves.

Thanks and Regards

Martin

Creating a Remote Workforce Strategy_Webinar_6-2020_LN.pdf

Find more posts tagged with

Infor EPM Discussions

Comments

MikeLewis

Martin,

I would love to work with you on this. Let me know how I can help.

Mike Lewis

Nick Gladkov

Just also need to share that we are releasing November version with new functions for similar use cases. For instance, function OLAPExportHierarchy will export full dimension hierarchy to relational table. In short, this is our approach for this task:

Mike Potashnick

Nick,

Will this process write directly to the Multi-tenant cloud staging area and does this mean we can use the same method for writing meta data and data from SQL tables? Will this process replace the need for the enterprise connector to datalake to staging?

Mike Potashnick

Martin,

We have also written code to do this and to maintain the OLAP dimensions manually via App Studio.

Nick Gladkov

Mike, while technically we can connect to local SQL from MT, so far I haven't met any IT that would allow it. So Staging (which is just SQL tables) are normally being used. We can both write to them and also from them to create OLAP model. Why do you want to put it into local tables? Doesn't it just kill the idea of having cloud environment?

martin-reischl

Hi Mike, what exactly is your code doing? If we can use it to download dimensions to staging, I'd be interested, to bridge the gap until the November version will have this as a part of the product. Thanks and Regards, Martin

Mike Potashnick

The biggest issue we have found in setting up the dimensions in the relational staging, is the limitation on using SQL commands such as create table. We feel that we have to create each dimension table separately and we have a model with 90+ dimensions.

John Robson

Once you have your first dimension you can use it as a template for others by saying Add Columns from query then putting in a really simple query like Select * from DimMaster. Most if not all dimensions will have standard columns like ID, ParentID, Weight and OrderPostion so cloning one dimensions should do 85% of the work.

martin-reischl

What we are trying to do, is use an AppEngine routine to create the XML statement that d/EPM creates when you export the staging database. We'd then import the XML to create the tables. Then we can use another AppEngine to export the dimension elements and all the attributes to those tables. The second part would be done by the standard command that Nick refers to, starting 2021-11.

MikeLewis

Is the limitation in the BI# code, or is it on the database? It sounds like you are able to manually create a table. If so, can you create a stored procedure that creates a table and call the stored procedure from BI#?

I'm on Version 11 and use this:

SQLExecuteNonQuery(SQLConn,"IF OBJECT_ID(N'[OLAPDimensionsElements]', N'U') IS NULL BEGIN CREATE TABLE [dbo].[OLAPDimensionsElements]([Dimension] [varchar](71) NULL,[Element] [varchar](71) NULL,[DateExported] [datetime] NULL) ON [PRIMARY] ; END;",0);

Nick Gladkov

Mike, this is still available..... on premise. In the cloud we cannot allow this logic and have UI driven tables creation.

Mike Potashnick

Here's thought. Can we use an RPA engine to mimic all of the key strokes in the Staging UI to create the multiple tables?!

Copyright © 2025 Infor. All rights reserved.