Morning all!
I am building a database to house/manage wireless products. Below is a copy of my relationships in Access.
You'll notice that I have one table to store all of my device information. A table to store all of my employee information. These are joined with a custody table, which allows you to track the relationship of the device to an employee over time with the use of stop and start dates.
I am trying to build my vizs in Tableau. What I am looking for is a count of devices for current inventory. A total cost for a month over time. A total cost of devices not used over time. Individually I have the vizs built.
Here's the issue: when I try to put my tables in Tableau, Tableau is adding too many occurrence's of a device ID. For example, the database has approximately 8,200 devices. When I bring in just the table of inventory and the table of monthly costs, use an inner join, I get a return of 43,000 devices. Another approach I have tried is to build individual queries from Access that are similar in their structure, except for the specific requirements such as monthly costs, month of service etc. I have also tried to "blend" the data but that hasn't worked so well.
Can anyone give me some sort of clue as to where to begin? It makes no sense to me why Tableau does this when I have followed the rules of normalization in Access, and I am using those primary and foreign keys in Tableau and get such horrible results.
If you do select count(*) from tblPhoneInv , I assume you get 8200 records.
If you do ;
SELECT count(*)
from tblCustody c
join tblPhoneInv p on c.deviceId = p.deviceId
I'd be willing to bet you get the full 43k records.
Tableau isn't importing these as individual little tables, it makes everything into one big flat table.
So if you have 3 records in tblPhoneInv for the same device Id, you will get three records in Tableau.
Using Level of Detail calculations for anything metric based is a good idea - Removing Duplicate Data with LOD Calculations | Tableau Software
Take a look at the 'Troubleshooting Joins' section here for a further explanation -
