Hi All,
I've built a cube using a data source and DSV that point to an Oracle 10g database. I am able to process this cube so long as the dimensions are all related to the main fact table using regular dimension usage. The problem I'm having arises when the dimension has to have "referenced" usage through an intermediate dimension. In this case, when the cube processes, the SQL that gets generated and run looks akin to the following:
--
SELECT [CstOrds].[CstOrdsDISCOUNT0_0] AS [CstOrdsDISCOUNT0_0],[CstOrds].[CstOrdsBUY_QTY_DUE0_1] AS [CstOrdsBUY_QTY_DUE0_1],[CstOrds].[CstOrdsDESIRED_QTY0_2] AS [CstOrdsDESIRED_QTY0_2],[CstOrds].[CstOrdsQTY_ASSIGNED0_3] AS [CstOrdsQTY_ASSIGNED0_3],[CstOrds].[CstOrdsQTY_ON_ORDER0_4] AS [CstOrdsQTY_ON_ORDER0_4],[CstOrds].[CstOrdsQTY_PICKED0_5] AS [CstOrdsQTY_PICKED0_5],[CstOrds].[CstOrdsQTY_RETURNED0_6] AS [CstOrdsQTY_RETURNED0_6],[CstOrds].[CstOrdsQTY_SHORT0_7] AS [CstOrdsQTY_SHORT0_7],[CstOrds].[CstOrdsQTY_TO_SHIP0_8] AS [CstOrdsQTY_TO_SHIP0_8],[CstOrds].[CstOrdsQTY_SHIPPED0_9] AS [CstOrdsQTY_SHIPPED0_9],[CstOrds].[CstOrdsQTY_SHIPDIFF0_10] AS [CstOrdsQTY_SHIPDIFF0_10],[CstOrds].[CstOrdsQTY_INVOICED0_11] AS [CstOrdsQTY_INVOICED0_11],[CstOrds].[CstOrdsCOST0_12] AS [CstOrdsCOST0_12],[CstOrds].[CstOrdsNET_AMOUNT0_13] AS [CstOrdsNET_AMOUNT0_13],[CstOrds].[CstOrdsGROSS_AMOUNT0_14] AS [CstOrdsGROSS_AMOUNT0_14],[CstOrds].[CstOrdsSPTA0_15] AS [CstOrdsSPTA0_15],[CstOrds].[CstOrdsTCA0_16] AS [CstOrdsTCA0_16],[CstOrds].[CstOrds0_17] AS [CstOrds0_17],[CstOrds].[CstOrdsCUSTOMER_NO0_18] AS [CstOrdsCUSTOMER_NO0_18],[CstOrds].[CstOrdsINVOICE_DATE0_19] AS [CstOrdsINVOICE_DATE0_19],[CstOrds].[CstOrdsREAL_SHIP_DATE0_20] AS [CstOrdsREAL_SHIP_DATE0_20],[CstOrds].[CstOrdsPDDNT0_21] AS [CstOrdsPDDNT0_21],[CstOrds].[CstOrdsDENT0_22] AS [CstOrdsDENT0_22],[CstOrds].[CstOrdsORDER_NO0_23] AS [CstOrdsORDER_NO0_23],[CstOrds].[CstOrdsLINE_NO0_24] AS [CstOrdsLINE_NO0_24],[CstOrds].[CstOrdsREL_NO0_25] AS [CstOrdsREL_NO0_25],[CstOrds].[CstOrdsCATALOG_NO0_26] AS [CstOrdsCATALOG_NO0_26],[sPart_8].[PART_NO] AS [sPartPART_NO2_0]
FROM
OPENROWSET
(
N'OraOLEDB.Oracle',
N'detest';N'bi';N'',
N'
SELECT "DISCOUNT" "CstOrdsDISCOUNT0_0","BUY_QTY_DUE" "CstOrdsBUY_QTY_DUE0_1","DESIRED_QTY" "CstOrdsDESIRED_QTY0_2","QTY_ASSIGNED" "CstOrdsQTY_ASSIGNED0_3","QTY_ON_ORDER" "CstOrdsQTY_ON_ORDER0_4","QTY_PICKED" "CstOrdsQTY_PICKED0_5","QTY_RETURNED" "CstOrdsQTY_RETURNED0_6","QTY_SHORT" "CstOrdsQTY_SHORT0_7","QTY_TO_SHIP" "CstOrdsQTY_TO_SHIP0_8","QTY_SHIPPED" "CstOrdsQTY_SHIPPED0_9","QTY_SHIPDIFF" "CstOrdsQTY_SHIPDIFF0_10","QTY_INVOICED" "CstOrdsQTY_INVOICED0_11","COST" "CstOrdsCOST0_12","NET_AMOUNT" "CstOrdsNET_AMOUNT0_13","GROSS_AMOUNT" "CstOrdsGROSS_AMOUNT0_14","SALES_PRICE_TOTAL_API" "CstOrdsSPTA0_15","TOTAL_COST_API" "CstOrdsTCA0_16",1 "CstOrds0_17","CUSTOMER_NO" "CstOrdsCUSTOMER_NO0_18","INVOICE_DATE" "CstOrdsINVOICE_DATE0_19","REAL_SHIP_DATE" "CstOrdsREAL_SHIP_DATE0_20","PROMISED_DELIVERY_DATE_NO_TIME" "CstOrdsPDDNT0_21","DATE_ENTERED_NO_TIME" "CstOrdsDENT0_22","ORDER_NO" "CstOrdsORDER_NO0_23","LINE_NO" "CstOrdsLINE_NO0_24","REL_NO" "CstOrdsREL_NO0_25","CATALOG_NO" "CstOrdsCATALOG_NO0_26"
FROM "IAL"."BP_BI_CUSTOMER_ORDER_LINE"'
)
AS [CstOrds],
OPENROWSET
(
N'OraOLEDB.Oracle',
N'detest';N'bi';N'',
N'"IAL"."SALES_PART"'
)
AS [sPart_8]
WHERE
(
(
[CstOrds].[CstOrdsCATALOG_NO0_26] = [sPart_8].[CATALOG_NO]
)
)
The error that is returned when I run the query from SQL Server Management Studio is as follows:
Msg 7357, Level 16, State 2, Line 1
Cannot process the object ""IAL"."SALES_PART"". The OLE DB provider "OraOLEDB.Oracle" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.
Note that it seems to attempt to query the table behind the referenced dimension (SALES_PART) by simply opening the table, rather than selecting something specific from it. Unfortunately this doesn't work when processing the cube, nor does it work when I paste the same query into SQL Server Management Studio. However if I modify it a little to use
N'select * from "IAL"."SALES_PART"'
instead of just
N'"IAL"."SALES_PART"'
then it runs no problem. Is there any way to control the SQL that Analysis services produces to process the cube, or is there some other way to resolve this problem so I can include dimensions with "referenced" usage? (Note that I've tried with both the Microsoft and the Oracle OLEDB providers, in both cases the behaviour & error were the same)
Thanks in advance.
Kind regards,
Miles
Miles, do you have multiple data sources involved here?
What appears to be happening is that the server detects a remote data source for the intermediate dimension and has to use remote queries to join the results together.
Some choices would be:
- Avoid the need for the remote query if possible (this would improve performance). If all your data is really coming from the same data source, then maybe something has just been improperly configured and is incorrectly causing a remote query.
- Create a linked server between the SQL Server and the Oracle server and then point to the SQL Server as the source of the tables in the DSV. This would mean credentials from the SQL Server to the Oracle server would be saved as part of the linked server.
- Change the referenced dimension to be unmaterialized (there is a check box in the dimension usage dialog for referenced dimensions). This would mean that query performance might be impacted because the join between the two dimensions would have to be done at query time instead of during processing.
- Perhaps you can change the table in the DSV to look like SELECT * FROM "IAL"."SALES_PART"
HTH,
Akshai
|||
Hi Akshai,
You're absolutely right that there were 2 data sources involved. The eventual solution was to make the primary data source for my DSV the oracle one, and then it all started behaving itself properly. As you rightly pointed out, it was doing an unnecessary remote query, tunnelling the requests to Oracle through SQL server leading to all kinds of problems. When the queries go directly to oracle, it all works fine. Thanks for the reply :)
Kind regards,
Miles
