Multi Table Pivot - Turning Excel into a Relational DB

[Anonymous]

Hello - 

I feel like I've done a good job so far in establishing tables and relationships & using bridge tables to form a nice flowing de-facto database. You can see my current tables and relationships below. The goal is for all data to be added and edited in the Transactional/Detail tables (GL, DETAIL, CONTRACT, ROSTER) and have Bridge/Helper/Lookup tables sit between to form relationships (QTable, DEALCODE etc.). The relasionships seem to be working fine, my tables are all One to Many. 

As I create simplistic pivots, just joining together attributes that are perfectly related, the pivots work fine, and aggregation works as intended.

Now I want to start doing cross-data reconciliations, ideally between DETAIL and GL, both of which have the two helper/lookup tables (ROSTER, DEALCODE) in common. Power Pivot is telling me I need some sort of relationship there, but I already have it with the lookup tables. 

Is this a CROSSFILTER() solution ? I cannot understand how to make that happen, if I could get an explanation.

I am on Excel 2016+ Desktop. I cannot share the workbook unfortunately.

All help is incredibly appreciated.

[BA_Pete · Accepted solution]

Hi @Anonymous ,

Quick answer: You can only compare measure values from each fact table along with columns from the common dimension tables, not columns from the fact tables.

Extended answer:

Without knowing exactly what you're trying to do when you get your relationship error and what the error says exactly, I can only guess that you're trying to add columns from both your SA_DETAIL and GL_DATA to your pivot at the same time. Regardless of your current relationships, the data model isn't going to be able to accurately match individual rows from these two tables to one another and hence you're getting relationship errors.

For example:

Let's say DEALCODE[Project Name] = "A". This filters both the SA_DETAIL and GL_DATA tables accordingly.

For the sake of example, let's assume that SA_DETAIL[Name] = GL_DATA[Company Name]. This gives you two filtered columns something like this:

SA_DETAIL[Name] = {"Acme Inc", "Acme Inc", "ABC Ltd", "Bubbles Corp"}

GL_DATA[Company Name] = {"Acme Inc", "Coco's", "XYZ Ltd"}

As you can see, just because these tables are related, it doesn't mean that exact row matches can be made between the tables, hence your relationship errors.

What you CAN compare between the two tables is measure values and common dimension table columns. As measures will aggregate all the values in each filtered table into scalar values, these can be compared in the same visual/pivot along with any column(s) from the common dimension tables but, as soon as you add a column from either fact table to the visual/pivot, you'll get your error again.

There's probably a bit more to it than this, but I think this is the principle at play here.

Pete

[Anonymous]

I'm actually quite close now! I've established the bones of what I'd hope to see via making a MEASURE, but how would I bring in data from the GL that is NOT present in the DETAIL table so I can perform a more comprehensive analysis; essentially instead of a LEFT join I suppose I'd want to create a full outer join?

[BA_Pete · Accepted solution]

Hi @Anonymous ,

If you want to do specific joins between the tables to get the output you want, you can use Power Query in Excel to do this.

Depending on your Excel version, you should be able to open the Power Query Editor either from here:

...or I think in older versions it sits within the "Combine Queries" option just above.

If you're going down the "Excel as a DB" route, Power Query is going to be a key tool to allow transformations on your tables (such as joins, filters, adding/removing columns etc.). It's another learning curve if you've not used it before I'm afraid, but it's incredibly powerful.

Using Power Query + Data Model/Power Pivot + Measures in Excel basically gives you Power BI capabilities, just with slightly less visually-appealing visualisations. Or you could just download Power BI Desktop if powerful and professional data handling and visualisation is something your organisation is aiming towards.

Re: The actual issue - I think I'd need a small amount of anonymised but representative example data from each table that you want to include in your manipulation, and also an example of what you'd want the output to look like. Once I can physically see what you're going for it should be much easier to understand what needs to be done, and how. However, in my experience, many-to-many joins/relationships or Full Outer joins are very rarely the solution.

Pete

[Anonymous]

I think leveraging you're insight, I was able to stumble upon this pretty good video that nicely outlines the steps here:

https://www.youtube.com/watch?v=4dGtJUc_jeU

While this solves a single calculation, I'd be all ears to hear a better, more comprehensive solution(s), if able.

Again, I'm really treating this entire workbook like a SQL DB, it is a shame that Excel hasn't quite caught on!
I'm also NOT married to Pivots as the reporting tool, I'm here for any/all suggestions!

[BA_Pete · Accepted solution]

Hi @Anonymous ,

Quick answer: You can only compare measure values from each fact table along with columns from the common dimension tables, not columns from the fact tables.

Extended answer:

Without knowing exactly what you're trying to do when you get your relationship error and what the error says exactly, I can only guess that you're trying to add columns from both your SA_DETAIL and GL_DATA to your pivot at the same time. Regardless of your current relationships, the data model isn't going to be able to accurately match individual rows from these two tables to one another and hence you're getting relationship errors.

For example:

Let's say DEALCODE[Project Name] = "A". This filters both the SA_DETAIL and GL_DATA tables accordingly.

For the sake of example, let's assume that SA_DETAIL[Name] = GL_DATA[Company Name]. This gives you two filtered columns something like this:

SA_DETAIL[Name] = {"Acme Inc", "Acme Inc", "ABC Ltd", "Bubbles Corp"}

GL_DATA[Company Name] = {"Acme Inc", "Coco's", "XYZ Ltd"}

As you can see, just because these tables are related, it doesn't mean that exact row matches can be made between the tables, hence your relationship errors.

What you CAN compare between the two tables is measure values and common dimension table columns. As measures will aggregate all the values in each filtered table into scalar values, these can be compared in the same visual/pivot along with any column(s) from the common dimension tables but, as soon as you add a column from either fact table to the visual/pivot, you'll get your error again.

There's probably a bit more to it than this, but I think this is the principle at play here.

Pete

[Anonymous]

BA_PETE - 

You are spot on; I do sincerely appreciate your assistance on this. I'm going to have to do some Googling to make sure I understand the PowerQuery Jargon you're using here (Dimension, Measure) but I may get there at some point today. 

My background and knowledge is really SQL based, so in those terms I really would just wanting to do 

INNER JOIN GL[BILLING CODE] ON DETAIL[DEAL] and

INNER JOIN GL[THIRD PARTY] ON DETAIL[V#]

Unfortunately of course, this is a many to many join, which excel will just NOT ALLOW; hence why I have the Unique ID of V# in ROSTER, and Unique ID of PROJECT in the DEALCODE table as the bridge/link tables between GL and DETAIL

If it helps, my End PIVOT would simply be 
V#, DEAL, SUM(GL[$amount]), SUM(DETAIL[$amount]), SomeCalculationForVariance on the SUMS

So if you have any ideas on how I can get there, please do share, you'll save me hours of research.

Thanks so very much.!!