Forum Discussion
variables and tables or columns
Many thanks for that AlbertoFerrari
I have heard several of your sessions and will try to get your book. I actually did purchase your initial book "Excel 2013 Building Data Models with PowerPivot". So I am very aware of the amazing work you and Mark do.
I will look into your trainings/mentorship/consultation.
In general what would be your advise as in ideal steps in setting up models for cases like these where the fact table (currently just the one called Epi) is 550k rows x 100 columns - should I split it into 3-4 smaller fact tables like conditions, investigations, intervention dates and patient details all lonked via the same primary key such as patient ID??? And have all smaller filter tables like geography, time, age all linked to all the patient ID column of all these smaller fact tables??? That way I will be able to pivot columns and hence not have as many columns as you have suggested
Currently I have the typical star schema as follows
Abhijeet
PS: Would you revommend a mentoring arrangement with SQLBI or just the gebric trainibgs that you guys have?
Hi,
It is very hard to give you any advice without analyzing much deeper your requirements, sorry for that but whatever I tell you would likely be a wrong answer.
As a general idea, for the kind of queries that I think you are trying, I would go much farther in normalizing the model.
Instead of having 100 columns, I think you can end up with a small set of columns (patient, date, doctor, hospital and stuff like this) to identify the event (a visit, perhaps) and then another table where your 100 columns become 100 rows and the column names become a new "Symptom" dimension.
So, something like:
Patient Date Symptom Measure Alberto 09/11 Headache YES Alberto 09/11 Pain YES Alberto 09/11 Dead NO
This lets you compute questions like: give the percentage of patients that had a headache and were not dead, or show me the patients without a headache, with pain and died. or any combination of AND, OR and NOT conditions. The code is not easy to write, but can be very generic and driven by the user through slicers.
Of course, the size of the table increases by a lot, but this is not an issue at all, a few hundreds of millions of rows are not scary at all.
Alberto