I am new to DRM and trying to understand since i have a need to source data from DRM into warehouse tables for reporting.
I have few questions and was hoping to find answers at this forum.
Does DRM store data in a database so that we could query the hierarchies are flatten/de-normalize the data to report it of OBIEE ? if so does any one have good source for documentation on what would be a starting point to write the queries from.
In this infrastructure, we have the entire EPMA stack 11.2.x using DRM for managing the dimensions and other metadata.
Also was trying to see if there are any good articles on using DRM in datawarehousing.
Thanks in advance.
Yes DRM stores all information in the Database and maintains highest referential integrity, For each and every metadata object there is an associated table in the underlying database (Oracle/ SQL Server).
A good idea is to create a hierarchy export to a staging table (or) a generation export ( you need to depend on an external table as generation export can’t export directly to a DB table) based on your reporting (OBIEE) requirements.
Thanks for that precise reply OracleDRM, I am planning to source these Parent and Child using the RM_NoDE , RM_HIERARCHY AND RM_VERSION tables and expand these parent child hierarchies using a custom stored procedure which flattens it and makes it easier for me to join to the datawarehouse stating tables and COA segments tables for reporting.
Or do you think hierarchy export to staging tables is a better approach compared to querying DRM database directly.
Thanks again for your valuable inputs.
I am not sure why you are leaning towards a DB query, when it is much easier for you to export the required information from DRM UI, you may find it simple to extract at this time from DB tables, what if, if you need to source the associated properties?, you end up writing a complex query introducing lot of joins,
My sincere suggestion is to explore the capabilities of DRM UI and if your requirement is not addressed then go for DB tables.
Thanks for the reply OracleDRM , I was tending towards DB tables for DRM export is because I thought the data loading or capturing the changes would be easy to do from ODI and use its scheduler to run the loads every night or something. If using the DRM UI is much efficient then may it would be better to create a bat script and run this on demand or may also look into scheduling this.
Once I get these hierchicial data into staging tables then I would still need to flatten and report out of these tables.
Thanks for your advice and also please let me know if you know any good documentation on DRM capabilities in datawarehousing.