Performance in Dynamic Query Mode for a Relational FM Model in C10.2.1FP3

[deadsea]

Hello All:

Are there any performance improvements when utilizing DQM for a purely relational model (non DMR) in C10.2.1 FP3? Has anyone implemented or migrated a relational model in DQM and found significant performance improvement?

I have searched a lot and was not able to find any suitable thread that provided any feedback on this topic so figured i will post it here. Most resources online state that DQM will provide significant performance improvement with DMR and via Dynamic Cubes, however, when it comes to relational model with DQM it just states that it will work (except that it is not as lenient as CQM when violating modelling best practices). When talking with IBM, they dont have any benchmark numbers (like they do for DMR and Dynamic Cubes over DQM) either.

Thanks all.

[MFGF]

Hello All:

Are there any performance improvements when utilizing DQM for a purely relational model (non DMR) in C10.2.1 FP3? Has anyone implemented or migrated a relational model in DQM and found significant performance improvement?

I have searched a lot and was not able to find any suitable thread that provided any feedback on this topic so figured i will post it here. Most resources online state that DQM will provide significant performance improvement with DMR and via Dynamic Cubes, however, when it comes to relational model with DQM it just states that it will work (except that it is not as lenient as CQM when violating modelling best practices). When talking with IBM, they dont have any benchmark numbers (like they do for DMR and Dynamic Cubes over DQM) either.

Thanks all.

I don't have any hard and fast info, sadly, but my feeling is that the improved query planning and generation of DQM, along with the improved caching across the entire user population, ought to make a difference to performance (to some degree).

Cheers!

MF.

[cognostechie]

I agree. Considering the fact that DMR is not an independant solution but rather gets the data from the relational layer, if
the performance is supposed to be better for DMR (when using DQM) then it should be better for relational layer too.

[deadsea]

Thanks folks. I was trying to quantify my reasoning to move to DQM, but nevertheless I am testing it out now and will update my findings.

Off topic: is there any solution to continue use our 1..N <-> 1..N or 0..N <-> 0..N in DQM? I know I can fix it in the model, however, at this point that is not a alternative due to the amount of effort involved in testing 100's report. Suffix to say I did not create this model and its a utter disaster :(

[MFGF]

Thanks folks. I was trying to quantify my reasoning to move to DQM, but nevertheless I am testing it out now and will update my findings.

Off topic: is there any solution to continue use our 1..N <-> 1..N or 0..N <-> 0..N in DQM? I know I can fix it in the model, however, at this point that is not a alternative due to the amount of effort involved in testing 100's report. Suffix to say I did not create this model and its a utter disaster :(

1..n <--> 1..n cardinality is a complete disaster right there in it's own right. It's a many-to-many relationship, and is a big no-no in any kind of reporting paradigm. Think of what it implies:

eg Products has a many-to-many relationship with Suppliers (ie a product can come from many suppliers and a supplier can supply many products)

In this scenario, if you have an order for a product, how can you report on who supplied the product? The product on the order can have many suppliers. It's a chasm trap, and it has to be resolved one way or another.

Generally it indicates that information is missing from the model. Somewhere, the business records which suppliers provide which products for which orders - how could it function otherwise? It's usually a case of bringing this information into the model and using it to resolve the many-to-many. Perhaps there's another table called supplier-product that resolves it? In this case it would fit between the product and the supplier tables in the model, and each would join to the supplier-product table 1..1 <--> 1..n

Sorry if you know this already. The short answer is no - DQM has no inbuilt magic for chasm traps.

Cheers!

MF.

[deadsea]

As an DM and ETL developer, I could not agree more with you. However, we/client are working under certain constraints, so I wanted to check if there was a way to overwrite the XQE grammer/rules via any of config files.

At this point, I will just still to 32 bit mode with CQ and DQ (but was hoping to leverage 64 bit since I am implementing Dynamic Cubes with 250 million + fact data)

Thanks for your response and have a great weekend.