Drillthrough appears to be showing multiple rows for each single record that should be displayed.
For example, in my fact table, there is a single record for each trip. The following MDX query returns a single cell with a trip count of three.
SELECT NON EMPTY { [CP Date].[Date by Month].[Year].&[2002] } ON COLUMNS ,
NON EMPTY { [Commodity].[Commodity by Type and Group].[Commodity Type].&[Other] } ON ROWS
FROM [Barge Trip]
WHERE ( [Measures].[Trip Count] )
If I drill through on that cell, exactly three records should be returned. However, in actuality, 12 rows are returned - three sets each having four identical rows. To drill through, I am simply adding the DRILLTHROUGH keyword to the MDX query shown above as follows:
DRILLTHROUGH
SELECT NON EMPTY { [CP Date].[Date by Month].[Year].&[2002] } ON COLUMNS ,
NON EMPTY { [Commodity].[Commodity by Type and Group].[Commodity Type].&[Other] } ON ROWS
FROM [Barge Trip]
WHERE ( [Measures].[Trip Count] )
The relationships between my fact table and my dimension tables are quite simple. Can anyone suggest any theories about what might be causing this?
Are you using AS 2000 or AS 2005 - there was once an issue like this in AS 2000, when the cube schema had been optimized...|||I am using AS2005.|||In that case, did you configure MOLAP or ROLAP storage for the fact dimension of the measure group containing [Measures].[Trip Count] - if it's ROLAP you should be able to trace the relational query to the fact table? And is [Measures].[Trip Count] a straight "count" measure on the measure group?|||For the record, I am now executing my drill throughs from ProClarity and am still getting back the four rows for each "real" fact record.
With regard to the second question question about [Measures].[Trip Count], it is a straight "count" measure on the measure group.
With regard to MOLAP/ROLAP, my partition was originally configured for MOLAP. Per your response, I configured the single partition associated with the measure group in question to "ROLAP 30%". I ran Query Profiler to attempt to trace the query for the drill through. I started two profiler windows - one for Analysis Services and one for the database engine. I then executed the drill through operation. I can see the MDX query in the Analysis Services profiler trace, but I see no Transact SQL query in the database engine trace. The MDX query I am seeing is...
DRILLTHROUGH MAXROWS 1000 SELECT { ( [CP Date].[Date by Month].[Year].&[2005] ) } ON COLUMNS ,
{ ( [Commodity].[Commodity by Type and Group].[Commodity Type].&[Unspecified] ) } ON ROWS
FROM [Barge Trip]
WHERE ( [Measures].[Trip Count], [Barge].[Barge].[All], [Barge].[Hull Depth].[All], [Barge].[Cover Type].[All], [Barge].[Barge Type].[All], [Barge].[Year Built].[All], [Barge].[Shipyard Built].[All], [Barge].[Model Name].[All], [Barge].[Model Description].[All], [Barge Trip].[Barge Trip].[All], [Barge Trip].[Direction].[All], [Barge Trip].[Company].[All], [Barge Trip].[Cover Status].[All], [Barge Trip].[Load Status].[All], [Commodity].[Commodity Type].[All], [Commodity].[Commodity Group].[All], [CP Date].[Date by Week].[All], [CP Date].[Day of Week].[All], [CP Date].[Week of Year].[All], [CP Date].[Month of Year].[All], [CP Date].[Quarter of Year].[All], [CP Date].[Day Of Month].[All], [CP Date].[Date by Month CP Date Calculations].&[Current CP Date], [Customer Freight].[Customer Name].[All], [Trip Origin Location].[By Area].[All], [Trip Origin Location].[By River].[All], [Trip Origin Location].[Facility Name].[All], [Trip Origin Location].[Facility Category].[All], [Trip Origin Location].[Facility Type].[All], [Trip Origin Location].[Town].[All], [Trip Origin Location].[River Segment].[All], [Trip Origin Location].[Cycle Area].[All], [Trip Origin Location].[Owner].[All], [Trip Origin Location].[Operator].[All], [Prior Unload Location].[By Area].[All], [Prior Unload Location].[By River].[All], [Prior Unload Location].[Facility Name].[All], [Prior Unload Location].[Facility Category].[All], [Prior Unload Location].[Facility Type].[All], [Prior Unload Location].[Town].[All], [Prior Unload Location].[River Segment].[All], [Prior Unload Location].[Cycle Area].[All], [Prior Unload Location].[Owner].[All], [Prior Unload Location].[Operator].[All], [Release Date].[Date by Month].[All], [Release Date].[Date by Week].[All], [Release Date].[Day of Week].[All], [Release Date].[Week of Year].[All], [Release Date].[Month of Year].[All], [Release Date].[Quarter of Year].[All], [Release Date].[Date by Month Release Date Calculations].&[Current Release Date], [Trip Destination Location].[By Area].[All], [Trip Destination Location].[By River].[All], [Trip Destination Location].[Facility Name].[All], [Trip Destination Location].[Facility Category].[All], [Trip Destination Location].[Facility Type].[All], [Trip Destination Location].[Town].[All], [Trip Destination Location].[River Segment].[All], [Trip Destination Location].[Cycle Area].[All], [Trip Destination Location].[Owner].[All], [Trip Destination Location].[Operator].[All], [Trip Start Date].[Date by Month].[All], [Trip Start Date].[Date by Week].[All], [Trip Start Date].[Day of Week].[All], [Trip Start Date].[Week of Year].[All], [Trip Start Date].[Month of Year].[All], [Trip Start Date].[Quarter of Year].[All], [Trip Start Date].[Date by Month Trip Start Date Calculations].&[Current Trip Start Date], [Trip End Date].[Date by Month].[All], [Trip End Date].[Date by Week].[All], [Trip End Date].[Day of Week].[All], [Trip End Date].[Week of Year].[All], [Trip End Date].[Month of Year].[All], [Trip End Date].[Quarter of Year].[All], [Trip End Date].[Date by Month Trip End Date Calculations].&[Current Trip End Date] )
Thanks for your ongoing assistance.
|||Actually, what I had in mind was configuring ROLAP storage for the "Fact" dimension of the measure group (for example, the [Internet Sales Order Details] ROLAP dimension in Adventure Works, which has a "Fact" relation to the "Internet Sales" measure group) - not for the measure group partition (though that should be OK either way). See if you could trace the relational SQL query, when you do that?
|||
I presume a "fact dimension" is simply a dimension based on the fact table. I did not have one for this cube, so I have attempted to set one up. My Rolap fact dimension processes fine, but when I try to browse it in the dimension browser, I get the following errors:
Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.
Errors in the OLAP storage engine: An error occurred while the 'Barge Trip Key' attribute of the 'Fact Barge Trip' dimension from the 'MEMCO Analysis' database was being processed.
Errors in the OLAP storage engine: Maximum number of ROLAP rows to process in one query has been exceeded.
Errors in the OLAP storage engine: Execution limit reached
Execution complete
If I change the dimension's storage mode to Molap and reprocess, then I can browse the dimension with no problem and it displays the dimension members I expect. In fact, I can incorporate it into the cube when it is in Molap storage mode and it aggregates correctly. Do you have any idea what's going on? I realize this is just a step toward solving the real problem, but I guess I need help with the step.
Thanks.
|||Since it seems that you may not be familiar with fact dimensions, you can study the configuration of [Internet Sales Order Details] in Adventure Works for guidance. After creating the fact dimension, you should define the Dimension Usage relation to its Measure Group as "Fact". Then you can retry your original DrillThrough query (with either MOLAP or ROLAP storage), to see if its results have changed.|||
I've been gone for a couple days, but now am back.
When I set up the fact dimension earlier this week, I did study the [Internet Sales Order Details] dimension as you suggest. In light of that example, I also had defined the "Fact" relationship between the [Barge Trip] measure group my new fact dimension [Fact Start Year] (I renamed the fact dimension from [Fact Barge Trip] to [Fact Start Year] since my prior entry). Unlike the Adventure Works example, my fact dimension is very simple involving only the existing fact table to which I added a single calculated column to provide a reasonable dimension. The new dimension has eight members.
When the new fact dimension is set to Molap, I can drill through in the same manner as previously described, and I still get the "multiple rows" returned for each underlying fact record. Here's the DRILLTHROUGH query that references my new "fact" dimension:
DRILLTHROUGH
SELECT { [Fact Start Year].[Trip Start Year].&[2005] } ON COLUMNS ,
{ [Commodity].[Commodity by Type and Group].[Commodity Type].&[Unspecified] } ON ROWS
FROM [Barge Trip]
WHERE ( [Measures].[Trip Count] )
However, if I modify the [Fact Start Year] dimension in the dimension editor changing the Storage Mode to Rolap, and then reprocess it, I am unable to browse the dimension. I get the errors that I previously mentioned when I try to browse the dimension. I can still reprocess the measure group. If I do, and then execute an MDX query against it, that also gets the same error...
Executing the query ...
Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.
Errors in the OLAP storage engine: An error occurred while the 'Trip Start Year' attribute of the 'Fact Start Year' dimension from the 'MEMCO Analysis' database was being processed.
Errors in the OLAP storage engine: Maximum number of ROLAP rows to process in one query has been exceeded.
Errors in the OLAP storage engine: Execution limit reached
Execution complete
Thoughts?
|||
A couple of thoughts:
- To resolve the ROLAP rows error, I recall that this SSAS Server Property has to be increased (can't find the relevant thread right now, but you can try tweaking it):
http://www.microsoft.com/technet/prodtechnol/sql/2005/ssasproperties.mspx
>>
SQL Server 2005 Analysis Services (SSAS) Server Properties
Published: June 26, 2006
SQL Server Technical Article
Writers: Anthony T. Mann, Edward Melomed
...
ROLAPDimensionProcessingEffort
ROLAPDimensionProcessingEffort is a server property that controls the maximum number of rows SSAS will obtain from a relational database in an attempt to resolve a query to a ROLAP dimension. If Analysis Server determines that the number of records that must be processed from the underlying database is greater than the value specified by this property, an error is returned to the client.
Property Name
General Page: OLAP \ Process \ ROLAPDimensionProcessingEffort
Default Value
300000
Unit of Measure
Data records
Data Type
Integer
Minimum Value
0 – No query to a ROLAP dimension will succeed.
Maximum Value
2147483647
Requires Restart
No
Alternate GUI Tool
None
Special Notes
None
Security Implications
None
>>
- For the multiple rows returned in MOLAP mode, only thing I can think of checking is that the key attribute configured for the fact dimension is unique.
|||Increasing ROLAPDimensionProcessingEffort to be greater than the number of rows in my underlying fact table (factBargeTrip) does allow my MDX queries to complete successfully when the StorageMode for my fact dimension is set to "Rolap". Thanks for the hint.
Now, when I execute a drillthrough, I am seeing a Transact SQL statement come through in the Profiler. The MDX query is still returning four rows when it ought to return one - even now that I am in Rolap mode. But if I grab the Transact SQL Query from profiler and execute it against the relational database underlying the cube, it only returns one row. The fact key (FactBargeTripKey) returned by the Transact SQL query matches the fact key (FactBargeTripKey) returned four times by the MDX query which I also see come over in profiler (I have both a db engine window and an AS window open in Profiler), so it appears that I am looking at the right thing.
Here's my most recent MDX query:
DRILLTHROUGH
SELECT { [Fact Start Year].[Trip Start Year].&[2005] } ON COLUMNS ,
{ [Commodity].[Commodity by Type and Group].[Commodity Group].&[Forest Products] } ON ROWS
FROM [Barge Trip]
WHERE ( [Measures].[Trip Count] )
CELL PROPERTIES VALUE, FORMATTED_VALUE, CELL_ORDINAL, ACTION_TYPE
And, here's the associated SQL query generated by Analysis Services which returns a single row...
exec sp_executesql N'
SELECT SUM ( [dbo_factBargeTrip].[dbo_factBargeTripDemurrageCostConst0_0] )
AS [dbo_factBargeTripDemurrageCostConst0_0], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripCostCleaning0_1] )
AS [dbo_factBargeTripCostCleaning0_1], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripCostFleetingOrigin0_2] )
AS [dbo_factBargeTripCostFleetingOrigin0_2], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripCostFleetingDestination0_3] )
AS [dbo_factBargeTripCostFleetingDestination0_3], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripCostFleetingIntermediate0_4] )
AS [dbo_factBargeTripCostFleetingIntermediate0_4], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripCostFleeting0_5] )
AS [dbo_factBargeTripCostFleeting0_5], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripCostOther0_6] )
AS [dbo_factBargeTripCostOther0_6], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripCostShiftingOrigin0_7] )
AS [dbo_factBargeTripCostShiftingOrigin0_7], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripCostShiftingDestination0_8] )
AS [dbo_factBargeTripCostShiftingDestination0_8], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripCostShiftingIntermediate0_9] )
AS [dbo_factBargeTripCostShiftingIntermediate0_9], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripCostShifting0_10] )
AS [dbo_factBargeTripCostShifting0_10], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripCostTowing0_11] )
AS [dbo_factBargeTripCostTowing0_11], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripCostFleetToFleet0_12] )
AS [dbo_factBargeTripCostFleetToFleet0_12], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripCostPriorEmpty0_13] )
AS [dbo_factBargeTripCostPriorEmpty0_13], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripRevenueStorage0_14] )
AS [dbo_factBargeTripRevenueStorage0_14], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripRevenueOriginDemurrage0_15] )
AS [dbo_factBargeTripRevenueOriginDemurrage0_15], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripRevenueDestinationDemurrage0_16] )
AS [dbo_factBargeTripRevenueDestinationDemurrage0_16], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripRevenueAllDemurrage0_17] )
AS [dbo_factBargeTripRevenueAllDemurrage0_17], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripRevenueTotalDemurrage0_18] )
AS [dbo_factBargeTripRevenueTotalDemurrage0_18], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripRevenueTotalFreight0_19] )
AS [dbo_factBargeTripRevenueTotalFreight0_19], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripRevenueOther0_20] )
AS [dbo_factBargeTripRevenueOther0_20], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripTonsActual0_21] )
AS [dbo_factBargeTripTonsActual0_21], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripTonsBilled0_22] )
AS [dbo_factBargeTripTonsBilled0_22], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripTripDays0_23] )
AS [dbo_factBargeTripTripDays0_23], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripTripDaysAveraging0_24] )
AS [dbo_factBargeTripTripDaysAveraging0_24], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripTripDaysDemurrage0_25] )
AS [dbo_factBargeTripTripDaysDemurrage0_25], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripTripDaysEmpty0_26] )
AS [dbo_factBargeTripTripDaysEmpty0_26], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripTripDaysLoaded0_27] )
AS [dbo_factBargeTripTripDaysLoaded0_27], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripTripDaysFree0_28] )
AS [dbo_factBargeTripTripDaysFree0_28], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripTripDaysFreight0_29] )
AS [dbo_factBargeTripTripDaysFreight0_29], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripTripDaysStorage0_30] )
AS [dbo_factBargeTripTripDaysStorage0_30], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripDaysToLoad0_31] )
AS [dbo_factBargeTripDaysToLoad0_31], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripDaysToUnload0_32] )
AS [dbo_factBargeTripDaysToUnload0_32],
COUNT_BIG ( [dbo_factBargeTrip].[dbo_factBargeTrip0_33] )
AS [dbo_factBargeTrip0_33], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripDemurrageCost0_34] )
AS [dbo_factBargeTripDemurrageCost0_34], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripCostTotal0_35] )
AS [dbo_factBargeTripCostTotal0_35], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripRevenueTotal0_36] )
AS [dbo_factBargeTripRevenueTotal0_36], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripMargin_x0020_With_x0020_Demurrage0_37] )
AS [dbo_factBargeTripMargin_x0020_With_x0020_Demurrage0_37], SUM ( [dbo_factBargeTrip].[dbo_factBargeTripMargin_x0020_Without_x0020_Demurrage0_38] )
AS [dbo_factBargeTripMargin_x0020_Without_x0020_Demurrage0_38], SUM ( [dbo_factBargeTrip].[dbo_factBargeTrip0_39] )
AS [dbo_factBargeTrip0_39],[dbo_factBargeTrip].[dbo_factBargeTripFactBargeTripKey0_40]
AS [dbo_factBargeTripFactBargeTripKey0_40],[dbo_factBargeTrip].[dbo_factBargeTripTripStartYear0_41]
AS [dbo_factBargeTripTripStartYear0_41],[dbo_factBargeTrip].[dbo_factBargeTripBargeKey0_42]
AS [dbo_factBargeTripBargeKey0_42],[dbo_factBargeTrip].[dbo_factBargeTripBargeTripKey0_43]
AS [dbo_factBargeTripBargeTripKey0_43],[dbo_factBargeTrip].[dbo_factBargeTripCommodityKey0_44]
AS [dbo_factBargeTripCommodityKey0_44],[dbo_factBargeTrip].[dbo_factBargeTripCpDateKey0_45]
AS [dbo_factBargeTripCpDateKey0_45],[dbo_factBargeTrip].[dbo_factBargeTripCustomerFreightKey0_46]
AS [dbo_factBargeTripCustomerFreightKey0_46],[dbo_factBargeTrip].[dbo_factBargeTripCycleEndDateKey0_47]
AS [dbo_factBargeTripCycleEndDateKey0_47],[dbo_factBargeTrip].[dbo_factBargeTripCycleStartDateKey0_48]
AS [dbo_factBargeTripCycleStartDateKey0_48],[dbo_factBargeTrip].dbo_factBargeTripOriginLocationKey0_49]
AS [dbo_factBargeTripOriginLocationKey0_49],[dbo_factBargeTrip].[dbo_factBargeTripPriorUnloadLocationKey0_50]
AS [dbo_factBargeTripPriorUnloadLocationKey0_50],[dbo_factBargeTrip].[dbo_factBargeTripReleaseDateKey0_51]
AS [dbo_factBargeTripReleaseDateKey0_51],[dbo_factBargeTrip].[dbo_factBargeTripDestinationLocationKey0_52]
AS [dbo_factBargeTripDestinationLocationKey0_52],[dbo_factBargeTrip].[dbo_factBargeTripTripStartDateKey0_53]
AS [dbo_factBargeTripTripStartDateKey0_53],[dbo_factBargeTrip].[dbo_factBargeTripTripEndDateKey0_54]
AS [dbo_factBargeTripTripEndDateKey0_54],[dbo_dimBarge_2].[BargeNumber] AS [dbo_dimBargeBargeNumber2_0],[dbo_dimBarge_2].[HullDepth]
AS [dbo_dimBargeHullDepth2_1],[dbo_dimBarge_2].[CoverType] AS [dbo_dimBargeCoverType2_2],[dbo_dimBarge_2].[BargeType]
AS [dbo_dimBargeBargeType2_3],[dbo_dimBarge_2].[YearBuilt] AS [dbo_dimBargeYearBuilt2_4],[dbo_dimBarge_2].[ShipyardBuilt]
AS [dbo_dimBargeShipyardBuilt2_5],[dbo_dimBarge_2].[ModelName] AS [dbo_dimBargeModelName2_6],[dbo_dimBarge_2].[ModelDescription]
AS [dbo_dimBargeModelDescription2_7],[dbo_dimBarge_2].[BargeID] AS [dbo_dimBargeBargeID2_8],[dbo_dimDate_12].[WeekContextKey]
AS [dbo_dimDateWeekContextKey5_0],[dbo_dimDate_12].[IsWeekday] AS [dbo_dimDateIsWeekday5_1],[dbo_dimDate_12].[DayNameKey]
AS [dbo_dimDateDayNameKey5_2],[dbo_dimDate_12].[WeekNameKey] AS [dbo_dimDateWeekNameKey5_3],[dbo_dimDate_12].[MonthNameKey]
AS [dbo_dimDateMonthNameKey5_4],[dbo_dimDate_12].[QuarterNameKey] AS [dbo_dimDateQuarterNameKey5_5],[dbo_dimDate_12].[DayOfMonthKey]
AS [dbo_dimDateDayOfMonthKey5_6],[dbo_dimDate_11].[WeekContextKey] AS [dbo_dimDateWeekContextKey7_0],[dbo_dimDate_11].[IsWeekday]
AS [dbo_dimDateIsWeekday7_1],[dbo_dimDate_11].[DayNameKey] AS [dbo_dimDateDayNameKey7_2],[dbo_dimDate_11].[WeekNameKey] AS [dbo_dimDateWeekNameKey7_3],[dbo_dimDate_11].[MonthNameKey] AS [dbo_dimDateMonthNameKey7_4],[dbo_dimDate_11].[QuarterNameKey] AS [dbo_dimDateQuarterNameKey7_5],[dbo_dimDate_11].[DayOfMonthKey] AS [dbo_dimDateDayOfMonthKey7_6],[dbo_dimLocation_22].[RiverName] AS [dbo_dimLocationRiverName9_0],[dbo_dimLocation_22].[FacilityCategory] AS [dbo_dimLocationFacilityCategory9_1],[dbo_dimLocation_22].[FacilityType] AS [dbo_dimLocationFacilityType9_2],[dbo_dimLocation_22].[FacilityAddress] AS [dbo_dimLocationFacilityAddress9_3],[dbo_dimLocation_22].[RiverCode] AS [dbo_dimLocationRiverCode9_4],[dbo_dimLocation_22].[RiverStartMile] AS [dbo_dimLocationRiverStartMile9_5],[dbo_dimLocation_22].[RiverEndMile] AS [dbo_dimLocationRiverEndMile9_6],[dbo_dimLocation_22].[SegmentStartMile] AS [dbo_dimLocationSegmentStartMile9_7],[dbo_dimLocation_22].[SegmentEndMile] AS [dbo_dimLocationSegmentEndMile9_8],[dbo_dimLocation_22].[CycleArea] AS [dbo_dimLocationCycleArea9_9],[dbo_dimLocation_22].[Owner] AS [dbo_dimLocationOwner9_10],[dbo_dimLocation_22].[Operator] AS [dbo_dimLocationOperator9_11],[dbo_dimLocation_20].[RiverName] AS [dbo_dimLocationRiverName11_0],[dbo_dimLocation_20].[FacilityCategory] AS [dbo_dimLocationFacilityCategory11_1],[dbo_dimLocation_20].[FacilityType] AS [dbo_dimLocationFacilityType11_2],[dbo_dimLocation_20].[FacilityAddress] AS [dbo_dimLocationFacilityAddress11_3],[dbo_dimLocation_20].[RiverCode] AS [dbo_dimLocationRiverCode11_4],[dbo_dimLocation_20].[RiverStartMile] AS [dbo_dimLocationRiverStartMile11_5],[dbo_dimLocation_20].[RiverEndMile] AS [dbo_dimLocationRiverEndMile11_6],[dbo_dimLocation_20].[SegmentStartMile] AS [dbo_dimLocationSegmentStartMile11_7],[dbo_dimLocation_20].[SegmentEndMile] AS [dbo_dimLocationSegmentEndMile11_8],[dbo_dimLocation_20].[CycleArea] AS [dbo_dimLocationCycleArea11_9],[dbo_dimLocation_20].[Owner] AS [dbo_dimLocationOwner11_10],[dbo_dimLocation_20].[Operator] AS [dbo_dimLocationOperator11_11],[dbo_dimLocation_19].[RiverName] AS [dbo_dimLocationRiverName13_0],[dbo_dimLocation_19].[FacilityCategory] AS [dbo_dimLocationFacilityCategory13_1],[dbo_dimLocation_19].[FacilityType] AS [dbo_dimLocationFacilityType13_2],[dbo_dimLocation_19].[FacilityAddress] AS [dbo_dimLocationFacilityAddress13_3],[dbo_dimLocation_19].[RiverCode] AS [dbo_dimLocationRiverCode13_4],[dbo_dimLocation_19].[RiverStartMile] AS [dbo_dimLocationRiverStartMile13_5],[dbo_dimLocation_19].[RiverEndMile] AS [dbo_dimLocationRiverEndMile13_6],[dbo_dimLocation_19].[SegmentStartMile] AS [dbo_dimLocationSegmentStartMile13_7],[dbo_dimLocation_19].[SegmentEndMile] AS [dbo_dimLocationSegmentEndMile13_8],[dbo_dimLocation_19].[CycleArea] AS [dbo_dimLocationCycleArea13_9],[dbo_dimLocation_19].[Owner] AS [dbo_dimLocationOwner13_10],[dbo_dimLocation_19].[Operator] AS [dbo_dimLocationOperator13_11],[dbo_dimDate_7].[WeekContextKey] AS [dbo_dimDateWeekContextKey15_0],[dbo_dimDate_7].[IsWeekday] AS [dbo_dimDateIsWeekday15_1],[dbo_dimDate_7].[DayNameKey] AS [dbo_dimDateDayNameKey15_2],[dbo_dimDate_7].[WeekNameKey] AS [dbo_dimDateWeekNameKey15_3],[dbo_dimDate_7].[MonthNameKey] AS [dbo_dimDateMonthNameKey15_4],[dbo_dimDate_7].[QuarterNameKey] AS [dbo_dimDateQuarterNameKey15_5],[dbo_dimDate_7].[DayOfMonthKey] AS [dbo_dimDateDayOfMonthKey15_6],[dbo_dimDate_23].[WeekContextKey] AS [dbo_dimDateWeekContextKey17_0],[dbo_dimDate_23].[IsWeekday] AS [dbo_dimDateIsWeekday17_1],[dbo_dimDate_23].[DayNameKey] AS [dbo_dimDateDayNameKey17_2],[dbo_dimDate_23].[WeekNameKey] AS [dbo_dimDateWeekNameKey17_3],[dbo_dimDate_23].[MonthNameKey] AS [dbo_dimDateMonthNameKey17_4],[dbo_dimDate_23].[QuarterNameKey] AS [dbo_dimDateQuarterNameKey17_5],[dbo_dimDate_23].[DayOfMonthKey] AS [dbo_dimDateDayOfMonthKey17_6],[dbo_dimDate_21].[WeekContextKey] AS [dbo_dimDateWeekContextKey19_0],[dbo_dimDate_21].[IsWeekday] AS [dbo_dimDateIsWeekday19_1],[dbo_dimDate_21].[DayNameKey] AS [dbo_dimDateDayNameKey19_2],[dbo_dimDate_21].[WeekNameKey] AS [dbo_dimDateWeekNameKey19_3],[dbo_dimDate_21].[MonthNameKey] AS [dbo_dimDateMonthNameKey19_4],[dbo_dimDate_21].[QuarterNameKey] AS [dbo_dimDateQuarterNameKey19_5],[dbo_dimDate_21].[DayOfMonthKey] AS [dbo_dimDateDayOfMonthKey19_6],[dbo_dimDate_24].[WeekContextKey] AS [dbo_dimDateWeekContextKey21_0],[dbo_dimDate_24].[IsWeekday] AS [dbo_dimDateIsWeekday21_1],[dbo_dimDate_24].[DayNameKey] AS [dbo_dimDateDayNameKey21_2],[dbo_dimDate_24].[WeekNameKey] AS [dbo_dimDateWeekNameKey21_3],[dbo_dimDate_24].[MonthNameKey] AS [dbo_dimDateMonthNameKey21_4],[dbo_dimDate_24].[QuarterNameKey] AS [dbo_dimDateQuarterNameKey21_5],[dbo_dimDate_24].[DayOfMonthKey] AS [dbo_dimDateDayOfMonthKey21_6],[dbo_dimCustomerFreight_8].[CustomerKey] AS [dbo_dimCustomerFreightCustomerKey22_0],[dbo_dimCustomerFreight_8].[CustomerID] AS [dbo_dimCustomerFreightCustomerID22_1],[dbo_dimBargeTrip_5].[BargeTripID] AS [dbo_dimBargeTripBargeTripID25_0],[dbo_dimBargeTrip_5].[CycleNumber] AS [dbo_dimBargeTripCycleNumber25_1],[dbo_dimBargeTrip_5].[CyclePattern] AS [dbo_dimBargeTripCyclePattern25_2],[dbo_dimBargeTrip_5].[CycleTripCount] AS [dbo_dimBargeTripCycleTripCount25_3],[dbo_dimBargeTrip_5].[Direction] AS [dbo_dimBargeTripDirection25_4],[dbo_dimBargeTrip_5].[Company] AS [dbo_dimBargeTripCompany25_5],[dbo_dimBargeTrip_5].[CoverStatus] AS [dbo_dimBargeTripCoverStatus25_6],[dbo_dimBargeTrip_5].[LoadStatus] AS [dbo_dimBargeTripLoadStatus25_7]
FROM (
SELECT [DemurrageCostConst] AS [dbo_factBargeTripDemurrageCostConst0_0],[CostCleaning] AS [dbo_factBargeTripCostCleaning0_1],[CostFleetingOrigin] AS [dbo_factBargeTripCostFleetingOrigin0_2],[CostFleetingDestination] AS [dbo_factBargeTripCostFleetingDestination0_3],[CostFleetingIntermediate] AS [dbo_factBargeTripCostFleetingIntermediate0_4],[CostFleeting] AS [dbo_factBargeTripCostFleeting0_5],[CostOther] AS [dbo_factBargeTripCostOther0_6],[CostShiftingOrigin] AS [dbo_factBargeTripCostShiftingOrigin0_7],[CostShiftingDestination] AS [dbo_factBargeTripCostShiftingDestination0_8],[CostShiftingIntermediate] AS [dbo_factBargeTripCostShiftingIntermediate0_9],[CostShifting] AS [dbo_factBargeTripCostShifting0_10],[CostTowing] AS [dbo_factBargeTripCostTowing0_11],[CostFleetToFleet] AS [dbo_factBargeTripCostFleetToFleet0_12],[CostPriorEmpty] AS [dbo_factBargeTripCostPriorEmpty0_13],[RevenueStorage] AS [dbo_factBargeTripRevenueStorage0_14],[RevenueOriginDemurrage] AS [dbo_factBargeTripRevenueOriginDemurrage0_15],[RevenueDestinationDemurrage] AS [dbo_factBargeTripRevenueDestinationDemurrage0_16],[RevenueAllDemurrage] AS [dbo_factBargeTripRevenueAllDemurrage0_17],[RevenueTotalDemurrage] AS [dbo_factBargeTripRevenueTotalDemurrage0_18],[RevenueTotalFreight] AS [dbo_factBargeTripRevenueTotalFreight0_19],[RevenueOther] AS [dbo_factBargeTripRevenueOther0_20],[TonsActual] AS [dbo_factBargeTripTonsActual0_21],[TonsBilled] AS [dbo_factBargeTripTonsBilled0_22],[TripDays] AS [dbo_factBargeTripTripDays0_23],[TripDaysAveraging] AS [dbo_factBargeTripTripDaysAveraging0_24],[TripDaysDemurrage] AS [dbo_factBargeTripTripDaysDemurrage0_25],[TripDaysEmpty] AS [dbo_factBargeTripTripDaysEmpty0_26],[TripDaysLoaded] AS [dbo_factBargeTripTripDaysLoaded0_27],[TripDaysFree] AS [dbo_factBargeTripTripDaysFree0_28],[TripDaysFreight] AS [dbo_factBargeTripTripDaysFreight0_29],[TripDaysStorage] AS [dbo_factBargeTripTripDaysStorage0_30],[DaysToLoad] AS [dbo_factBargeTripDaysToLoad0_31],[DaysToUnload] AS [dbo_factBargeTripDaysToUnload0_32],1 AS [dbo_factBargeTrip0_33],(TripDaysDemurrage + TripDaysStorage) * DemurrageCostConst AS [dbo_factBargeTripDemurrageCost0_34],
CostCleaning + CostFleeting + CostOther + CostShifting + CostTowing + CostFleetToFleet AS [dbo_factBargeTripCostTotal0_35],RevenueTotalFreight + RevenueTotalDemurrage + RevenueStorage + RevenueOther AS [dbo_factBargeTripRevenueTotal0_36],(RevenueTotalFreight + RevenueTotalDemurrage + RevenueStorage + RevenueOther) - (CostCleaning + CostFleeting + CostOther + CostShifting + CostTowing + CostFleetToFleet) AS [dbo_factBargeTripMargin_x0020_With_x0020_Demurrage0_37],(RevenueTotalFreight + RevenueStorage + RevenueOther - RevenueTotalDemurrage) - (CostCleaning + CostFleeting + CostOther + CostShifting + CostTowing + CostFleetToFleet - ((TripDaysDemurrage + TripDaysStorage) * DemurrageCostConst) ) AS [dbo_factBargeTripMargin_x0020_Without_x0020_Demurrage0_38],
TripDays - TripDaysDemurrage - TripDaysStorage AS [dbo_factBargeTrip0_39],[FactBargeTripKey] AS [dbo_factBargeTripFactBargeTripKey0_40],left(cast(TripStartDateKey as varchar(8)), 4) AS [dbo_factBargeTripTripStartYear0_41],[BargeKey] AS [dbo_factBargeTripBargeKey0_42],[BargeTripKey] AS [dbo_factBargeTripBargeTripKey0_43],[CommodityKey] AS [dbo_factBargeTripCommodityKey0_44],[CpDateKey] AS [dbo_factBargeTripCpDateKey0_45],[CustomerFreightKey] AS [dbo_factBargeTripCustomerFreightKey0_46],[CycleEndDateKey] AS [dbo_factBargeTripCycleEndDateKey0_47],[CycleStartDateKey] AS [dbo_factBargeTripCycleStartDateKey0_48],[OriginLocationKey] AS [dbo_factBargeTripOriginLocationKey0_49],[PriorUnloadLocationKey] AS [dbo_factBargeTripPriorUnloadLocationKey0_50],[ReleaseDateKey] AS [dbo_factBargeTripReleaseDateKey0_51],[DestinationLocationKey] AS [dbo_factBargeTripDestinationLocationKey0_52],[TripStartDateKey] AS [dbo_factBargeTripTripStartDateKey0_53],[TripEndDateKey] AS [dbo_factBargeTripTripEndDateKey0_54]
FROM [dbo].[factBargeTrip] )
AS [dbo_factBargeTrip],[dbo].[dimBarge] AS [dbo_dimBarge_2],[dbo].[dimDate] AS [dbo_dimDate_12],[dbo].[dimDate] AS [dbo_dimDate_11],[dbo].[dimLocation] AS
[dbo_dimLocation_22],[dbo].[dimLocation] AS [dbo_dimLocation_20],[dbo].[dimLocation] AS [dbo_dimLocation_19],[dbo].[dimDate] AS [dbo_dimDate_7],[dbo].[dimDate] AS [dbo_dimDate_23],[dbo].[dimDate] AS [dbo_dimDate_21],[dbo].[dimDate] AS [dbo_dimDate_24],[dbo].[dimCustomerFreight] AS [dbo_dimCustomerFreight_8],[dbo].[dimBargeTrip] AS [dbo_dimBargeTrip_5],[dbo].[dimCommodity] AS [dbo_dimCommodity_6]
WHERE ( ( [dbo_factBargeTrip].[dbo_factBargeTripBargeKey0_42] = [dbo_dimBarge_2].[BargeKey] )
AND ( [dbo_factBargeTrip].[dbo_factBargeTripBargeTripKey0_43] = [dbo_dimBargeTrip_5].[BargeTripKey] )
AND ( [dbo_factBargeTrip].[dbo_factBargeTripCommodityKey0_44] = [dbo_dimCommodity_6].[CommodityKey] )
AND ( [dbo_factBargeTrip].[dbo_factBargeTripCpDateKey0_45] = [dbo_dimDate_7].[DateKey])
AND ( [dbo_factBargeTrip].[dbo_factBargeTripCustomerFreightKey0_46] = [dbo_dimCustomerFreight_8].[CustomerKey])
AND ( [dbo_factBargeTrip].[dbo_factBargeTripCycleEndDateKey0_47] = [dbo_dimDate_11].[DateKey] )
AND ( [dbo_factBargeTrip].[dbo_factBargeTripCycleStartDateKey0_48] = [dbo_dimDate_12].[DateKey] )
AND ( [dbo_factBargeTrip].[dbo_factBargeTripOriginLocationKey0_49] = [dbo_dimLocation_19].[LocationKey] )
AND ( [dbo_factBargeTrip].[dbo_factBargeTripPriorUnloadLocationKey0_50] = [dbo_dimLocation_20].[LocationKey] )
AND ( [dbo_factBargeTrip].[dbo_factBargeTripReleaseDateKey0_51] = [dbo_dimDate_21].[DateKey] )
AND ( [dbo_factBargeTrip].[dbo_factBargeTripDestinationLocationKey0_52] = [dbo_dimLocation_22].[LocationKey] )
AND ( [dbo_factBargeTrip].[dbo_factBargeTripTripStartDateKey0_53] = [dbo_dimDate_23].[DateKey] )
AND ( [dbo_factBargeTrip].[dbo_factBargeTripTripEndDateKey0_54] = [dbo_dimDate_24].[DateKey] )
AND ( [dbo_dimCommodity_6].[CommodityGroup] = @.Param1 )
AND ( [dbo_factBargeTrip].[dbo_factBargeTripTripStartYear0_41] = @.Param2 ) )
GROUP BY
[dbo_factBargeTrip].[dbo_factBargeTripBargeKey0_42],[dbo_dimBarge_2].[BargeNumber],[dbo_dimBarge_2].[HullDepth],[dbo_dimBarge_2].[CoverType],[dbo_dimBarge_2].[BargeType],[dbo_dimBarge_2].[YearBuilt],[dbo_dimBarge_2].[ShipyardBuilt],[dbo_dimBarge_2].[ModelName],[dbo_dimBarge_2].[ModelDescription],[dbo_dimBarge_2].[BargeID],[dbo_factBargeTrip].[dbo_factBargeTripCommodityKey0_44],[dbo_factBargeTrip].[dbo_factBargeTripCycleStartDateKey0_48],[dbo_dimDate_12].[WeekContextKey],[dbo_dimDate_12].[IsWeekday],[dbo_dimDate_12].[DayNameKey],[dbo_dimDate_12].[WeekNameKey],[dbo_dimDate_12].[MonthNameKey],[dbo_dimDate_12].[QuarterNameKey],[dbo_dimDate_12].[DayOfMonthKey],[dbo_dimDate_12].[DayOfMonthKey],[dbo_factBargeTrip].[dbo_factBargeTripCycleEndDateKey0_47],[dbo_dimDate_11].[WeekContextKey],[dbo_dimDate_11].[IsWeekday],[dbo_dimDate_11].[DayNameKey],[dbo_dimDate_11].[WeekNameKey],[dbo_dimDate_11].[MonthNameKey],[dbo_dimDate_11].[QuarterNameKey],[dbo_dimDate_11].[DayOfMonthKey],[dbo_dimDate_11].[DayOfMonthKey],[dbo_factBargeTrip].[dbo_factBargeTripDestinationLocationKey0_52],[dbo_dimLocation_22].[RiverName],[dbo_dimLocation_22].[FacilityCategory],[dbo_dimLocation_22].[FacilityType],[dbo_dimLocation_22].[FacilityAddress],[dbo_dimLocation_22].[RiverCode],[dbo_dimLocation_22].[RiverStartMile],[dbo_dimLocation_22].[RiverEndMile],[dbo_dimLocation_22].[SegmentStartMile],[dbo_dimLocation_22].[SegmentEndMile],[dbo_dimLocation_22].[CycleArea],[dbo_dimLocation_22].[Owner],[dbo_dimLocation_22].[Operator],[dbo_factBargeTrip].[dbo_factBargeTripPriorUnloadLocationKey0_50],[dbo_dimLocation_20].[RiverName],[dbo_dimLocation_20].[FacilityCategory],[dbo_dimLocation_20].[FacilityType],[dbo_dimLocation_20].[FacilityAddress],[dbo_dimLocation_20].[RiverCode],[dbo_dimLocation_20].[RiverStartMile],[dbo_dimLocation_20].[RiverEndMile],[dbo_dimLocation_20].[SegmentStartMile],[dbo_dimLocation_20].[SegmentEndMile],[dbo_dimLocation_20].[CycleArea],[dbo_dimLocation_20].[Owner],[dbo_dimLocation_20].[Operator],[dbo_factBargeTrip].[dbo_factBargeTripOriginLocationKey0_49],[dbo_dimLocation_19].[RiverName],[dbo_dimLocation_19].[FacilityCategory],[dbo_dimLocation_19].[FacilityType],[dbo_dimLocation_19].[FacilityAddress],[dbo_dimLocation_19].[RiverCode],[dbo_dimLocation_19].[RiverStartMile],[dbo_dimLocation_19].[RiverEndMile],[dbo_dimLocation_19].[SegmentStartMile],[dbo_dimLocation_19].[SegmentEndMile],[dbo_dimLocation_19].[CycleArea],[dbo_dimLocation_19].[Owner],[dbo_dimLocation_19].[Operator],[dbo_factBargeTrip].[dbo_factBargeTripCpDateKey0_45],[dbo_dimDate_7].[WeekContextKey],[dbo_dimDate_7].[IsWeekday],[dbo_dimDate_7].[DayNameKey],[dbo_dimDate_7].[WeekNameKey],[dbo_dimDate_7].[MonthNameKey],[dbo_dimDate_7].[QuarterNameKey],[dbo_dimDate_7].[DayOfMonthKey],[dbo_dimDate_7].[DayOfMonthKey],[dbo_factBargeTrip].[dbo_factBargeTripTripStartDateKey0_53],[dbo_dimDate_23].[WeekContextKey],[dbo_dimDate_23].[IsWeekday],[dbo_dimDate_23].[DayNameKey],[dbo_dimDate_23].[WeekNameKey],[dbo_dimDate_23].[MonthNameKey],[dbo_dimDate_23].[QuarterNameKey],[dbo_dimDate_23].[DayOfMonthKey],[dbo_dimDate_23].[DayOfMonthKey],[dbo_factBargeTrip].[dbo_factBargeTripReleaseDateKey0_51],[dbo_dimDate_21].[WeekContextKey],[dbo_dimDate_21].[IsWeekday],[dbo_dimDate_21].[DayNameKey],[dbo_dimDate_21].[WeekNameKey],[dbo_dimDate_21].[MonthNameKey],[dbo_dimDate_21].[QuarterNameKey],[dbo_dimDate_21].[DayOfMonthKey],[dbo_dimDate_21].[DayOfMonthKey],[dbo_factBargeTrip].[dbo_factBargeTripTripEndDateKey0_54],[dbo_dimDate_24].[WeekContextKey],[dbo_dimDate_24].[IsWeekday],[dbo_dimDate_24].[DayNameKey],[dbo_dimDate_24].[WeekNameKey],[dbo_dimDate_24].[MonthNameKey],[dbo_dimDate_24].[QuarterNameKey],[dbo_dimDate_24].[DayOfMonthKey],[dbo_dimDate_24].[DayOfMonthKey],[dbo_dimCustomerFreight_8].[CustomerKey],[dbo_factBargeTrip].[dbo_factBargeTripCustomerFreightKey0_46],[dbo_dimCustomerFreight_8].[CustomerID],[dbo_factBargeTrip].[dbo_factBargeTripBargeTripKey0_43],[dbo_dimBargeTrip_5].[BargeTripID],[dbo_dimBargeTrip_5].[CycleNumber],[dbo_dimBargeTrip_5].[CyclePattern],[dbo_dimBargeTrip_5].[CycleTripCount],[dbo_dimBargeTrip_5].[Direction],[dbo_dimBargeTrip_5].[Company],[dbo_dimBargeTrip_5].[CoverStatus],[dbo_dimBargeTrip_5].[LoadStatus],[dbo_factBargeTrip].[dbo_factBargeTripFactBargeTripKey0_40],[dbo_factBargeTrip].[dbo_factBargeTripFactBargeTripKey0_40],[dbo_factBargeTrip].[dbo_factBargeTripFactBargeTripKey0_40],[dbo_factBargeTrip].[dbo_factBargeTripTripStartYear0_41]',N'@.Param1 nvarchar(15),@.Param2 nvarchar(4)',@.Param1=N'Forest Products',@.Param2=N'2005'
I have checked the key attribute to ensure that the keys associated with the fact dimension are unique. To test this, I verify that the following query returns no rows:
Select FactBargeTripKey, count(*) From FactBargeTrip
Group By FactBargeTripKey Having count(*) > 1
Surely there's a way to figure out what's going on, but I am at a loss.
|||The only odd thing I noticed in the SQL query was that [dbo_factBargeTrip].[dbo_factBargeTripFactBargeTripKey0_40] is repeated thrice in the GROUP BY - don't know why.
Since DrillThrough queries in AS 2005 can be translated (roughly) to regular MDX equivalents, do you get duplicate rows with something like:
SELECT { [Measures].[Trip Count] } ON COLUMNS ,
NON EMPTY [FactBargeTrip].[FactBargeTripKey].[FactBargeTripKey].Members ON ROWS
FROM [Barge Trip]
WHERE ( [Commodity].[Commodity by Type and Group].[Commodity Group].&[Forest Products], [Fact Start Year].[Trip Start Year].&[2005] )
(assuming that [FactBargeTrip] is the fact dimension, and [FactBargeTripKey] is its key attribute)?
|||I find [dbo_factBargeTrip].[dbo_factBargeTripFactBargeTripKey0_40] in the query three times, but only once in the GROUP BY clause of the query. That SQL query was a lot to wade through, but the three occurrences of [dbo_factBargeTrip].[dbo_factBargeTripFactBargeTripKey0_40] make sense. The second occurrence is within a subquery and defines the alias, [dbo_factBargeTripFactBargeTripKey0_40], based on [FactBargeTrip].[BargeTripKey]. The first occurrence simply selects that column from the subquery. The third occurence is used in a GROUP BY clause for grouping of the result. So, I don't believe that the three occurrences of that string yield a clue.
I executed the query similar to the one you provided (correcting the dimension and key attribute as you suggested) as follows:
SELECT { [Measures].[Trip Count] } ON COLUMNS ,
NON EMPTY [Fact Start Year].[Trip Key].[Trip Key].Members ON ROWS
FROM [Barge Trip]
WHERE ( [Commodity].[Commodity by Type and Group].[Commodity Group].&[Forest Products],
[Fact Start Year].[Trip Start Year].&[2005] )
The dimension and key attribute do map to the underlying database table and column that you specified. The query yielded this result...
Perhaps I should open a case with Microsoft.
|||Since the straight MDX seems to work, one other thing worth trying (if you haven't done this already) is to check the [Fact Start Year].[Trip Key] members returned in the Drillthrough - this could be done either with an explicit Return clause in the Drillthrough statement, or by configuring the Drillthrough columns in a default Drillthrough action (like the Internet Details action in Adventure Works). Is the member key: 288235 actually returned 4 times?|||I had not tried that. But when I do, it yields four records repeating the key four times. Here's the query:
DRILLTHROUGH
SELECT { [Fact Start Year].[Trip Start Year].&[2005] } ON COLUMNS ,
{ [Commodity].[Commodity by Type and Group].[Commodity Group].&[Forest Products] } ON ROWS
FROM [Barge Trip]
WHERE ( [Measures].[Trip Count] )
RETURN [$Fact Start Year].[Trip Key]
Here's the result:
No comments:
Post a Comment