Join FACT table with Direct Query table

[#1]

Hi all,

Can i join a normal in memory fact table with a DIRECT query table? Of course i will not une any synthetic keys.

What i am trying to achieve is have a grouped table (monthly grain) and a DIRECT query table for the details.

this is the code i am using

Right now it is not working.  (direct query failed, and i see nothing in the DB using profiler).

det:

load *,autonumber(SalesPersonID & CustomerID & OrderMonth & OrderYear) as grpID2

;

DIRECT QUERY

DIMENSION

CustomerID,

SalesPersonID,

SalesOrderID,

OrderDate,

NATIVE('month([OrderDate])') as OrderMonth,

NATIVE('Year([OrderDate])') as OrderYear

MEASURE

SubTotal,

TaxAmt,

TotalDue

Detail

DueDate,

ShipDate,

AccountNumber,

CreditCardApprovalCode,

rowguid,

ModifiedDate

FROM [Sales].[SalesOrderHeader];

grp:

select

  month([OrderDate]) as OrderMonth,

  Year([OrderDate]) as OrderYear,

  CustomerID,

  SalesPersonID,

  sum(SubTotal) as SubTotalGrouped,

  sum(TaxAmt) as TaxAmtGrouped,

  sum(TotalDue) as TotalDueGrouped

FROM [Sales].[SalesOrderHeader]

group by

  month([OrderDate]),

  Year([OrderDate]),

  CustomerID,

  SalesPersonID;

// intermediate table to join facts

cnt:

Load distinct

CustomerID,

OrderMonth,

OrderYear,

SalesPersonID,

autonumber(SalesPersonID & CustomerID & OrderMonth & OrderYear) as grpID

resident grp;

concatenate

Load distinct

CustomerID,

OrderMonth,

OrderYear,

SalesPersonID,

autonumber(SalesPersonID & CustomerID & OrderMonth & OrderYear) as grpID2

resident det;

drop field CustomerID from det;

drop field OrderMonth from det;

drop field OrderYear from det;

drop field SalesPersonID from det;

grp2:

Load

  autonumber(SalesPersonID &CustomerID & OrderMonth & OrderYear) as grpID,

  SubTotalGrouped,

  TaxAmtGrouped,

  TotalDueGrouped

resident grp;

drop table grp;

[robert_mika]

Did you establish your connection to ODBC?

It works for me

[sujeetsingh]

Need a sample please

[robert_mika]

I think the sample is the AdvetureWorks script provided..