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;
