Solved

PowerPivot file setup

Posted on 2013-12-10
1
318 Views
Last Modified: 2013-12-27
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.
0
Comment
Question by:actsoft
1 Comment
 
LVL 2

Accepted Solution

by:
mike247 earned 500 total points
ID: 39711733
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

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

937 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now