Advice needed on designing schema to accomodate multiple transaction tables.


  • Hi,

    The attached images shows my current schema. It consists of three transaction tables, a product table and a calendar table.

    - Background -

    The product table 'Q1 Data Set' contains all unique sales. In addition it also contains a number of columns by which I will later filter my pivot tables (e.g. whether the customer of the order is new/returning). This table also contains a column named 'DateOrdered',the date the order was originally placed (but not paid). 

    Each sale that is paid can be done so either in a single transaction, or across multiple transactions of different transaction types.

    An example of a sale  paid in multiple parts would be an order that has three transactions; one online (table 'trans_sagepay', one over the phone (table'trans_epdq') and another by card (table'trans_manual'). Furthermore there can be more than one transaction of each type for an sale.

    I have created measures which total the sales in each transaction table. Each transaction has a 'transaction_date' which is the date of that individual transaction.

    The calendar is simply a date table that has some friendly formatted columns for laying out pivot tables. An example column is FiscalMonthAbbrv which displays months similar to '(04) - January' to accommodate our fiscal year.

    - Problem -
    My problem is that I need the ability to create some tables that have the Date Ordered as the rows (listed by Year>Month), and I need to produce other tables that have Transaction Date as the rows.  

    Date ordered works fine, however the problem comes when I try and create a table based on the transaction date.

    With the current model seen in the attached image I cannot do it because the transactions have a relationship to Q1 Data Set and this table has the relationship with the Cal_Trans table. What happens in this scenario is that whenever I set the rows to be FiscalMonthAbbr  the values it displays is the transactions based not on transaction date but date ordered. To explain further:

    If I have an order A with a DateOrdered of 01/01/2014, but the transaction of £100 for that order was made later on the 05/01/2014, that £100 is incorrectly attributed to the 01/01/2014.

    To clarify the type of table I am aiming for see the mock-up below, I however NEED the ability to filter this table using columns found in Q1 Data Set.

    How can I make a schema so that I can use both DateOrdered and TransactionDate? I cannot combine all three transaction tables into one because each transaction type has columns unique to that specific type.

    • Edited by Maracles Tuesday, February 11, 2014 1:49 PM Added table.
    Tuesday, February 11, 2014 1:41 PM

All replies

  • You need to have relationships from trans_epdq TransactionDate to Call_Trans TransDate column  to make sure you can correctly associate transaction record to date based on transdate value.

    Please Mark This As Answer if it helps to solve the issue Visakh ----------------------------

    Tuesday, February 11, 2014 1:49 PM
  • Thanks Visakh, I tried this also, however when I do this and then relatethe individual transaction tables to the product table I could show transaction values against transaction date, but I could not filter these by the columns found in the product table.

    In the way above I can filter the tables with columns contains in the product table, however I cannot show transaction values against transaction date.

    Is there a way to do both?


    To clarify; I am trying to build three usable relationships that will be active simultaneously.

    Transactions Tables -> Date Calendar

    Product Tables -> Date Calendar

    Transaction Tables -> Product Table

    • Edited by Maracles Tuesday, February 11, 2014 1:57 PM Clarity
    Tuesday, February 11, 2014 1:52 PM
  • Have you considered using two date tables? Basically if I understand correctly your date table plays two different roles - one to filter order dates and another for transaction dates.

    Tuesday, February 11, 2014 5:03 PM
  • Sorry didnt get that. can you explain with an example. what happens when you try to slice using a column in product table?

    Please Mark This As Answer if it helps to solve the issue Visakh ----------------------------

    Tuesday, February 11, 2014 5:22 PM
  • Slicing on product columns should still filter down the transactions due to the relationship between product and transactions. Furthermore transactions would be filtered by the new date table that is related to the date fields of the transactions tables.

    Maracles would it be possible that you upload that data model (without any sensitive data)?

    Tuesday, February 11, 2014 6:02 PM
  • Thanks for your suggestions, at the moment I don't have time to prepare a non-confidential copy of the data model, however I've taken one step forward, and one step back!

    First to clarify; to calculate sales of each transaction type I have created the following measures (I've given them friendly names):




    I then have a measure called rev_total which sums together the above measures. This allows me to calculate total revenue, but also to break it down by transaction type.

    With this in mind I revised the schema based on Visakh original suggestion to look like this:

    Using this I was able to produce a table which looked like that below:

    There were two issues with this:

    • If I add the individual measures for each transaction type I get no errors, as soon as I add the 'Total Sales' measure on the end of the table I get an error "Relationship between tables may be needed". Seemingly however the numbers still calculate as expected - what is causing this error and how do I remove it?
    • I CAN with this scenario filter by 'phd' which is a column in the Q1 Data Set table and it works as expected. I cannot however filter by all columns in this table, an example would be 'word count'.

      'Word Count' is a integer column, each record in the Q1 Data Set table has a value set for this column.

      I would like to take the column above and add a new measure called 'Total Word Count' (which I have created) which will calculate the total number of words in that monthly period. When I add this however I get the same relationship error as above and it display the word count total for the entire source tbale for every row of the pivot table.

      How can I get this schema working so that I can filter by word count and other columns from the product table. It Is confusing me how I can filter by one column, but not by a another in the same table.

    Also, I don't fully understand how I would add a second date table or how it would help my issues.

    Thanks very much for you help.

    Tuesday, February 11, 2014 9:06 PM