Multiple related fact tables

• Rasmus Remmer Bielidt

  SSCommitted

  Points: 1767

  More actions

  December 15, 2014 at 3:12 am

  #313076

  Hi,

  Trying to get my head around the design for the following dimensional structure.

  I have one fact table (factCustomerTransactions) which holds all transactions (invoices, payments, interest etc.).

  I have another fact table (factCustomerSettlements) which holds information concerning how each factCustomerTransaction was settled and the date on which this occured.

  Ideally, what I need is the possiblity for the end user to pick a date dimension member and see the unsettled amount for each factCustomerTransaction.

  factCustomerTransactions table definition (truncated):

  CREATE TABLE [Dwh].[factCustomerTransactions](

  [ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,

  [COMPANYID] [int] NOT NULL, -- FK for Company dimension

  [CUSTOMERID] [int] NOT NULL, -- FK for Customer dimension

  [DATASET] [varchar](3) NOT NULL, -- businessPK

  [ROWNUMBER] [int] NOT NULL, -- businessPK

  [TRANSTYPE] [int] NOT NULL,

  [VOUCHER] [int] NOT NULL,

  [AMOUNTEXCH] [numeric](28, 12) NOT NULL,

  [AMOUNTMST] [numeric](28, 12) NOT NULL,

  [TRANSDATEKEY] [int] NOT NULL,

  [DUEDATEKEY] [int] NOT NULL,

  [LASTCHANGED] [datetime] NOT NULL -- for incremental loading

  ) ON [PRIMARY]

  ALTER TABLE [Dwh].[factCustomerTransactions] WITH CHECK ADD CONSTRAINT [FK_factCustomerTransactions_dimCompany] FOREIGN KEY([COMPANYID])

  REFERENCES [Dwh].[dimCompany] ([ID])

  ALTER TABLE [Dwh].[factCustomerTransactions] WITH CHECK ADD CONSTRAINT [FK_factCustomerTransactions_dimCustomer] FOREIGN KEY([CUSTOMERID])

  REFERENCES [Dwh].[dimCustomer] ([ID])

  factCustomerSettlements table definition:

  CREATE TABLE [Dwh].[factCustomerSettlements](

  [ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,

  [TRANSACTIONID] [int] NOT NULL, -- surrogate FK (references factCustomerTransactions)

  [DATASET] [varchar](3) NOT NULL, -- businessPK / businessFK - used to populate TRANSACTIONID

  [ROWNUMBER] [int] NOT NULL, -- businessPK

  [TRANSRECID] [int] NOT NULL, -- business FK - used to populate TRANSACTIONID

  [SETOFFTRANSVOUCHER] [int] NOT NULL,

  [SETTLEAMOUNTMST] [numeric](28, 12) NOT NULL,

  [SETTLEAMOUNTEXCH] [numeric](28, 12) NOT NULL,

  [TRANSDATEKEY] [int] NOT NULL,

  [LASTCHANGED] [datetime] NOT NULL

  ) ON [PRIMARY]

  ALTER TABLE [Dwh].[factCustomerSettlements] WITH CHECK ADD CONSTRAINT [FK_factDebsettlement_factCustomerTransactions] FOREIGN KEY([TRANSACTIONID])

  REFERENCES [Dwh].[factCustomerTransactions] ([ID])

  Now, my question is how to model this in Analysis services.

  What I currently have done is to create a cube with the factCustomerTransactions as a measure group and I have created a degenerate dimension on the ID of factCustomerTransactions. Then I have added a second measure group using factCustomerSettlements using the TRANSACTIONID FK in the factCustomerSettlements table to relate to the degenerate dimension in the factCustomerTransactions table.

  In essence, the factCustomerTransactions table becomes a kind of dimension table for factCustomerSettelements.

  All this compiles without any errors, however, the result is not as wished. What happens is that no matter what query I issue including measures from both measure groups will return the total sum from the factCustomerSettlements table for each dimension.