Hi Brian
Thank you for your feedback and recommendations. The basic model ideas were really useful and you already have me thinking about how to structure my model. I will watch and read the provided links and then design my model and post it here for review and suggestions on improving the model further
One advantage I have is that once I have designed the model I can reuse it for each subsequent assessment
Will post my model early next week
Cheers
Andrew
Hi Mohammed
Thank you for your input and you are right I need to invest time in designing the model but I think I need to learn a bit more as a next critical step
Appreciate your interest and hopefully I can post a design later next week
Cheers
Andrew
Hello @Ducati,
I would keep all the Excel files in a folder and connect with Power Query from Power BI to that folder and “read” all the data.
Then I would combine all the files with the same structure and do the Data Model. The main advantage of Power Query over normal Excel is that you can really transform data (and I’m thinking here of UNPIVOT) and build a loooong and narrow Data (Fact) Table and several Lookup (Dimensions) tables . Then try to stick to a Star Schema Model so that the DAX measure stay simple.
Should you have two sample files I’ll be glad to try something to help
Hi @Ducati, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!
Hello Cristian
Thank you for your reply. I am not quite sure I follow you completely. In Excel I have around 50 tables that are used for the assessment analysis but there is quite a lot of duplication in the tables eg age, gender etc.
I am not sure how to construct one long table? Or quite sure what a star scheme is but the example above by Brian I can do and am working on that now.
I can create the dimension tables for example - age, gender, disability, living alone - these would be in (a) the respondent’s profile table, I could also create (b) location table with location, type of displacement eg refugee, IDP, Host, returnee etc and © assistive aid (product) table.
We also check for access to services eg health, water, shelter, food etc - would this be another lookup table?
We also ask older people about their priorities for assistance. We have 11 things eg water, shelter, food, health, cash, safety etc. For each they are asked to rank on a scale of 0 to 5 then we can calculate the priority ranking for older men, older women, older men with disability, older women with disability, older men living alone and older women living alone. So I can see how age, gender, disability and “alone” would be “slicers” and this latter info would be in the “respondents table” but can or how do I put “priorities” into a lookup table?
I have a sample EXCEL table that I can post (just got to figure out how to post a file - there is help so I will read it then post a sample table
Thanks for all the help
ALONE FOOD with DISABILITY access meals hungry nights NEW V2 Colombia 2020.xlsx (26.7 KB)
I hope I have done this correctly but this is an example table where we calculate the following for older people affected by a disaster:
How many meals per day they are eating
How often they go hungry at night
Barriers that they face in accessing food
This particular table looks at older people who live alone compared to older people who live alone with disability (so Alone and disability are filters and we also filter for age and gender) - I can see how we can use PowerBI to apply age, gender, alone and disability as slicers in a single chart
There is another almost identical table that looks at the whole sample of older people (without the Alone filter applied) and compares older people to older people with disability
So ideally I am looking to compile one chart in Power BI that looks at this topic - food but where I can filter age, gender, living alone and disability - this helps me understand how having a disability or living alone affects access to food.
I can also apply age and gender filters to see if there is any discrimination or disparity with respect to age or gender - this helps us to target our assistance better so we get the right relief to the right people at the right time.
Hope this makes sense?
Andrew
Hi Andrew,
Checking the file you uploaded it seems this one is already having some groupings / counting / calculations.
When I said about having the Excel files in the same folder and get their data with Power Query I was thinking of the RAW DATA Files from the surveys (XLSX or CSVs).
I wanted to showcase a small report and found a sample Raw Data Survey file on data.world (https://data.world/browning/capmetro-smart-trips-questionaire).
Even if I got only one file and the process is more or less the same: from 76 initial columns I got only 6 in the Data Model through UnPivot. Getting the dimensions created from Raw Data it got me to a “Star Shema Model” .
In the file I got I made no Explicit Measure but that is the way to go should you wand a trully dynamic report. Check out the GIF below and let me know should this is what you were looking for.
Survey Results Sample.7z (3.8 MB) .
Regards,
Cristian
Dear Cristian
Thank you for your reply and apologies I misunderstood the file you required was for the raw data. I will, upload the raw data file on this reply.
I could not open the file that you posted non of my applications can open the file? Is it possible to have the file in PDF?
I have been working on a data model and have about eight lookup tables and then I would have twelve data tables one for each sector such as Health, Water, Food etc
I have been continuing to study the course material and I have a better understanding of Key measures, measure branching etc but still struggling a bit with the data model.
Below is the raw data for our assessment in Colombia of older Venezuelan men and women who have migrated to Colombia
Guajira RNA - cleaned data .xlsx (428.7 KB)
Thank you for your assistance I really appreciate your time and effort
Andrew
Hello Andrew,
The file I uploaded is actually a compressed GIF file than needs to be un-compressed before opening it.
Unfortunately there’s a 4MB limit for the upload and even if I changed it to ZIP it’s still 4.1 MB ;).
I also uploaded the file here also for you to take a look and let me know should this kind of report is what you need .
I’ll have a look on the Raw Data File and today and I’ll show a POC as soon as I have one.
Cristian
Great Thanks Cristian
I have worked on the data model but I am not sure that it is correct I will have a look at the file you have uploaded
RNA Star data Model 2020.docx (134.7 KB)
Andrew
Hi Cristian
I unfortunately can not get into PowerBI at the moment as my laptop has developed a RAM memory problem and I am using my Mac which is not compatible with PowerBI so I can’t read the files. We are still in lockdown so work is unsure how soon they can get my Windows laptop working again.
I am trying to keep learning and working out the data model so if you could help me work out the model for now I would be most grateful
Andrew
Hi Andrew,
There’s no need for PBI to view the GIF file.
Check out, meanwhile, the first sample report with your data here
Let me know your thoughts.
Cristian
The model you’ve started looks good so keep it up.
Using the file you attached, besides the sample I uploaded, you could do analysis with a Decomposition Tree like in the picture below:
I’ll try to update the PBI Report saved on the service already so you could “play” live.
Cristian
Hi Cristian
Thank you for your support the Decomposition Tree looks awesome and that is certainly an excellent solution to some of the analysis
Is there a video on how to construct a decomposition tree? If yes please can you send me the link
I have a question about the model
The fact table in the model it is still very wide at least 200 columns. I get Sam’s point about removing unnecessary columns and I can see why a narrow table is very advantageous- here is my challenge - all the data in the fact table will be used so I can’t delete them
I can use the query editor develop all the other tables in the model that is easy enough but I am a bit stuck on what to do with the fact table in the middle?
Here is my question
As I see it I have two options:
(a) use the query editor bring is the fact columns from the Excel file and commit it to the model as one very wide table (roughly 200 columns)
Or
(b) using the query editor create eight fact tables (one for each sector eg Food table, Health table etc) - in effect this would break up the wide table but I am not convinced this is the ideal
If I use option (a) and if I organise my queries well (I basically already have a good idea of the queries I need because I am currently using Access and Excel) and file all the queries by sector the I will have basically broken up the wide table and achieved the same as option (b)
Please let me know your thoughts on this
I am working my way through the “Advanced data modelling and transformations course so am interested to learn about staging queries and I am certain I can use key measure branching
Thanks for all your help and support
Andrew
Hi Cristian
Thank you for doing this example, it’s really good and I like how you used the questions together with the answers. In my data table when we download the completed survey I have the questions as headers and then the responses underneath. For Access we don’t import the questions only a very short field name. In the example you did were the questions imported Into the model as the first row?
I can see how your sample study works for questions that can only have one answer but how do you manage questions where multiple options can be selected eg “illness” a respondent could have more than one illness?
Thanks for all your support
Andrew
I would choose option B: keep a single connection to the Excel file and go from there to build several fact tables, all connected with the same dimensions. Then you can have separate pages exploring each area or a specific page looking into 2 or three areas compared to the same dimensions.
Regarding Decomp Tree this is the first link I found: https://www.youtube.com/watch?v=5_wN-kUIcGs but there are other ones related that can be watched. There is a specific one I liked shoeing how can you build Subtotal Percentages: check it out here https://www.youtube.com/watch?v=AJxCCfFmLTQ.
If you liked my inputs please mark the answer as a solution.
Regards,
Cristian
It’s great to know that you are making progress with your query @Ducati. Please don’t forget if your question has been answered within the forum it is important to mark your thread as ‘solved’. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!
Hello Cristian
Thank you for the linkages to you tube they are very useful. I am glad you agree with option (b) as that does make the files easier to manage in PowerBI
I have a couple of questions with respect to the survey questions but I will add that as a separate questions
Thanks for all your help really appreciate your support
Andrew
Hello Cristian
Thank you for the example. Please can you advise me on a couple of things in your survey model
(a) The attributes table - is this where you have the survey questions and response options?
(b) please can you show me how you set up the table/chart so that when you click on a response option you see the percentage that chose that option - I think that is a neat solution to some of our questions
Thank you for your support
Andrew
