Wednesday, March 7, 2012

Error using UnaryOperatorColumn on a Many-to-Many linked dimension

Hi,
i made a small debugging environment for a problem i am getting, it has 2 fact and 2 dimension tables:

FactAccount - fact - (AccountKey,Balance)
DimAccount - dimension - (AccountKey,Code,Name)
BridgeAccountReport - fact - (AccountKey,ReportKey)
DimReport - dimension - (ReportKey,ParentReportKey,Code,Name,UnaryOperator)

the dimension usage is as follows:

for BridgeAccountReport
DimAccount - Regular
DimReport - Regular

for FactAccount
DimAccount - Regular
DimReport - Many-to-Many using BridgeAccountReport as intermediate fact table

DimReport has a self-referencing hierarchy using ParentReportKey.

The idea is to be able to use the same facts recorded at the DimAccount level under different analysis structures recorded in DimReport.

The problem i am getting is that the UnaryOperatorColumn is only having an effect on the inmediate superior level,
but the result on that level is not afterwards used to calculate the aggregations on upper levels.

Here is a sample result:

Net profit 53 (NO typing error!!)
Profit 6
Income 28
Expenses 22 (unary operator set to '-')
Taxes 3

Profit, which is correctly calculated based on unary operator as 6 is not the used as it is to calculate Net Profit which ends up with a value of 53.
Instead the full values of the lower levels seem to be used to calculate it.

To contrast these results i made a similar example, but this time with only:

FactAccount (AccountKey,Balance)
DimAccount (AccountKey,ParentAccountKey,Code,Name,UnaryOperatorColumn)

and i get:

Net profit 9
Profit 6
Income 28
Expenses 22 (unary operator set to '-')
Taxes 3

Correct results.

So, the difference must be somehow related to the indirect relation over the many-to-many link.

Am i forgetting something? Is it a bug in analysis services?

thanks for any hint or comment, af.Hi,
I have encountered the same problem. On the leaf level the unary operator is considered, but on higher level it is ignored.

Have you found any solution or workaround?

Any hint would be helpful ...thx.|||Hi, so i am not crazy :-)
Yours is the first answer i got both from this forum and from the Connect bug report which you can visit here:
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=200152

I was also planning to describe this more in detail in a forum dedicated to many-to-many relations but i couldnt find the time so far. The forum is in:
http://www.sqlbi.eu/Forum/tabid/72/forumid/17/scope/threads/Default.aspx

On that site there is also an excellent paper on many-to-many modelling.

Let me know if you find some answer to this problem.

af.

No comments:

Post a Comment