Hi Andy
This is an annotation of your diagram, identifying the roles of your tables as interpreted by the query engine.
As you can see you have 3 dimensions and 2 fact tables. The dimensions are Part cost, Inv Locations, and product line/Part master.
The facts are Inventory bal and and trans history.
I think this is a problem as I believe that you want trans history in the product line/Part master dimension.
It is not clear where your 4 columns come from but I suspect that some are coming from trans history and I think that is causing havoc.
If you want to include the transaction history in your product line/Part master dimension then you will need to model that dimension so that the relationship between it and the inventory balance fact table goes through the necessary keys of the transaction history table.
Normally during the process of designing your data warehouse and ETL, this sort of situation will be identified and a mapping between the transactional schema data base and a data warehouse schema, which is better suited for report query purposes, would be done.
You could try to model this with the existing data base structure. This might require some modelling of additional columns in the query subjects to generate the required keys in both tables. I don't know if there exists information which could be used to do that modelling.
For example, I am able to fairly easily model time windows in relationships between a dimension table and a fact table in the Cognos great outdoors warehouse sample data base because the keys for time are in the structure yyyymmdd and can be substringed, concatenated with the necessary '-' symbols and that string then cast into a date, which can be the key to a join to the dimension table, where a date (employee record start date or employee record end date for example) would exist. I can use the same method to create a date column in a fact table, which I can use as the lookup reference to a calendar so that I can set up relative time measures. The data which defines date keys in your facts might not be so easy to deal with.
------------------------------
IAN HENDERSON
------------------------------
Original Message:
Sent: Thu March 04, 2021 01:59 PM
From: Andy Wilson
Subject: Rows on Report not Matching DB
Correction
- Location Master = Inventory Balance = Location, (To pull descriptions only, no calculations), (right outer Join, 1 to many, No Filtering)
------------------------------
Andy Wilson
------------------------------
Original Message:
Sent: Thu March 04, 2021 01:56 PM
From: Andy Wilson
Subject: Rows on Report not Matching DB
Sorry for the late response Henk. Thank you,.
Relationships are;
- Part Master to Trans History = Part Number, (Inner Join, 1 to many, No Filtering)
- Part Master to Inventory Balance = Part Number, (Right outer join, 1 to many, No Filtering)
- Part Master to Product line = Product line #, (To pull descriptions only, no calculations), (Inner Join, 1 to many, No Filtering)
- Part Cost to Inventory Balance = Part Number, (Inner Join, 1 to many, No Filtering)
- Location Master = Inventory Balance = Location, (To pull descriptions only, no calculations), (Inner Join, 1 to many, No Filtering)
This is a data module. The Inventory Balance table has multiple occurrences of the part due to the part being in several locations.
The items below usage is actually 1,844,700
------------------------------
Andy Wilson
Original Message:
Sent: Thu February 25, 2021 02:34 PM
From: HENK CAZEMIER
Subject: Rows on Report not Matching DB
Hi Andy,
Would you be able share a pictorial of the tables + relationships that you're modeling?
Are you using a Data Module or an Framework Manager model ?
// Henk
------------------------------
HENK CAZEMIER