B
Bluetooth
Hi,
Looks like we've nailed a nasty bug in OWC (both version 10 an 11). Things
happen when we have a filter (page) based on a dimension with custom rollup.
Here is the scenario to reproduce (based on Foodmart 2000 database):
STEP 1. We need a custom rollup dimension, so CATEGORY dimension can be
slightly adapted for this purpose. Here is a script that will do the trick:
set oConn = CreateObject("ADODB.Connection")
oConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
"c:\Program Files\Microsoft Analysis
Services\Samples\foodmart 2000.mdb"
oConn.Open
oConn.Execute "update `C:\Program Files\Microsoft Analysis
Services\Samples\foodmart 2000`.`category` set category_rollup = '~'"
oConn.Execute "update `C:\Program Files\Microsoft Analysis
Services\Samples\foodmart 2000`.`category` set category_rollup='+' where
category_id='ADJUSTMENT'"
msgbox "Done"
STEP 2. Analysis manager, CATEGORY dimension, level "Category Description",
"Unary Operator" property should be set to "category_rollup"
STEP 3. Process the Foodmart 2000 database (or just the Budget cube, we will
use for our demonstration)
STEP 4. Connect to the Budget cube wit OWC. Below is a sample HTML code that
will do the trick:
<html><body>
<OBJECT class=ptexec classid="clsid:0002E552-0000-0000-C000-000000000046"
height=251
id=pt style="HEIGHT: 251px; LEFT: 0px; TOP: 0px; WIDTH: 502px" width=502>
<PARAM NAME="ConnectionString" VALUE='Data Source=localhost;Initial
Catalog="Foodmart 2000";Provider=MSOLAP.2;Client Cache Size=25;Log
File=C:\MDXOWC.Log"'>
<PARAM NAME="DataMember" VALUE="BUDGET">
</body></html>
STEP 5. Making a report:
a) drag AMOUNT measure to data area on OWC
b) drag CATEGORY dimension to a page/filter area and check only the "Current
Year's actuals"
c) drag STORE dimension to rows
d) drag TIME dimension to columns
You should have three rows of data (Canada, Mexico and USA) and two columns
(1997,1998). Values for (Canada,1997) and (Mexico,1997) are zero, the rest
are non-zero.
STEP 6 (WHERE WE ACTUALLY HIT THE BUG)
Drag the Time dimension from columns to rows, placing it between first and
second column). The result is a nested Store Country / Year report
BUT!!!!!!!!!!
We have now only one row - USA. Canada and Mexico are gone, as are the
years.
What is more interesting, the same report exported from OWC to Microsoft
Exel works fine (shows correctly all relevant rows and numbers).
My question is if this is a known behaviour / bug and if there is any
workaround or pending fix. Matter is rather serious, since our customers are
getting wrong numbers out of their OLAP/BI systems.
Best Regards
Szymon Slupik, CTO
CDN S.A., Krakow, Poland
Looks like we've nailed a nasty bug in OWC (both version 10 an 11). Things
happen when we have a filter (page) based on a dimension with custom rollup.
Here is the scenario to reproduce (based on Foodmart 2000 database):
STEP 1. We need a custom rollup dimension, so CATEGORY dimension can be
slightly adapted for this purpose. Here is a script that will do the trick:
set oConn = CreateObject("ADODB.Connection")
oConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
"c:\Program Files\Microsoft Analysis
Services\Samples\foodmart 2000.mdb"
oConn.Open
oConn.Execute "update `C:\Program Files\Microsoft Analysis
Services\Samples\foodmart 2000`.`category` set category_rollup = '~'"
oConn.Execute "update `C:\Program Files\Microsoft Analysis
Services\Samples\foodmart 2000`.`category` set category_rollup='+' where
category_id='ADJUSTMENT'"
msgbox "Done"
STEP 2. Analysis manager, CATEGORY dimension, level "Category Description",
"Unary Operator" property should be set to "category_rollup"
STEP 3. Process the Foodmart 2000 database (or just the Budget cube, we will
use for our demonstration)
STEP 4. Connect to the Budget cube wit OWC. Below is a sample HTML code that
will do the trick:
<html><body>
<OBJECT class=ptexec classid="clsid:0002E552-0000-0000-C000-000000000046"
height=251
id=pt style="HEIGHT: 251px; LEFT: 0px; TOP: 0px; WIDTH: 502px" width=502>
<PARAM NAME="ConnectionString" VALUE='Data Source=localhost;Initial
Catalog="Foodmart 2000";Provider=MSOLAP.2;Client Cache Size=25;Log
File=C:\MDXOWC.Log"'>
<PARAM NAME="DataMember" VALUE="BUDGET">
</body></html>
STEP 5. Making a report:
a) drag AMOUNT measure to data area on OWC
b) drag CATEGORY dimension to a page/filter area and check only the "Current
Year's actuals"
c) drag STORE dimension to rows
d) drag TIME dimension to columns
You should have three rows of data (Canada, Mexico and USA) and two columns
(1997,1998). Values for (Canada,1997) and (Mexico,1997) are zero, the rest
are non-zero.
STEP 6 (WHERE WE ACTUALLY HIT THE BUG)
Drag the Time dimension from columns to rows, placing it between first and
second column). The result is a nested Store Country / Year report
BUT!!!!!!!!!!
We have now only one row - USA. Canada and Mexico are gone, as are the
years.
What is more interesting, the same report exported from OWC to Microsoft
Exel works fine (shows correctly all relevant rows and numbers).
My question is if this is a known behaviour / bug and if there is any
workaround or pending fix. Matter is rather serious, since our customers are
getting wrong numbers out of their OLAP/BI systems.
Best Regards
Szymon Slupik, CTO
CDN S.A., Krakow, Poland