March 6, 2010 at 2:20 pm
I have a Cube with three dimensions, InventoryItems, InventoryLocations, and ServerTime. When I leave the InventoryLocaions dimension out of my MDX query the performance is fine. Adding the InventoryLocations increases the response time from 7 to 10 seconds to over 5 minutes. (2067 rows, 32 columns without it to 175000 rows and 32 columns with). I don't understand what is causing this. There are only 117 different locations. Here is my query with the InventLocations commented out(non empty behavior didn't seem to make any difference):
//mdx query for summary report
WITH MEMBER [ServerTime].[Fiscal Year].[LASTYEAR] AS '[ServerTime].[Fiscal Year].&[2008-10-01T00:00:00]', solve_order = 1
MEMBER [ServerTime].[Fiscal Year].[CURRENTYEAR] AS '[ServerTime].[Fiscal Year].&[2009-10-01T00:00:00]', solve_order = 2
MEMBER Measures.ItemName AS '[InventItems].[INVENTTABLE].CURRENTMEMBER.Member_key'
member Measures.VendName as '[InventItems].[PRIMARYVENDORID].CURRENTMEMBER.Member_key'
//member measures.WHSE AS '[InventLocations].[Warehouses].CURRENTMEMBER.Member_Key'
-- NON_EMPTY_BEHAVIOR = '[Measures].[FRCSTQTY]'
SELECT
CROSSJOIN({[ServerTime].[Fiscal Year].[LASTYEAR],[ServerTime].[Fiscal Year].[CURRENTYEAR]},
{Measures.ItemName, Measures.VendName //, measures.WHSE
,[Measures].[POQTY], [Measures].[UNPSTQTY], [Measures].[SALESQTYONHND],
[Measures].[QTYDELIVERED], [Measures].[FORSALE], [Measures].[QTYTRFED], [Measures].[QTYMOVEDIN],
[Measures].[QTYMOVEDOUT], [Measures].[FRCSTQTY], [Measures].[QTYONSO], [Measures].[Booking Quantity],
[Measures].[QTYONHND], [Measures].[QTYINV] })
ON COLUMNS,
NON EMPTY { (
[InventItems].[INVENTTABLE].[INVENTTABLE],
[InventItems].[PRIMARYVENDORID].[PRIMARYVENDORID]//,
//[InventLocations].[Warehouses].[warehouses]
) }
ON ROWS FROM [InventoryStatus]
March 6, 2010 at 2:32 pm
Just curious if you have Attribute relationships defined in the dimensions and if you have designed and setup any aggregations on the measure groups. Typically both of these usually get overlooked in the initial setup of a SSAS database, especially the second one. Make sure you go into the partitions of the measure groups and define aggregations.
----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
March 6, 2010 at 2:58 pm
Agree with both points Dan made. Note that based on what info you've provided, aggregations may not help much at all as it *appears* that you're trying to bring back the lowest level, of each of the 3 dimensions that you have in your cube. Having the attribute relationship set correctly between the Vendor and Product attributes could make a big difference (there's an implicit one to many between these).
Would also look to review the MDX you're running. Not quite sure why you're creating a text string (e.g. product name, vendor name and warehouse) as measures when you can view them (and *are* viewing them) as members on the rows.
Also, ignoring the likelihood that you will have a significant number of empty intersections, while the column and row counts may seem low, consider the possible intersections you're looking to evaluate and return for the two queries. At 2037 rows and 32 cols, it's roughly 65k cells, whereas at 175,000 rows and 32 columns, thats like 5.6M cells, quite a difference. Getting all of these passed across the wire (let alone evaluated/calculated) would take some time.
Not knowing the business requirement, it's hard to tell what the primary drive for the report is, but if it is for a 'summary report', you may want to consider providing you users with a filtering mechanism. Let them select one (or maye even two or three) of the warehouses and retrieve that dataset. You'd obviously have to test what number of warehouses allows for good query response time and still returns enough information for the user/s.
A slightly revised query (not necessarily going to execute any faster in the slightest) could be :
//mdx query for summary report
WITH MEMBER [ServerTime].[Fiscal Year].[LASTYEAR] AS '[ServerTime].[Fiscal Year].&[2008-10-01T00:00:00]', solve_order = 1
MEMBER [ServerTime].[Fiscal Year].[CURRENTYEAR] AS '[ServerTime].[Fiscal Year].&[2009-10-01T00:00:00]', solve_order = 2
SELECT
CROSSJOIN({[ServerTime].[Fiscal Year].[LASTYEAR],[ServerTime].[Fiscal Year].[CURRENTYEAR]},
{[Measures].[POQTY], [Measures].[UNPSTQTY], [Measures].[SALESQTYONHND],
[Measures].[QTYDELIVERED], [Measures].[FORSALE], [Measures].[QTYTRFED], [Measures].[QTYMOVEDIN],
[Measures].[QTYMOVEDOUT], [Measures].[FRCSTQTY], [Measures].[QTYONSO], [Measures].[Booking Quantity],
[Measures].[QTYONHND], [Measures].[QTYINV] })
ON COLUMNS,
NON EMPTY
{[InventLocations].[Warehouses].[warehouses]} * {[InventItems].[PRIMARYVENDORID].[PRIMARYVENDORID]} * {[InventItems].[INVENTTABLE].[INVENTTABLE]}
ON ROWS FROM [InventoryStatus]
Steve.
March 6, 2010 at 4:21 pm
Steve, I have run the Aggregations based on Usage and you're right, that did not help much. The point of the reprot is to show a snapshot of each item's onhand, on sales order, on PO, current ytd sales, last years sales etc. they need to see this by Vendor, Warehouse, and itemId. All of the fact tables are related to the Item Dimension via the ItemId and to the ItemLocation (Warehouse) dimension via the Location code. There isn't a direct relation between Item and Location. The text string is becuase they insist on seeing the Item and Vendor Codes as well as the Name. So I have to show the Key value and the Name of the Key for at Least Item and Vendor. I actually only need the CrossJoin on two measures but couldn't figure out how to get that along with the rest of my columns.
The end result is an SSRS report.
Thanks for your detailed input. Your query did run in about 3 minutes, which is better than 5 but I still need to get the item and vendor key.
Thanks,
don
March 7, 2010 at 12:04 am
Hey Don,
I realize I'm being pedantic but I would still try to drive your user/s to a clear reason as to why they need to see ~ 175k rows in a single report. There's a few ways around it for e.g.
- if they always want to print them, you could schedule the report to run for each warehouse and send it to the printer destination (can't remember if this is still a sample or in final code now with SSRS)
- seeing as it's in SSRS, you could add filters (multi-select) for Vendor and/or Warehouse
One of the issues you'll be seeing is that using the ID as a measure means that no row will ever be 'empty' (ie all products, warehouses and vendors have member keys). So you'll definitely be wanting to influence their appearance in the set by setting the empty behaviour on the calc members to a measure that you know is indicative of whether the row should be shown (e.g. sales, perhaps SOH; not sure, you'll need to determine this)
You might be able to get away with a query for that looking something like this...
//mdx query for summary report
// CY and LY saes amounts, guessing QTYINV being sales measure
MEMBER Measures.CYSales AS 'SUM([ServerTime].[Fiscal Year].&[2009-10-01T00:00:00], [Measures].[QTYINV])'
MEMBER Measures.LYSales AS 'SUM([ServerTime].[Fiscal Year].&[2008-10-01T00:00:00], [Measures].[QTYINV])'
// ID's as measures
MEMBER Measures.ItemName AS '[InventItems].[INVENTTABLE].CURRENTMEMBER.MEMBER_KEY', NON_EMPTY_BEHAVIOR = [Measures].[QTYINV]
MEMBER Measures.VendName as '[InventItems].[PRIMARYVENDORID].CURRENTMEMBER.MEMBER_KEY', NON_EMPTY_BEHAVIOR = [Measures].[QTYINV]
MEMBER Measures.WHSE AS '[InventLocations].[Warehouses].CURRENTMEMBER.MEMBER_KEY', NON_EMPTY_BEHAVIOR = [Measures].[QTYINV]
SELECT
{Measures.WHSE, Measures.VendName , Measures.ItemName,
Measures.LYSales, Measures.CYSales,
[Measures].[POQTY], [Measures].[UNPSTQTY], [Measures].[SALESQTYONHND],
[Measures].[QTYDELIVERED], [Measures].[FORSALE], [Measures].[QTYTRFED], [Measures].[QTYMOVEDIN],
[Measures].[QTYMOVEDOUT], [Measures].[FRCSTQTY], [Measures].[QTYONSO], [Measures].[Booking Quantity],
[Measures].[QTYONHND], [Measures].[QTYINV] }
ON 0,
NON EMPTY
[InventLocations].[Warehouses].[warehouses]
* [InventItems].[PRIMARYVENDORID].[PRIMARYVENDORID]
* [InventItems].[INVENTTABLE].[INVENTTABLE]
ON 1
FROM
[InventoryStatus]
Steve.
March 7, 2010 at 4:38 pm
Steve,
Thanks again. I'll work on the Non empty behavior. Basically if all the measures are zero then the row should be suppressed, however if ANY of them are not zero then I need the row. I was under the impression that the Non_empty_behavior doesn't accept calculated measures but essentilly if I could add all the measures to gether and get zero then That would be the empty row. I haven't been able to get multi-select parameters to work in SSRS when the dataset behind the parameter comes from an MDX query. (This is SSRS 2005). Is there a trick to it? I have gotten the parameter to work without the multi-select and it does run much faster. Maybe I can convince them to run it for one-vendor or one warehouse at a time.
Don
March 8, 2010 at 6:54 am
Steve,
FYI, even without restricting the Warehouse this is a major improvement. down to 37 seconds. I can use this.
Thanks again.
Don
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply