rule doubling values in source

[mmckimson]

I am having an issue with a rule where a rule, while calculating data, is doubling the values of the source cube. In my rule, I've attempted to take data for 'Total Customers' to a data element called 'Existing Customers' in my target cube. The source cube looks as follows and contains the following feeders:

Dimensions
dimCostCenters
dimCustomers
dimProducts
dimTime
SalesCalc (calculates Sales and Units Sold )
Versions

['Total Customers','Sales'] =>
DB('Sales Summary',!dimCostCenterFinal,'Existing Customers',!dimProducts,!dimTime,!Versions,'Planned Sales');

['Total Customers','Units Sold']=>
DB('Sales Summary',!dimCostCenterFinal,'Existing Customers',!dimProducts,!dimTime,!Versions,'Planned Quantity');

The target cube looks as follows and contains the following calculations:

Dimensions
dimCostCenters
dimCustomerStatus (New, Existing, Total Customers)
dimProducts
dimTime
SalesSummary (summarizes sales and units, calculates price)
Versions

['Planned Sales'] = n:DB('Current Sales Calc',!dimCostCenterFinal,'Total Customers',!dimProducts,!dimTime,'Sales',!Versions);
['Planned Quantity'] = n:DB('Current Sales Calc',!dimCostCenterFinal,'Total Customers',!dimProducts,!dimTime,'Units Sold',!Versions);

It's almost as if the calculation is summing both the detail customer data and the aggregated Total Customer, but I can't figure out why. The only thing that I can think of is that each dimension (dimCustomers, dimCustomerStatus) both contain a summary element 'Total Customers'.

Any guidance that some more experienced developers can provide would be much appreciated!
Mike

[Paul Segal]

['Planned Sales'] = n:DB('Current Sales Calc',!dimCostCenterFinal,'Total Customers',!dimProducts,!dimTime,'Sales',!Versions);
['Planned Quantity'] = n:DB('Current Sales Calc',!dimCostCenterFinal,'Total Customers',!dimProducts,!dimTime,'Units Sold',!Versions);

It's almost as if the calculation is summing both the detail customer data and the aggregated Total Customer, but I can't figure out why. The only thing that I can think of is that each dimension (dimCustomers, dimCustomerStatus) both contain a summary element 'Total Customers'.
Mike

At a quick glance, should not the above rules point to 'Existing Customers' rather than 'Total Customers'? You don't show the dimension in full, but I assume that 'Total Customers' is a c element?

[rmackenzie]

You said that:

In my rule, I've attempted to take data for 'Total Customers' to a data element called 'Existing Customers' in my target cube.

But your rules don't reflect that:

['Planned Sales'] = n:DB('Current Sales Calc',!dimCostCenterFinal,'Total Customers',!dimProducts,!dimTime,'Sales',!Versions);
['Planned Quantity'] = n:DB('Current Sales Calc',!dimCostCenterFinal,'Total Customers',!dimProducts,!dimTime,'Units Sold',!Versions);

The rules have 'Total Customers' on the right-hand side, but not 'Existing Customers' on the left-hand side. You could try this:

Code: Select all

['Existing Customers', 'Planned Sales'] = n:DB('Current Sales Calc',!dimCostCenterFinal,'Total Customers',!dimProducts,!dimTime,'Sales',!Versions);
['Existing Customers', 'Planned Quantity'] = n:DB('Current Sales Calc',!dimCostCenterFinal,'Total Customers',!dimProducts,!dimTime,'Units Sold',!Versions);

It's almost as if the calculation is summing both the detail customer data and the aggregated Total Customer, but I can't figure out why.

Because you were loading e.g. the 'Planned Sales' measure for every element in the dimCustomerStatus dimension you may see a double (or triple, quadruple etc) up when you define a cube-view that includes a consolidation in this dimension.

Hope that helps,
Robin

[mmckimson]

You said that:

In my rule, I've attempted to take data for 'Total Customers' to a data element called 'Existing Customers' in my target cube.

But your rules don't reflect that:

['Planned Sales'] = n:DB('Current Sales Calc',!dimCostCenterFinal,'Total Customers',!dimProducts,!dimTime,'Sales',!Versions);
['Planned Quantity'] = n:DB('Current Sales Calc',!dimCostCenterFinal,'Total Customers',!dimProducts,!dimTime,'Units Sold',!Versions);

The rules have 'Total Customers' on the right-hand side, but not 'Existing Customers' on the left-hand side. You could try this:

Code: Select all

['Existing Customers', 'Planned Sales'] = n:DB('Current Sales Calc',!dimCostCenterFinal,'Total Customers',!dimProducts,!dimTime,'Sales',!Versions);
['Existing Customers', 'Planned Quantity'] = n:DB('Current Sales Calc',!dimCostCenterFinal,'Total Customers',!dimProducts,!dimTime,'Units Sold',!Versions);

It's almost as if the calculation is summing both the detail customer data and the aggregated Total Customer, but I can't figure out why.

Because you were loading e.g. the 'Planned Sales' measure for every element in the dimCustomerStatus dimension you may see a double (or triple, quadruple etc) up when you define a cube-view that includes a consolidation in this dimension.

Hope that helps,
Robin

I see your logic, however after making your suggested change I see the exact same results, i.e. values doubled.

Mike

[asutcliffe]

Hi Mike, have you tried debugging with the rules tracer?

http://publib.boulder.ibm.com/infocente ... F0007.html

When debugging rules and feeders, I always find it easier to start by examining a single leaf level cell.

[mattgoff]

try this:

Code: Select all

['Existing Customers', 'Planned Sales'] = n:DB('Current Sales Calc',!dimCostCenterFinal,'Total Customers',!dimProducts,!dimTime,'Sales',!Versions);
['Existing Customers', 'Planned Quantity'] = n:DB('Current Sales Calc',!dimCostCenterFinal,'Total Customers',!dimProducts,!dimTime,'Units Sold',!Versions);

It's almost as if the calculation is summing both the detail customer data and the aggregated Total Customer, but I can't figure out why.

Because you were loading e.g. the 'Planned Sales' measure for every element in the dimCustomerStatus dimension you may see a double (or triple, quadruple etc) up when you define a cube-view that includes a consolidation in this dimension.

Hope that helps,
Robin

I see your logic, however after making your suggested change I see the exact same results, i.e. values doubled.

Depending on your version of TM1 (and I don't recall which version does what), you need to zero out the cells where you deactivated the rule. Take a look at the New and confirm that the rule does not apply and values are zeroed.

Matt

[Steve Rowe]

Have you checked for double counting in another unrelated dimension in the cube. It is possible it is a dimension structure issue rather than a rule problem?

[mmckimson]

Have you checked for double counting in another unrelated dimension in the cube. It is possible it is a dimension structure issue rather than a rule problem?

A good question, although I don't think I have any other issues with data not aggregating properly. It's worth a look. Any other ideas appreciated!

[declanr]

by mmckimson » Tue Jun 26, 2012 5:55 pm

Steve Rowe wrote:
Have you checked for double counting in another unrelated dimension in the cube. It is possible it is a dimension structure issue rather than a rule problem?


A good question, although I don't think I have any other issues with data not aggregating properly. It's worth a look. Any other ideas appreciated!

To be honest I don;t think you need any other ideas.

Hi Mike, have you tried debugging with the rules tracer?

http://publib.boulder.ibm.com/infocente ... F0007.html

When debugging rules and feeders, I always find it easier to start by examining a single leaf level cell.

This says it all, I would be extremely surprised if an issue like this was caused by a bug since your rule looks pretty basic so someone else would have encountered it. The sad fact is that we reach a point where we have to acknowledge that we have done something incorrectly, normally something that turns out to be blatantly obvious when we get further into it.

Without seeing the numbers or understanding your cubes it would be hard for anyone to pinpoint this but as explained above, when you look at a lowest level element and trace back the value, you can see where every number that makes it up has come from... at that point you will know where the doubling up is occurring and can change the rule appropriately.

[Steve Rowe]

One more idea

Check that to see if you have a subset called "Total Customers" which may contain the total for this dimension and all the customers.

In excel at least if you refer to the subset name and this is different to any of the elements in the dimension then you would get the total of the elements in the subset.

Its not impossible for them to have added this to the rule functionality.

For me it is horried functionality in Excel, and would be even worse if it was in the rules too.

just a thought if you have exhausted all other options....

Cheers

[mmckimson]

by mmckimson » Tue Jun 26, 2012 5:55 pm

This says it all, I would be extremely surprised if an issue like this was caused by a bug since your rule looks pretty basic so someone else would have encountered it. The sad fact is that we reach a point where we have to acknowledge that we have done something incorrectly, normally something that turns out to be blatantly obvious when we get further into it.

Without seeing the numbers or understanding your cubes it would be hard for anyone to pinpoint this but as explained above, when you look at a lowest level element and trace back the value, you can see where every number that makes it up has come from... at that point you will know where the doubling up is occurring and can change the rule appropriately.

I totally agree with this statement actually... naturally I'm a relative TM1 novice and under a time crunch and of course start grasping at straws to solve the problem. I'll use the tracer to see if I can figure out why it does what it does and post back results.

Mike

[rmackenzie]

When debugging rules and feeders, I always find it easier to start by examining a single leaf level cell.

Not doing this caught me out a lot when I was first getting to grips with TM1 and is definitely sound advice.