Log In
Register
Home
Power Pivot
September 2, 2020
Offline
We want to present data stored in a number of SharePoint lists by means of Power Pivots.
We have to deal with hierarchies and m-n-relations (for example: Products - ProductCompetencies - Competencies); they are in separate lists.
I have created the queries to get the list data into our data model, including the 'in between' list,
and I created relationships between the tables.
But still I cannot get the correct Power Pivot.
Is that only possible by means of Dimension tables, or is the ProductCompetencies-table treated automatically as one?
Or is it possible to change the ProductCompetencies-table into a Dimension table?
See Answer
July 16, 2010
Offline
Hi Maarten,
I can't tell from the image which is the fact table, but based on the direction of relationships in the image attached it appears there are many issues. Keep in mind that dim tables filter fact tables. So, the arrow on the connector line should be pointing from the dim table towards the fact table. Also, the dim table should have the 1 side of the relationship and the fact table typically has the many side.
You might want to simplify your dim tables by having a single dim to fact table step, rather than dim to dim to fact.
I hope that points you in the right direction. If you're still stuck, then I'll need a lot more information about the model, what the tables represent and ideally a copy of the file.
Mynda
Power Pivot
September 2, 2020
Offline
Hi Mynda,
I've tried quite a few things, but not with a satisfying solution.
So I attached a file with part of our data (different m-n-relationships).
In the first worksheet I made a skecth of what we have, and what we want.
Some of those are working in the Dashboard-sheet, but I cannot manage to filter the pivot table (center) by means of the slicers.
I don't get the picture where to select the correct slicers and filters for the pivot.
Maybe you can help me out.
July 16, 2010
Offline
Hi Maarten,
Thanks for sharing the file.
The image in your Intro sheet has the dim and fact labels back to front. Looking at your model, the dim tables are Proposities, Producten and Kennisgebieden and the fact tables are PropProd, ProductKennis and SengKennis.
PivotTable DTKennis is trying to filter the Kennisgebied, Niveau and Sengineer fields from the SengKennis table by Product, Proposite and Rol, but Product, Proposite and Rol do not have relationships with the SengKennis table, which is why the Slicers don't do anything for this PivotTable.
I have a feeling you have split your data into these separate tables for the purpose of creating a Power Pivot model. Was this data in one large table/s prior to you trying to build your Power Pivot model?
Mynda
Power Pivot
September 2, 2020
Offline
Hi Mynda,
OK, I abusively put the texts Fact and Dim in my Intro the wrong way;
I think in the Model they are correct.
What I hoped was that by 'connecting' all data through Dim tables the Pivot Table could be filtered.
All these tables have been defined as lists in a SharePoint site, and with a pure hierarchical relational database background.
What would be the best solution, flattening the model by bringing more columns into the tables by looking up?
And another question: are these kind of questions dealt with in your course(s)?
I'm considering starting with it but don't know for sure, yet.
Kind regards,
Maarten
July 16, 2010
Offline
Hi Maarten,
It's difficult to say definitely that the relationships are the right way around in the model because your fact tables aren't obvious fact tables. Normally fact tables will contain values and or dates that represent records or transactions. The SengKennis table is the only table with numeric data, but even then it's possible this is a dim table, and since I don't speak Dutch I'm struggling to draw conclusions from the data that might be obvious 🙂
All the data isn't connected. Even though you technically have relationships from one table to the next, this doesn't mean any table can filter any other table. Currently you have the following filter relationships:
- PropProd Table can be filtered by Proposites and Producten
- ProdutKennisgebieden Table can be filtered by Producten and Kennisgebieden
- SengKennis Table can be filtered by Kennisgebieden
I think you would get a huge amount out of taking my Power Pivot course. It will get you up and running much faster than to-ing and fro-ing like this in the forum, because it's very difficult to teach the fundamentals on the fly like this. You're only at the modelling stage of Power Pivot. There's a whole lot more to learn with DAX measures etc. and you can't get them right if your model isn't set up correctly, so getting these fundamentals right is key.
Let me know if you have any other question.
Mynda
Answers Post
1 Guest(s)
