How to maintain individual dimension structures

Hi,

Some thoughts about building and maintaining dimension-structures in V12:

I have lots of old V11-Importmaster-Jobs in which dimensions are updated. Usually they all work in the same way, e.g. for accounts:

Run a query on a table with all accounts from the accounting-system
Check, if account already exists
If not: write a message in the protocol, add the new account and assign it to its node.

The challenge is: the main structure (nodes) of the PNL or Balance Sheet is available in the accounting system, but there is only one chart of accounts!

Now the requirement is that in controlling dept in addition to the standard chart of accounts they need various and flexible nodes to build sums over different sets of accounts which are from different nodes in the standard chart. So one base-account can be member of a lot of nodes. This can result in a number of parallel hierarchies.

This flexibility cannot be maintained in the accounting system, maybe one alternative chart of account would be possible but not unlimited.

In V11 this was an easy thing: Just adding some new elements and do the node-assignments in OLAP-Administration and done!

Now as we are in the migration to V12 and to become one day cloud-ready, I want to replace the Importmaster-Jobs more and more with native EPM-mappings and modelling tools.

I understand, that for this the whole structure of the dimension must be available in a relational structure, so not only new accounts or the standard chart, but the whole dimension including all individual nodes and elements, because when using a EPM mapping the dimension is always uploaded as whole.

What is the best way to easily maintain the individual structures in a dimension that cannot be queried from another source-system, as they are built in the head of the controllers?

Good thing would be, to maintain these individual structures in an Excel-Sheet, as everybody is familiar using Excel and it is very easy to copy 500 lines and search and replace things, make comments and formatting for easy reading. But how can the content of this Excel be uploaded into the staging database to link it to the other “standard” content of the dimension.

Importmaster is one way, but as said, I want to get rid of this tool.
SQL-Server Datatools or SSIS_Packages is another way – very technical.
Exporting XLSX to CSV and upload this in repository and then using an APE-Script is technically possible, but this is inconvenient and several steps to execute.
Giving users direct access to the staging database so that they can write their own INSERTS- and SQL-Statements – will end in chaos….

I assume, that other “legacy-V11/MIS-Customers” face this challenge as well.

I’m interested in how others are facing this challenge, what is your best practice to maintain individual dimension structures, what is your “replacement/successor” of OLAP-Administration-Tool?

I know that in V12 Relational- and OLAP modelling such functionality is available, but I’m talking of dimensions with hundreds or thousands of elements and I think the existing tools are good for smaller dimensions like versions etc, but not comfortable to handle big dimensions as accounts, customers, items etc.

Looking forward to your experience,

Ralf

Find more posts tagged with

Infor EPM Discussions

Comments

Llorenç Tomas

Hi Ralf,

Although we still not have a lot of V12 installations I can tell you what we are doing about maintaining dimensiones. We can have 4 casuistics:

1) Standards dimensions such as consolidation statutory --> Manually by Business Modeling (also from Excel)

2) Customized dimensions not standards, by OLAP Modeling, here you feed the source staging tables by different methods:

* Import Master (On Prem)
* Manually importing from CSV
* Querys based
* From DataLake with ION Workflow (Cloud). DataLake data are coming from:
* Enterprise Connector (tested and running )
* SFTP (not tested yet)
* API's (not tested yet)

3) Customized dimensions not standards and out of OLAP Modeling (option 'PreserveManualAddedObjects') maintained with an App Studio that writes directly to OLAP pretending the same functionality as OLAP Administrator in V11. Here we have an App Studio report reused in several installations that 'read' automatically all the cubes/dimensions of the repository where installed and allows to maintain hierarchies, dimensions values and attributes

4) Customized dimensions not standards in OLAP Modeling maintained with an App Studio that writes directly to OLAP, same as point 3 but in addition is also writing to the Staging table avoiding publishing the OLAP Modeling and guaranteeing the synchronization between OLAP and Staging tables

I hope it helps you

kind Regards,

gottesheim-daniela

Hello Ralf,

we use Infor EPM version 12 in combination with ImportMaster to maintain and update our dimension. We retrieve most of our information regarding the structure and elements of a dimension out of relational tables, which are filled with data from our source systems (ERP, CRM,...). In addition, some elements are maintained directly in the ImportMaster definition file. These are for example top elements, but also manually maintained elements like years, months or measure elements. So elements in dimension which don't change or change rather slowly.

It could also be that elements are maintained in IM scripts or in relational tables which are updated manually. The access to these "manual tables" or even the whole database is limited to admin users. No general user can directly change the structure or elements of a dimension.

We now face the challenge to replace the ImportMaster and move all the information to relational tables which are the basis for OLAP Modeling. Infor provided AppEngine scripts which transferred all the information from the existing OLAP model to these relational tables. So the basis was set rather automatically. Our biggest task now is to ensure that the relational tables are updated regularly in order to add new items, customers, cost centers and so on to the dimensions.

Nevertheless we will keep maintaining dimension structure end element information manually. This will most likely take place in relational tables in either the staging DB or our company-internal data warehouse. But there is no guarantee that maybe some AppEngine script will include hardcoded dimension elements and hierarchies as well ;-)

Best regards, Daniela

martin-reischl

Hi,

as for myself, I strongly plead for using the OLAP Modeling in V12. The only obvious exception is the standard Business Application, which uses Business Modeling.

One addition I'd like to make to the previous comments, the "reverse engineering" from the OLAP model to OLAP Modeling will create a "DatasourceID" column in every table, analogous to V11 Designer. You can use this column to know which element, and which relationship, is coming from a source system, or has been added manually. I recommend to use it in the situations you describe - so if a hierarchy changes in the source system, you can change it in the dimension without interfering with any manual hierarchies.

Martin

Ralf Zerfass

Dear all,

thanks for your helpful replies which are really appreciated.

Seems that we all have more or less the same ideas and workarounds to maintain individual dimension structures.

Probably we (the old V11-/MIS-community) have to arrange with the new processes of generally speaking relational modelling and bury the idea of quickly adding a new aggregation or element in a dimension within 2 minutes, unless everybody builds his own modelling tool in AppStudio in combination with APE-processes (which I already have, but I’m not really happy with it.)

What about the idea of making this discussion as a topic in the next EPM User-Group-Meeting and may be somebody can show his solution or Infor gives a live demonstration of how to maintain complex dimensions with different sources using the standard tools of V12?

Best regards and a nice weekend,

Ralf

MikeLewis

I think this would be a great topic because it is so fundamental. I assume Nick is watching this, but I'll contact him to make sure he is aware of this topic.

Copyright © 2025 Infor. All rights reserved.