The customer would like to pull hierarchies from business viewpoint instead of pushing via the relational adapter.
They are hoping for business viewpoint to source the hierarchy rollups.
Resolving the problem
Best Practice Recommendation
It is best practice to subscribe to the BV repository using one of the
adaptors supplied by IBM.
In this case, it is recommended that they use the Relational Adaptor
where they can map the hierarchical data and element attributes to a
table of their own design. We generally recommend a separate staging
table for each dimension that they wish to integrate with, although
this can be generalized to share a table if they desire.
In general, the structure will need to be:
Child_Key, Parent_Key, Child_Attr1, Child_Attr2, , , , , Child_Attrn
The data in this staging table can then be processed according to their
needs, which might be to support a child / parent SQL statement into
their application, or may be further processed to expand this into a
"flattened" hierarchy table structure of the type often used in a
Once the subscription is built, it can be automated with command line
instructions so that they can run it for themselves on demand.
We don't see much value add in building your own interface to the
native BV database.
Do It Yourself
Addressing the first bullet in your question in the event that you want
to do it yourself, you will need to understand that the BV dimensions
are contained in an object oriented data base, which makes for a
comlicated extract for do it yourself approaches. Things to consider
1. The BV database owner has to be comfortable with giving up
security on all dimensions in the database. You can ensure that
dimensions are not updated by providing only READ access, but you will
not be able to control READ access to only specific dimensions.
2. It will NOT be a simple, single SQL extract for each dimension.
You will need a development language that can execute both SQL and be
able to call a recursive function that conditionally builds and
executes the SQL statements needed in order to reassemble and extract
all the hierarchical relationships from the top of the hierarchy down.
3. It is an object oriented application held within a relational
database where nearly all the attributes of a dimension are contained
in one table.
4. The attributes are not held in recognizable column names, they
are properties with property ids with the values held in a separate
5. You will need to reverse engineer the Version Management
implementation, which is an Effective Dated concept.
6. The table structure could change in a future release,
invalidating your scripts / interface.
7. We know from our own internal staff development that this is a
steep learning curve, and IBM cannot effectively support that kind of
resource intensive knowledge transfer.
If you are really good at SQL, recursive function calls, and object
oriented programming styles, it can be done. You can start by
analyzing the RPTM_OBJECT and RPTM_PROPERTY tables.
It is a lot less effort to just use a subscription which can be built
in less than a day once you know what attributes you want to retrieve.
There is very little upside to doing it yourself.