PowerPivot file setup

I have a lot of data I want to work with in PowerPivot 2013.
I'm not sure what approach would be the best to initially pull the data.
I have 3 databases on 2 servers.

Table                company      server      Join      Final join
Sales Header      A      A      1A      Sales Header
Credit Header      A      A      1A      Sales Header
Sales Header      B      A      2A      Sales Header
Credit Header      B      A      2A      Sales Header
Sales Header      C      B      3A      Sales Header
Credit Header      C      B      3A      Sales Header
                        
Sales DETAIL      A      A      1B      Sales Detail
Credit DETAIL      A      A      1B      Sales Detail
Sales DETAIL      B      A      2B      Sales Detail
Credit DETAIL      B      A      2B      Sales Detail
Sales DETAIL      C      B      3B      Sales Detail
Credit DETAIL      C      B      3B      Sales Detail

I need 20-25 fields out of each table where there are 75-100 fields.
The Sales & Credit files need to be combined first to get all sales.
Then those combined for each company.

Should I build views 1st to combine the data in each company on each server?
Or a suggestion?

This is all new and I'm kind of jumping in with both feet, so please excuse if this seems like a simple question to you.
actsoftAsked:
Who is Participating?
 
mike247Commented:
Yes, I recommend creating a view in each database that has exactly what you want--especially if each view uses multiple tables.  Make all three views contain the same column headings in the same order.  Then you can use Power Pivot to combine the 3 views into 1 pivot table.  I would also add a "tag" column to each view so you know where the data originated.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.