Detail filters using DMR 10.2.1

[mrcool]

Hello All,

I strongly believe that traditional detail filters on the DMR report studio reports impact the performance. Could you please guide me in the right direction to create the prompt filters effectively. We have so many prompts and most of the prompts are not part of crosstab inorder to apply the prompt macro in the data item itself.
Our package has DQM enabled.
I would also like to know from the design point of view is it advisable to develop reports on DMR? Does it have any advantageous compared to relational modelling?
your inputs will be highly appreciated.

Thanks.
mc

[MDXpressor]

Your request is not a 12 month rolling window, but I go through an explanation of the difference between selecting an OLAP member versus filtering in OLAP in this post.

http://www.cognoise.com/index.php/topic,25165.0.html

Basically it boils down to this:  in SQL, a filter is a good thing, it reduces the number of records that the datasource has to return to Cognos for processing in the BI Layer.  In OLAP a filter is bad because you haven't told the cube which members of the hierarchy is important.  You've simply said 'I need something from this demisnion, but I don't know what.  I'll clean it up in the BI layer by filtering the results.'  This forces your cube to return every member of the hierarchy, not horrible in a small cube, but possibly devastating to an MDR model.

Focus your member call.  Filters in OLAP should be used for lightweight purposes.

[mrcool]

Thank you so much for your reply MDXpressor.
I tried to simplify the report to check the detail filter functionality and when a filter is applied on the attribute of a level, I could see that in the where clause on the SQL. I was surprised seeing that as my assumption was it would filter only in Cognos instead of database.
Is it normal in DMR?

Thanks,
mc

[MDXpressor]

Well, let me bow out of the conversation and defer to someone like Nimrod or CognosPaul, or MFGF, just anyone else besides me...  I don't want to steer you down the wrong path, and my experience with DMR has always been with CQM.  My experience was always so bad that I just abandoned the thought altogether. 

Most of the data I work with is stale financials, generally 2 to 4 weeks old, and at the month level of granularity.  I don't have any projects which connects with live data (and haven't had in 6+ years doing internal deployments at cognos, and 6+ years doing Independent consulting).  Which, to this point in my career has been the only reason I'd consider using DMR.  Is there another viable reason to use DMR?  Are you saving resources in the BI deployment presumably?  But what is it costing the business in terms of run-time performance?  What was the final bill on tuning your DMR?  Was it worth the savings?  Is your user base happy with your product?

I remember the days you could walk into a customer site with Transformer, build a cube off of the spreadsheets on their desktop, and show them points of the their business they'd never seen before.  And you could do it inside 90 minutes.  There is a power in that which is lost in DMR.  I've watched a lot of DMR projects fail in misery around me.  At the end of the day the same story always seemed to be true from my view on the sidelines: IT likes DMR because they can wash their hands of cube builds.  Business hates DMR because it's so slow compared to a normal cube.  IT dumps countless dollars into the black hole of DMR tuning, and blows the entire development budget trying to get it to work quickly.  It never does.  In the end all that money spent on tuning could have been used to develop a cube, and maintain it for 10 years.  Cubes are proven technology, the same cannot be said as easily for DMR. 

[navissar]

Well, let me bow out of the conversation and defer to someone like Nimrod or CognosPaul, or MFGF, just anyone else besides me...  I don't want to steer you down the wrong path, and my experience with DMR has always been with CQM.  My experience was always so bad that I just abandoned the thought altogether. 

Most of the data I work with is stale financials, generally 2 to 4 weeks old, and at the month level of granularity.  I don't have any projects which connects with live data (and haven't had in 6+ years doing internal deployments at cognos, and 6+ years doing Independent consulting).  Which, to this point in my career has been the only reason I'd consider using DMR.  Is there another viable reason to use DMR?  Are you saving resources in the BI deployment presumably?  But what is it costing the business in terms of run-time performance?  What was the final bill on tuning your DMR?  Was it worth the savings?  Is your user base happy with your product?

I remember the days you could walk into a customer site with Transformer, build a cube off of the spreadsheets on their desktop, and show them points of the their business they'd never seen before.  And you could do it inside 90 minutes.  There is a power in that which is lost in DMR.  I've watched a lot of DMR projects fail in misery around me.  At the end of the day the same story always seemed to be true from my view on the sidelines: IT likes DMR because they can wash their hands of cube builds.  Business hates DMR because it's so slow compared to a normal cube.  IT dumps countless dollars into the black hole of DMR tuning, and blows the entire development budget trying to get it to work quickly.  It never does.  In the end all that money spent on tuning could have been used to develop a cube, and maintain it for 10 years.  Cubes are proven technology, the same cannot be said as easily for DMR.

Ah, the old DMR discussion.
MDXpressor is right, of course. The chief advantage of DMR used to be its online capacity. When you needed an OLAP structure (If you wanted to let analysts use Analysis Studio, for example) and still be online, DMR would be the way to go. Nonetheless, even in the days of CQM this wasn't the only reason to use DMR.
When a real OLAP solution, such as MS-SSAS is available, there's almost no reason to use DMR but the one mentioned. But if your only OLAP alternative is Transformer, under some circumstances you're going to have a rough time. Transformer cubes have three main disadvantages compared with other OLAP solutions. The first is volume: Transformer cube have a hard time with big data sets. Over 2GBs, you'll have to partition, and partitioning in transformer is over files, which makes it slow to build and slow to perform. So, even when you can be satisfied with a cube update twice a day, if it takes two hours to build, it's worthless. Also, query times on large Tranformer cubes are far from satisfactory.
The second issue is security. Transformer cubes essentially multiply themselves for security groups, which is a terrible way of doing security and impacts build times.
The third issue is attributes. Transformer cube members are only allowed three attributes: Key, name, description. If you need more than that, you'll come up short.
DMR solves these problems even on CQM. It doesn't perform well on large data sets, but it makes up for it in not having any build times. It does security the same way it is done on relational, so it doesn't necessitate multiplying the data. And it allows infinite attributes.

But it has always been lacking in performance. DMRs could work and work well when they answer three rules: They're built over a well organized, well indexed, well modeled, properly aggregated DWH, they're simple and don't rely on relations which are too complex, and don't go over the top with data. Also, and that is very important, they only work well when the reports are built well - using dimensional concepts.
The way I would normally incorporate DMRs is this: Reports will be developed over the relational model, and analysts would get the DMR for their toying about needs with Analysis Studio. In this kind of deployment, DMRs can be quite useful when they're well constructed, and even speed up dev times.

...

Enter DQM. Because of DQM's hybrid query abilities and caching mechanism, a lot of the performance issues related to DMR are nowadays, history. Under some circumstances, especially when large volumes of data are involved, a DMR using DQM will perform better than a Transformer cube. One should remember that IBM's Dynamic Cubes are a form of DMR.
Having said that, DQM also works on pure relational. So, one needs to ask this: If it pre-developed, pre-made reports you're after, why not just use relational? Someone advocating the use of DMR should have a good answer for that (Such good answers can include: drill down/up capabilities needed, tree prompts needed, most reports require dimensional-like calculations (Comparison by year and so on)). So in DQM era, the answer of "Why use DMR" is basically dependent on the answer to "What are your needs".

One thing remains true though: DMRs are dimensional datasources and their queries should be such constructed. If you want to use detail filters because a simple experiment had shown you that under certain circumstances Cognos makes a where clause of it, go ahead - but know what you're doing. If you do not fully understand how DQM is creating each query and why over DMR, using detail filters will come back to bite you. I've been doing DMRs over DQM for a while now, and I'm still not sure how hybrid queries are created for DMRs - I know of some scenarios where one could use a detail filter and it will work, but more often than not this isn't the case.

So that's my take on the whole DMR issue. One last thing: When my customers try to pick an OLAP solution, I often send them to check back with their DB vendor regarding costs of their OLAP solution (Essbase for Oracle, SSAS for SQL Server and so on). An SSAS deployment with Tabular and MOLAP can cater for a lot of the OLAP needs quite well, with the advantage of being re-deploy-able for other front end tools. So take this under consideration as well - not everything has to be done Cognos side.

[mrcool]

Thank You so much for your inputs..They are very helpful.

Thanks,
mc

[cognostechie]

Bottomline - A Transformer cube is OLAP (MOLAP) whereas a DMR is OLAP over Relational.  This means that when you use a DMR, the data still resides in the database in a relational format whereas in a Transformer cube the data physically resides inside the cube in OLAP format. Hence, if you use a detail filter when using the cube then it's bad because there will be no SQL generated and hence the data retrieved cannot be a smaller data set at the time of retrieval. That's why MDX should be used so that only those members can be retrieved that are needed.

The scenario with DMR is different. The data is retrieved using a SQL statement and then it is converted from relational format to OLAP format by using MDX. That convertion take place when the user runs the report. Using a detail filter is not bad because when the data is retrived, it's the SQL that goes to the DB, not MDX . Using MDX against a DMR is a luxury as it allows you to use
some powerful MDX functions for which no SQL  alternatives are available.

That being said, I have a different opinion on certain points mentioned above. The purpose of a cube is to do analytics, not
relational/operational reporting. Transformer allows four attributes (short name, long name, member description and category code and these can be used in Report Studio) and needing more than that means you are using it for reporting, not analytics . DMRs ability to have more attributes means that you are using it for reporting so comparing it to a cube makes very little sense. Another point is that DMR is only 'online' when it is connected to the source system. In a proper BI environment, you
would have a proper DW which is refreshed during the night so for the entire day, there is no difference between the DMR and the cube in terms of latency of data. In some industries, the data needs to be more volatile and the DW is refreshed multiple times in a day but in that case, you can refresh the cube at the same time too because the cube can be built by reading data from the source system instead of reading it from the DW. Technically that is possible. So you have to architect the solution depending on your needs. Having said that, again, a cube should be used for analytics, not reporting and for that refreshing the data multiple times in a day doesn't really make sense. Ask any CEO or senior management and they don't really care what happened in the business two hours ago as it doesn't add any usefulness to decision making because the percentages/ratios hardly change because of that. I have had Transformer cubes in excess of 2 GB split in multiple files and didn't have a problem when the users were drilling down so 'big data' is a relative definition. Ofcourse , if the cube is 50 GB and the performance is slow then the same would apply to DMR because in that case, the DMR would also fetch billions of rows while fetching the data. It would also warrant a question on whether the cubes and the DW have been architected properly or not. Looking at the needs of using a cube (analytics) , putting 50 GB in a cube means that either the tables used from the DW are detailed tables or the developer/architect simply doesn't know the ability of using one cube over another so he/she tried to put the data in one cube instead of a series of cubes and connecting them. It might also mean that the cube is used for reporting or as an alternate solution to using a relational package because they could not resolve the performance issues of the relational package. I have seen people taking that approach. I have also seen the business trying to use the cube as a means to extract the data into EXCEL .

DMR does have s solid advantage over Transformer cubes though - It allows you to filter on any attribute even if that attribute is not part of any hierarchy. This is a big advantage in many industries.

[navissar]

That's an interesting discussion. There's more than one way to skin a cat. Cognostechie's remarks are good and they make sense, nonetheless I view things differently, based on my own experience. This isn't a very technical discussion, it's more of a philosophical one. What I'm going to write here is my own personal philosophy, and isn't meant to be argumentative, but rather it's meant to show another perspective.
Now, having said that...

First, I reject the distinction between Analysis and Reporting. That distinction started to blur a few years ago, and in most current days implementations I'm involved with, reports are dynamic enough to be considered analytic and analysis is basis for reports. When a report boasts one or more of dynamic categories, SPSS predictive analytics, TM1 planning, scatterplots with member aggregation and so on, it's well past traditional reporting. And that's without saying a word about the trend of the last few years, the one that basically dictated the last Gartner - Business Discovery, which is almost to the letter a combination of analytics and reporting.
Second, I dispute that a cube shouldn't be used for reporting. Having data aggregated on all levels in all dimensions can speed up reporting. Assuming each reports looks at different directions in different grain levels, the only way even a well structured DWH can compare to a strong cube is by having an aggregated fact for each report, which isn't practical. This becomes more and more true as data grows in volumes. Which brings me to my next point:
Third, the notion that a business day's worth of data is immaterial is true for many industries, but untrue for many others. Forex and gaming come to mind, but also online media, social media and so on. When currency exchange rates change every second dramatically, two hours' worth of data can be millions of dollars.
Also, there's operational reports. I once built a dashboard system for a road services company. They needed operational data for one of their managers - he needed to know how many vehicles were out and how many people are waiting to be served, and where, and he needed to see the picture NOW, to decide whether or not to have a subcontractor, which costs a small fortune, take on some cases. He needed the data to be updated whenever he refreshed his page. I set up a system of fast refreshing materialized views, that sped things up. Couldn't do that in OLAP.
I had a project for a newspaper where the analysts needed online sales data that they can toy around with because advertisement sales affect the number of pages of the paper which will be printed tonight, and the number of pages affects the price of different types of ads and so on. You need analysis, and you can't use OLAP.
I also dispute, from experience, that a 50GB cube means too much data or a cube badly planned. I have an operating SSAS cube that's over half a terabyte. It's aggregative, it just happens to hold data about online social media interactions. An average query returns in less than a second. The cube is built twice a day, half an hour each build. Transformer cannot do that, because Transformer isn't that kind of OLAP solution.

One last thing regrading parallel cube and DWH ETL: While it is possible, it puts a strain on the operational system. One may want to avoid that.

Just my two cents. Take it for what it is.

[cognostechie]

The benefit of these discussions is to share ideas and I am thankful to Nimrod and everyone else because it increases
my knowledge not only of the technology but also about what are the different scenarios others have encountered and
what are the requirements of other industries. It would definately help me in my future business also which is making
an out-of-the-box BI solution which will be sold as a canned product with source systems.

I agree about the mixture of analytics and reporting but from the examples mentioned, I would again mention my earlier point
that the technology allows spreading the data in multiple cubes which can then be connected in the report. Drilling from one cube to another is allowed so I would not like to build one cube which can have all of the data. I worked for the world's biggest chip manufacturer and they were refreshing the cubes multiple times in a day. The refresh time was about 30 minutes but the cubes had 4 yrs of data and none of the cubes were 50 GB though all the cubes together were more than 50 GB (equivalent of that terabyte data in SSAS). One of the cubes was holding only the current month's data but the business was able to see
all 4 yrs data because cube to cube communication was enabled in the reports/dashboards. If the business wants to see
last 2 hrs data then yes, you can refresh only that cube that holds the current months data because the past data has not changed so there is no need to refresh other cubes. This method will allow you to do what that newspaper company wanted
too.

When I try to think that whatever we need to put into one report  should come from one cube then my perception about Transformer limitations would be the same as yours. That's when the problem will appear. It would not be fair to qualify one tool and disqualify the other simply because we did not use the tool to it's optimum capacity.

That being said, Transformer would still have some limitations and so would SSAS and DMR and that's exactly why there are different tools to accomplish different results so I would not advocate any single tool here.

[Lynn]

What a great discussion! This is a fine example of what makes this forum so valuable. Thank you very much to MDXpressor, Nimrod, and cognostechie  for taking the time to share your expertise!

[raj_aries81]

Wow....awesome....that was really informative..Thanks Nimrod and Cognos Techie :)