3 Replies Latest reply: Oct 19, 2015 3:24 AM by Robert Mika RSS

    Join FACT table with Direct Query table

    george kastros

      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;