This is my first time using Power Pivot with live data from my company database. We are a non-profit using an association management database with custom built user defined tables to handle data about the clients we serve. Our UD tables are definitely not designed for ease of analysis, they are more of a dumping ground for every possible piece of information we might need to track about our clients
I am pulling raw data into Power Query from SQL database.
Each time an individual applies for our services, we generate an Application table. Each individual application will either be updated with an accepted date or a deactivated date, with a separate validated field describing the reason for deactivation. Further, the application is broken up into 4 phases and we capture the date that an application enters each phase.
In addition to analyzing the ultimate outcome for the applications, I also want to be looking at timelines for completion of each phase of the application so that we can identify and reduce friction points in the application process.
Since I have a single Application table that contains all the fields of interest and a large number of applicants will have a blank date in at least one of the fields of interest, I'm not entirely sure how to organize the information for analysis. My original thought is that I will need to do something like this:
1) Create a Fact table from Application that includes client ID, and outcome of application
2) Create a Dim_Phase1 table from Application that includes only those records with a date in the Phase 1 field including Phase 1 date
3) Create a Dim_DeactPhase1 table from Application that includes only those records that were deactivated in Phase 1, including reason for deactivation
4)Create a Dim_Phase2 table from Application that includes only those records with a date in the Phase 2 field, including Phase 2 date
5) Create a Dim_DeactPhase2 table from Application that includes only those records that were deactivated in Phase 2, including reason for deactivation
...repeat for all phases
Is there a smarter/more efficient way to organize the data? I can't think of another way to avoid columns with blank dates.
Thanks for your thoughts!
Hi Grete,
It's difficult to say without seeing some sample data, but my initial thought is you have too many tables. Dim tables are for data that is static e.g. customer addresses, dates of birth etc. Data that you'd want to summarise your facts by.
It sounds to me like the date information should be in your fact tables. If you want to compare phase data, then you shouldn't have separate tables for each phase.
However, to be sure I would need to see a mock up of your data and an example of the reports you'd want to generate from that sample data.
Mynda
Thanks much for your prompt response. I have some travel coming up and will attach a sample data set and report mock-up when I return.
Actually, here is a very quick sample of the data we collect and the types of questions I want to answer.
As you can see, an applicant has multiple phases through which he or she must proceed, and the application process can cease at any time. This leaves lots of blank dates in my output. Also, an applicant can be both accepted to our program and then later withdraw or be denied before receiving services.
Hi Grete,
Thanks for sharing the file and the analysis you want to do. Some of the measures are self explanatory and relatively straight forward, but some require more details. e.g.:
Trend duration of Phase 1 - Phase 2 processing, what date are you counting this data based on, the pre-app date, phase 1 date or phase 2 date? And so on for the other trend duration measures.
It would be helpful if you can generate a mock-up PivotTable that summarises the results for one of these trend duration measures so I can see how the data is related and check results of any measures that I test.
I also need an example result for this measure: For each deactivation type, what is the frequency of occurrence in each phase?
Thanks,
Mynda
Thank you so much, i realized my own error. I was still stuck in traditional pivot table mode, when in fact I can create Dax measures that can then be analyzed like a table column. Now that I'm actually using the tools that your class covered, I'm good to go 🙂
