July 6, 2007 at 9:48 am
Hello
We are on AS2000, and I'm facing a strange problem...may be someone can help me.
When we analyse the stock cube, for all products we have a value. For example it's X. But when we drill down the dimension to the last level we have x-y. So some values are not shown when we drill down. The x value it's the correct one. The information in the fact table it's the same of X.
The mdx query is this one:
WITH MEMBER [Sku].[ Grand Total] AS 'SUM( INTERSECT( { { EXTRACT( { [Sku].[Brand].&[150].&[].&[].CHILDREN }, [Sku] ) } }, { [Sku].[Brand].&[15].&[].&[].CHILDREN } ) )', SOLVE_ORDER = 1000 SELECT { [Measures].[Stock Cost] } ON COLUMNS ,
NON EMPTY { { [Sku].[Brand].&[150].&[].&[].CHILDREN }, ( [Sku].[ Grand Total] ) } ON ROWS
FROM [Stocks]
WHERE ( [Company].[Company].&[1], [Store].[BU].[Store].&[48900], [Time].[All].[Day].&[20061231], [Retail].[All Retail] )
Thanks.
July 20, 2007 at 8:03 am
Hello
Anybody have a clue on this?
Thanks
July 20, 2007 at 2:21 pm
If you can clarify your question I think we may be able to help you more easily. How about some sample data as well?
July 26, 2007 at 7:36 am
Hello
I 'm going to give an example.
For year 2006, for warehouse X I have USD 200.000 grand total of stock. And this is the correct value. It's the value of the grand total of the fact table.
Then I choose to see in the dimension product, which are the products that are in stock, doing a selection of the items in the next level of the dimension.
Then I see which are the products that are in stock, but, the grand total now is diferrent, it's USD 195.000. And this is wrong. 5000 USD are missing in the grand total I don't know why.
Thank you for your help.
July 26, 2007 at 9:07 am
It's really hard to troubleshoot MDX code without having the database handy to query, but I think your problem lies in the calulated member in you MDX query:
WITH MEMBER [Sku].[ Grand Total]
AS
'SUM(
INTERSECT(
{
{
EXTRACT(
{ [Sku].[Brand].&[150].&[].&[].CHILDREN }
, [Sku]
)
}
}
,
{ [Sku].[Brand].&[15].&[].&[].CHILDREN }
)
)'
, SOLVE_ORDER = 1000
I believe the Intersect function may be causing the problem. Try adding the ALL keyword as follows:
WITH MEMBER [Sku].[ Grand Total]
AS
'SUM(
INTERSECT(
{
{
EXTRACT(
{ [Sku].[Brand].&[150].&[].&[].CHILDREN }
, [Sku]
)
}
}
,
{ [Sku].[Brand].&[15].&[].&[].CHILDREN }
, ALL)
)'
, SOLVE_ORDER = 1000
That's one idea... the other issue could be the use to the Extract function... Descendants may work better here. You'll have to try playing with the MDX to figure this one out.
I hope that helps a bit.
David
July 26, 2007 at 1:01 pm
I looking at your MDX query a little more, it looks like you must have a product with the same name in the [Sku].[Brand].&[150] and [Sku].[Brand].&[15] rollups. Because the "ALL" parameter isn't there, it is filtering out the second instance of that name, thus the lower total.
I'm pretty certain now that adding the ALL parameter as described above will work.
Let me know how it goes.
David
July 30, 2007 at 11:17 am
Hello David
I add the all parameter, but didn't work. The result are the same.
But I found one thing. The value 150 was wrong, it's 15, not 150. But still doesn't return the correct value.
Any other ideia? I cant figure out nothing more...
Thank you.
July 30, 2007 at 11:38 am
Ok... I'm not sure why the calculated member is setup that way then. What is the calculation trying to do? Is it just trying to show the grand total for all of the children of Sku.Brand.15?
August 1, 2007 at 8:48 am
Hello
Yes, that's it.
Thank you.
August 1, 2007 at 9:02 am
All you have to do then is this:
WITH MEMBER [Sku].[ Grand Total] AS '([Sku].[Brand].&[15], [Measures].[Stock Cost])', SOLVE_ORDER = 1000
SELECT { [Measures].[Stock Cost] } ON COLUMNS ,
NON EMPTY { { [Sku].[Brand].&[15].&[].&[].CHILDREN }, ( [Sku].[ Grand Total] ) } ON ROWS
FROM [Stocks]
WHERE ( [Company].[Company].&[1], [Store].[BU].[Store].&[48900], [Time].[All].[Day].&[20061231], [Retail].[All Retail] )
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply