Mike, thanks again for your help. This is my first attempt at using the Excel Data Model (having part-read Rob Collie's DAX Formulas for PowerPivot Book!) and I really appreciate the advice.
I'm now going to write a full 'brief' and the method I am going to use to tackle the problem, not in the expectation of you doing all the work but so that any suggestions you make won't need to be re-worked later. As a developer myself I know that there
is nothing more annoying that not getting the full picture early on, because it tends to mean lots of changes and re-writes along the way as.
A picture tells a thousand words so I've given you another fuller view of the tables I have at my disposal, which I hope will be helpful.
And so to the explanations.
It is a one-to-many relationship between project and project financials.
Each project has one row in Project and many rows in Project Financials (one row per month).
The Calendar table is as you would expect a date table.
The Project Role table is the table that holds the full list of employees. Each Project has two rows, one for its project director and one for its project manager.
The Project Delivery table has many rows for each project (one for each month).
The Aged Debt table has eight rows per project and these correspond to eight debt descriptions in the Aged Debt table. Essentially each project has its debt split across eight bands depending on how old the debt is.
The ExchangeRate holds currency exchanges rates for 39 currencies and Currency table holds the names of those currencies - linked by a currency key column.
The data that I am interested in is for the 'current period', the 'previous period' and the twelve periods before that. At the moment I have manually adjusted the Project Financials, Project Delivery and Calendars table properties so that they only bring
through the required periods of information:
WHERE (([ReportCalendarDayKey] = 20130501) OR ([ReportCalendarDayKey] = 20130601) OR ([ReportCalendarDayKey] = 20130701) OR ([ReportCalendarDayKey] = 20130801) OR ([ReportCalendarDayKey] = 20130901) OR ([ReportCalendarDayKey] = 20131001) OR ([ReportCalendarDayKey]
= 20131101) OR ([ReportCalendarDayKey] = 20131201) OR ([ReportCalendarDayKey] = 20140101) OR ([ReportCalendarDayKey] = 20140201) OR ([ReportCalendarDayKey] = 20140301) OR ([ReportCalendarDayKey] = 20140401) OR ([ReportCalendarDayKey] = 20140501) OR ([ReportCalendarDayKey]
= 20140601))
I know this is clunky and it will need to be automated at some point using the CurrentFiscalPeriod and CurrentFiscalYear values.
Question: Will I be able to restrict the data imported into the Project Financials and Project Delivery table based on information in the Calendar table (which is the only place where the CurrentFiscalPeriod and CurrentFiscalYear values
are stored?
Starting at the end
The finished product we are trying to produce will be something like this. This is not 'set in stone' but when I drew this it was the only way I could see us achieving what we need to achieve (however that was before I found out how powerful
DAX can be).
Current Methodology
The way that I currently am doing things is by creating three pivots that look up the three fact tables (delivery, debt and financial) based on the slicer choices. I then use Excel to create some 'measures' and finally I use some lookups
to display that information on a 'Dashboard' presentation sheet. I came up with this methodology before reading about DAX and it might be that I can now create the measures using DAX and avoid the need bring through the information and then 'mash' it
in Excel - time will tell but I am hopeful.
The Requirements
What we are looking to do is create a 'dashboard' showing information about projects that belong to our employees.
The dashboard will show KPIs for six aspects of the project. There will also then be an option to go to a 'single project' page that will show more detail when just one project is selected. As an example on the dashboard page we show the worst debt category
and its amount, on the single project page we will show the eight debt 'age bands' and the amounts of debt for each age band.
The list of projects needs to be filtered by Employee but because that list is very long (47,000 entires) we want to be able to shorten it by choosing a Unit/Division/Subdivision/Project Status first.
Clicking on currency will adjust the values to the chosen currency. This I envisage doing by using a disconnected table 'lookup' that will multiply the values by the chosen exchange rate.
Question - Long List of Names: rather than use the Project Role table that has all PMs/PDs since the beginning of time would it make sense/be possible to create a table on the fly from the list of names in either the Project of the
Project Financials table? I have analysed the columns and got some numbers to help you come to an educated opinion.
Project Financials = 190,000 rows, two columns PD and PM, 4281 unique values.
Project = 17,500 rows, two columns PD and PM, 4301 unique values.
Project Role = 48,000 rows, one column, 4821 unique values.
As per the post above - at the moment Project Role is 'talking' to the Project Table, is this the most efficient way to do this?
The challenges (for me)
Is my approach right? Should I be using three pivots or should I look to create the measures using DAX (it's funny but just by writing this email I already think I know the answer!) and get them all on one pivot table.
Assuming that the answer is to create the measures in DAX and then put them all onto one pivot table I might (for 'might' read 'almost certainly') need your help - but I will attempt to do it myself first and then perhaps get you to 'peer review' my
code - if that is OK?
My plan of action
I think that the things that I need to iron out/do are these:
1 - Ascertain the most efficient way of meeting the requirements. i.e. is it by creating one pivot table that brings through the project data and the KPIs and then creating two 'front-end' presentation tabs, one for the KPIs and one for Single Project
analysis. Or should I create one pivot table that shows the KPIs on a 'dashboard' tab and another pivot table (or maybe multiple pivot tables/charts) that shows the single project information on a 'single project' tab.
2 - Ascertain the most efficient way of doing the employee lookup.
3 - Create measures for my 'KPIs' using DAX.
How you can help
If I have given you enough to go on I would really value your opinion on points 1 and 2 in my plan of action. I think that getting this right will be crucial to the speed that the workbook runs at (and whether or not I can meet all the requirements) -
in my investigations I have quickly found that adding columns/slicers etc can suddenly slow things down beyond being useable.
Obviously later on there will be more questions relating to restricting the import data to just the right periods and my DAX measures formulas but they I think are 'small beer' compared to the 'architectural' questions.
I'm sorry if this post is to long but as I said at the top if you are to make intelligent suggestions then you need to see the whole picture.
In anticipation of your help many thanks again,
Paul
-
Edited by
Paul Trower
Thursday, June 05, 2014 10:22 AM
Re-added second picture that wasn't showing