TM1 Forum

Hi,
I have created quite simple report "Quantity sold" measure in Rows with "Buyer's Country" and "Product ID" in Columns.

Report created in Cognos BI Report Studio accessing data from TM1 cube. Cube is quite sparse and result is for sure result of sparse data. The query in Report Studio is executing 20 minutes with killing report after this time (Cognos BI hard limit in our environment). From Windows Task Manager I see tm1sd.exe process occupies 100% of CPU core all the 20 minutes time.

If from command prompt I manually execute query directly to the warehouse database (from where TM1 cube data is imported during the night) the SQL query runs for less then one! second.

Are there any basic steps (recommendations) I should check in TM1 to see if I can dramatically speed up this query execution? What are basic steps to speed-up sparse reports?

See attachment for details.
Thanks.

Environment:
- Cognos BI Server 64-bit 10.2.1 fixpack 6 on Windows 2008 R2
- TM1 server 64-bit 10.1.1 fixpack 2 on Windows 2008 R2
- TM1 cube is fresh imported from warehouse during nightly import and during the day there are only read accesses by end-users, no write accesses. This cube is only used for reporting.

First of all I see that your Buyer dim has 18 thousand elements in the data warehouse, but a whopping 1.1 million in TM1. Why the difference? Are you sure you're comparing things that can be compared? Anyway - a few quick questions that pop to mind are:
How long does a corresponding view in TM1 Cube Viewer take to generate?
Does the cube have any rules? If yes, are they fed properly?
Is the FM package published as DQM or CQM?
Are you using server-side zero suppression?

BTW, thanks for taking the time to provide a lot of detail to go with your question. It's a pleasure to see questions like that.

qml wrote:First of all I see that your Buyer dim has 18 thousand elements in the data warehouse, but a whopping 1.1 million in TM1. Why the difference? Are you sure you're comparing things that can be compared? Anyway - a few quick questions that pop to mind are:

Good spotted. There are the same data, but maybe little bit differently modeled. Buyer dim is also populated into TM1 in combination with fact table. In TM1 Buyer dim has the following hierarchically (TM1 levels): top_single_total_buyer_member --> continent --> country --> buyers_name_with_id --> buyer's order number --> buyer's invoice number. Bottom two (order/invoice) are pulled from warehouse fact table into last two levels in Buyer's hierarchy.

qml wrote:How long does a corresponding view in TM1 Cube Viewer take to generate?

I have seen this tips in many web pages on net and I am banging my had with this TM1 views. I can't find out any good source with step by step instruction on net how to create them. I would really really appreciate some more details how to create this views dynamically from Turbo Integrator process. Specially how to create such a view to include only level_Country from Buyer's dim. And how to fire a view refresh. Now I don't have any view on this cube and reading the web I have a feeling this may be key to performance improvements, caching data.

qml wrote:Does the cube have any rules? If yes, are they fed properly?

Cube has some rules, but this particular report does not uses any measure that is calculated in rules. In my understanding this should not get influence on report performance. Just for test I have deleted the rules from cube and performance problem is the same.

qml wrote:Is the FM package published as DQM or CQM?

DQM.

qml wrote:Are you using server-side zero suppression?

I don't know what "server-side zero suppression" is. I turned suppression on in Cognos BI Report Studio by changing default crosstab template to suppress zero rows (exact setting: Data | Suppress | Suppress Rows Only), so each new report already has this suppression setting. This suppression setting adds additional "NON EMPTY" words into MDX (I double checked turning this setting on/off).

Ok, thanks for the answers. We're getting somewhere here.

So, first of all, TM1 is not the right tool to store transaction-level data, which is what you are doing when you go down to the invoice level. Your data granularity in TM1 should probably not go beyond Buyer. I'm not saying it's impossible to have orders and invoices in TM1, but it's just not the right way to do it. Instead, you should have a drill through to your data warehouse defined so that users running reports can get those invoice and order details when they need to.

With the current design your cube is beyond sparse. It has more cells than there are bacterial cells on the planet, but you're only throwing 400,000 facts in it. It's multidimensional homeopathy. TM1 can handle sparsity very well, but certain things like dimensions of >1M elements will be a problem.

As to checking the view in Cube Viewer, I was thinking of creating a view manually to see if it takes long to run. This would allow you to remove Cognos from the equation and check your performance in pure TM1. If a view with 135 rows takes more than a few seconds to return results then your model is poorly designed. Any caching etc would only be covering the problem, not solving it. Also, you can create a cube view using TI, but it shouldn't be necessary, you should be able to create a crude drag-and-drop view corresponding to your BI report in a few minutes.

As to the rules, it doesn't even matter if you're pulling any rule-calculated values into your report. You need to have SKPICHECK and FEEDERS in a cube of this size, otherwise it will never work. Any rule added to the cube switches off its sparse consolidation algorithm, making it much slower. In your case, if you really do not use SKIPCHECK it's probably about 27 orders of magnitude slower (no kidding).

Seems to me like you need to get someone who knows how to design TM1 solutions to provide some onsite help. Otherwise you are likely to make some serious mistakes.

qml wrote:Instead, you should have a drill through to your data warehouse defined so that users running reports can get those invoice and order details when they need to.

I was already thinking on reducing the size of cube removing order/invoice from it, but I have to get this info back somehow, drill-through is probably one of good candidate.

qml wrote:TM1 can handle sparsity very well, but certain things like dimensions of >1M elements will be a problem.

That is exactly what I would like to find out, where are the limits of TM1 and where some other solution is more appropriate.

qml wrote:As to checking the view in Cube Viewer, I was thinking of creating a view manually to see if it takes long to run. This would allow you to remove Cognos from the equation and check your performance in pure TM1. If a view with 135 rows takes more than a few seconds to return results then your model is poorly designed. Any caching etc would only be covering the problem, not solving it. Also, you can create a cube view using TI, but it shouldn't be necessary, you should be able to create a crude drag-and-drop view corresponding to your BI report in a few minutes.

I did the following:
I. Create dynamic subset for Buyer Country level
1. Right click on Buyer dim | Insert new Subset.
2. Subset Editor opens. Menu: Tools | Record Expression.
3. All button to make sure all members are displayed.
4. Click Filter by Level and select level 3 (TM1 starts counting from 0 to n from bottom-up).
5. Tools | Stop Recording and answer Yes.
6. Subset | Save, named the report "buyer_country", unchecked the Private button (make sure Save Expression is checked).
7. Checked the created MDX with View | Expression:
{TM1FILTERBYLEVEL( {TM1SUBSETALL( [PROD_MARZE_KUPEC] )}, 3)}

II. Create dynamic subset for ProductID level
The same steps as in I. Subset name: productID
Created MDX is:
{TM1FILTERBYLEVEL( {TM1SUBSETALL( [PROD_MARZE_IZDELEK] )}, 0)}

III. Create view
1. Right clicked and selected Browse to open Cube Viewer.
2. In Rows I click on my measure dimension and selected my measure FAKT KOLIČINA (English: Quantity sold).
3. In Columns I drag buyer and clicked on it to open Subset Editor. Menu Subset | Open and selected "buyer_country" subset.
4. Like step 3 for ProductID.
5. From time dimension I have selected "APRIL 2015" and from organization unit dimension I have selected "031".
6. In Cube Viewer selected Options | Suppress Zeros.
7. File | Save a report name it: "country_by_product_view" (unchecked Private at saving dialog).
8. Clicked on Recalculate button. Building Views dialog opens, but after exactly 5.3 seconds I got results in Cube Viewer.

I checked the data and it looks like the data are exactly what I need.

IV. Launched Cognos BI Report Studio and executed report from my first post in this thread. The created TM1 view and Cognos BI report looks the same(see attachment). But report in Cognos BI takes for ever. After 10 minutes I have killed the report. During execution I have opened Windows Task Manger on Windows server and I see tm1sd.exe is occupying 100% of one CPU for whole time. Don't know why, but it looks like this TM1 view is not excepted.

qml wrote:As to the rules, it doesn't even matter if you're pulling any rule-calculated values into your report. You need to have SKPICHECK and FEEDERS in a cube of this size, otherwise it will never work. Any rule added to the cube switches off its sparse consolidation algorithm, making it much slower. In your case, if you really do not use SKIPCHECK it's probably about 27 orders of magnitude slower (no kidding).

I have got SKIPCHECK and also the FEEDERS in the rules. But for above tests I have just deleted rules to make sure rules are not a problem. So tests are without rules.

When you do zero suppression only in BI, TM1 has to return every single cell to BI so that BI can then decide what to show or not. With a cube of your size this will never work. Heck, it won't even work for a cube 100th of your size. You have to have TM1 doing the zero suppression, skipping the empty cells and only returning the valid ones to BI. I'm not a Bi expert but I believe you need to do this in your Framework Manager package, by basing it on a TM1 view that has been set for zero suppression. Do a search on this forum for this topic. I know I've seen it posted before.

Hi,

Most probably you already checked that but have you tried creating a report in BI using a smaller-size-cube and see if it works in order to make sure that integration between TM1 and BI works fine?

tomok wrote:When you do zero suppression only in BI, TM1 has to return every single cell to BI so that BI can then decide what to show or not.

This used to be the case, but not anymore. If using BI 10.2.1 or newer with TM1 10.1.1 or newer and DQM (all boxes ticked in this case) then the default mode is zero suppression done on the TM1 side, regardless of whether you switch it on or off in the report. You can change this behaviour, but it requires a bit of work (keyword: IsUnderFed).

If your Cube Viewer view is quite quick to run then there is something else going on. I would look at TM1Top to see what is going on in the server and crank up server message logging by adding the following entries in tm1s-log.properties:

log4j.logger.TM1.MdxViewCreate=DEBUG
log4j.logger.TM1.API=DEBUG

Careful, this will create a big log file, so only activate these settings for the BI report run.

What you're looking for in these logs is the MDX passed to TM1 and view calculation times and also any other communication passing through the API (e.g. dimension structures).

And to go back to basic principals - how closely located are your BI and TM1 boxes?
It still uses an awfully chatty client to communicate so if they are further apart than the opposite ends of a coin you can start seeing serious impacts.

I know it's not so much of an issue with DQM, but I believe there is still some benefit to setting the useprovidercrossjointhreshold parameter in the qfs_config.xml file in BI. Might be worth playing around with, but it does not look like your doing too many cross joins.

Another thing to consider is that some versions of TM1 10.1.1 had a bug with parallel interaction which caused the cache to break if ParallelInteraction was switched on. It might be worth switching it off to see if it makes a difference.

Interestingly enough, IBM seems to have addressed this poor BI - TM1 link with some new parameters in TM1 10.2.2:

CognosMDX.AggregateByAncestorRef
When possible, replaces aggregation over a member set with a reference to an ancestor, if the aggregated member set comprises a complete set of descendants and all members have the weight 1.
CognosMDX.CellCacheEnable
Allows the IBM Cognos MDX engine to modify TM1 consolidation and calculation cell cache strategies.
CognosMDX.PrefilterWithPXJ
Expands the data source provider cross join approach to nested filtered sets.
CognosMDX.SimpleCellsUseOPTSDK
Applies IBM Cognos MDX engine consolidation and calculation cell cache strategies to all cells in query results.
CognosMDX.UseProviderCrossJoinThreshold
Applies the data source provider cross join strategy, even if it is not explicitly enabled in IBM Cognos BI.

qml wrote:You can change this behaviour, but it requires a bit of work (keyword: IsUnderFed).

Hi,
Excellent. That's it!!! I have got one problem with one of my cube having complex rules and I just though it was a bug, so reported a PMR at IBM and got reply if you have some problems with Cognos BI and feeders then there is a magic to add IsUnderFed Cube Attribute and I added it and problem disappeared - this was one of the problem when we migrated from Compatible Query Mode (problem not appearing) to Dynamic Query Mode (problem appeared) package at Cognos BI. That attribute was added for very small cube and there was no performance penalties with setting this attribute. I never thought this setting can dramatically impact the performance of large cube.

Performance test:
1. Removed IsUnderFed cube attribute.
2. Restarted TM1 server. (to make sure there are no caches)
3. Restarted Cognos BI server. (to make sure there are no caches).
4. Run report (without generating any views etc at TM1) and it is executed in 11 seconds.
Monitoring with tm1top.exe and Windows Task Manager and I think there isn't anything really to improve at TM1 site. There were plenty of "Cognos BI" processes listed in Windows Task Manager during execution, but tm1sd.exe was just for a second or so... I couldn't believe this is a solution, so I added parameter restarted servers and the problem is back, remove IsUnderFed attribute, restarted servers and problem disappeared. So this attribute is the cause of the problem.


But I really don't remember why I (or one of my colleague) have added this attribute to this large cube. I tested the cube and it looks like it is working fine without IsUnderFed attribute. Maybe there is some trick that I don't spot right now.

Bellow are the rules at this cube. Are feeders correctly written?

SKIPCHECK;
# Illogical values at member combinations that has to be set to 0 value.
['dimension_status','Measure_1']=0;
['dimension_status','Measure_2']=0;
['dimension_status','Measure_3']=0;
['dimension_status','Measure_4']=0;
['dimension_status','Measure_5']=0;
['dimension_status','Measure_6']=0;


['Measure_1']=['Measure_2']\['Measure_7']*1000;
['Measure_8']=['Measure_9']\['Measure_7']*1000;
['Measure_3']=['Measure_7']*(['Measure_1']+['Measure_8'])\1000;
['Measure_4']=['Measure_5']\['Measure_10']*1000;
['Measure_11']=['Measure_12']\['Measure_10']*1000;
['Measure_6']=['Measure_10']*(['Measure_4']+['Measure_11'])\1000;
['Measure_11']=['Measure_13']\['Measure_10']*1000;

FEEDERS;
['Measure_7']=>['Measure_1'];
['Measure_7']=>['Measure_8'];
['Measure_7']=>['Measure_3'];
['Measure_10']=>['Measure_4'];
['Measure_10']=>['Measure_11'];
['Measure_10']=>['Measure_6'];
['Measure_10']=>['Measure_11'];


P.S. I am sorry for late reply, I was on vacation.
Regards

In your case it looks like it would be better to not have the feeder statements but instead use natural consolidation as a way of feeding the ratio measures so that they don't disappear from zero-suppressed views. This approach has been outlined in quite a few threads e.g. in this one.