Terese
Yes, you're understanding the problem correctly. Yes, the SalesRep dimension is flagged to be materialized.
Here is the SQL
SELECT [dbo_vw_OrdersAndSalesDetailFact].[ICAndExtExtendedPrice] AS [dbo_vw_OrdersAndSalesDetailFactICAndExtExtendedPrice0_0],[dbo_vw_OrdersAndSalesDetailFact].[ICAndExtOrderQtyInRptUOM] AS [dbo_vw_OrdersAndSalesDetailFactICAndExtOrderQtyInRptUOM0_1],[dbo_vw_OrdersAndSalesDetailFact].[SoldToAccountId] AS [dbo_vw_OrdersAndSalesDetailFactSoldToAccountId0_2],[dbo_vw_OMSBPCustomer_2].[PrimarySalesRepAccountID] AS [dbo_vw_OMSBPCustomerPrimarySalesRepAccountID2_0]
FROM [dbo].[vw_OrdersAndSalesDetailFact] AS [dbo_vw_OrdersAndSalesDetailFact],
OPENROWSET
(
N'SQLNCLI.1',
N'',
N'[dbo].[vw_OMSBPCustomer]'
)
AS [dbo_vw_OMSBPCustomer_2]
WHERE
(
(
[dbo_vw_OrdersAndSalesDetailFact].[SoldToAccountId] = [dbo_vw_OMSBPCustomer_2].[CustomerNumber]
)
)
Now if I take this exact SQL and run it in query analyzer I get basically the same error:
Msg 7430, Level 16, State 3, Line 1
Out-of-process use of OLE DB provider "SQLNCLI.1" with SQL Server is not supported.
If I remove the OPENROWSET section as below
SELECT
[dbo_vw_OrdersAndSalesDetailFact].[ICAndExtExtendedPrice]
AS
[dbo_vw_OrdersAndSalesDetailFactICAndExtExtendedPrice0_0],
[dbo_vw_OrdersAndSalesDetailFact]
.[ICAndExtOrderQtyInRptUOM]
AS
[dbo_vw_OrdersAndSalesDetailFactICAndExtOrderQtyInRptUOM0_1],
[dbo_vw_OrdersAndSalesDetailFact]
.[SoldToAccountId]
AS
[dbo_vw_OrdersAndSalesDetailFactSoldToAccountId0_2],
[dbo_vw_OMSBPCustomer_2]
.[PrimarySalesRepAccountID] AS
[dbo_vw_OMSBPCustomerPrimarySalesRepAccountID2_0]
FROM [dbo].[vw_OrdersAndSalesDetailFact] AS [dbo_vw_OrdersAndSalesDetailFact],
-- OPENROWSET
-- (
-- N'SQLNCLI.1',
-- N'',
-- N'[dbo].[vw_OMSBPCustomer]'
-- )
--
[omswrite]
.[dbo].[vw_OMSBPCustomer] AS [dbo_vw_OMSBPCustomer_2] ****different database here
WHERE
(
(
[dbo_vw_OrdersAndSalesDetailFact]
.[SoldToAccountId] = [dbo_vw_OMSBPCustomer_2].[CustomerNumber]
)
)
The customer table and the fact table are in different databases, not sure if that is causing some problems, but don't think it should.