[This question was also sent as an email]
Hi Mynda,
I am a little over halfway through the PowerPivot course and have found your videos tremendously helpful – beautiful job! I’m now wanting to begin implementing what I have learned for my own project. The problem is where to begin.
I’ll start with some info and hopefully you’ll let me know if you need more context. I am attempting to collect aggregated information on US federal contracts and grants for diseases such as Ebola. My company wants me to eventually make dashboards to see to whom, when, how much, etc. federal funding went for Ebola since FY2010.
I am working with excel table downloads from GovTribe.com, GovWin.com, USASpending.gov, and taggs.gov. The downloaded excel files from the four websites in red gives information such as:
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Boxes in orange contain column info I must use for my pivot tables and dashboards – the other information I can also use but is not of primary importance. I also add a few columns of my own to identify place of performance country (UU for US and UI for outside of US) and awarded vendor (if they’re in the academia, government, international entity, non-profit, or private sectors).
So my question is – where do I start? Do I split the columns up into their own excel tables or keep this entire table with all of these contracts and awards in one spot? The excel documents can also be quite large. I also have to think about the DIM and Fact tables – what would be examples using these column headers?
Thank you kindly,
Amberlie
Hi Amberlie,
Congratulations on your progress through the courses. I recommend you complete all of the courses before you try to implement the techniques in your own work. I can see that you haven't started the Power Query course yet. Power Query will be essential in this project you're working on.
e.g. you need to use Power Query to get the files you've exported form the various sources and consolidate them into a single table that contains the columns you want to analyse. From Power Query you can load the data to Power Pivot.
To answer your question about dim and fact tables, based on the columns you've identified in orange, I don't see many that lend themselves to putting in a dim table. Perhaps you could put the Place of Performance in your fact table and move the Place of Performance State and Country to a dim table. Similarly, the Contact's Name could stay in the fact table and the Contact's Position, email and link could move to a dim table.
That said, for now please go back and complete at least the Power Query and Power Pivot courses before doing anything with your own data. I know it's exciting and you're keen to get started, but this will help you know where to begin and ensure you set things up correctly from the outset. It won't be fun to start building your model now and get something wrong which results in you having to start from scratch.
Mynda
