Are the BI tools suitable for this problem?

[Talisman of Death]

hello

I have a particular issue I am working on as below. I basically have 2 tables from different datasets:

ERP - Table1
SalesOrder ERPCustomerID StartDate EndDate Qty Price Address

We are an IT company and have a separate database for customer usage etc:

OurProductPortal -Table2
Month ERPCustomerID ProductPortal ID BandWidthTotal
01/01/2014
01/02/2014
01/03/2014


ERPCustomerID would be the common to both tables - although the lists of customers on both databasses might not fully match.
Qty field from Table1 will be analysed vs BandWidthTotal Table2 over time

Table2 Month shows first day of month, but it actually relates to the whole month

The Tables have many other fields, but its not relevant to mention more here



The issue: can I relate Qty purchased to Bandwidth used on a monthly basis using BI Tools? The issue:


1)Table1 has start/end date column but Table2 has a month column
2)Table2 is by ERPCustomerID, but Table1 is by Sales Order, which then includes ERPCustomerID. 1 Sales Order may have several lines (1 per service), and there are also >1 Sales Orders for any specific customer.



Keep as separate tables? Combine?





I had a few thoughts without using BI tools:


Add columns to Table1 ---- 1 for each month - so 12 columns for a year.Then put "Yes" or "No" for whether the contract was in place in that month. The problem now is that PivotTables don't recognise all the date columns as being related to each other or the single Month column in Table2. I have to drag columns 1 by 1 into the PivotTable, and it sorts them alphabetically on the string name rather than in Date order.





Expand Table1: Append 1 column called "Month" in Table1. Then recreate the row for each month, just updating the month. So I would have say 12 rows per Sales Order over a 1yr period.
This is a bad idea - too much duplicated data, and data set will quickly become too large.




The other way is to append columns taken from Table1 to Table2. This would be much easier, possibly more logical. But the big problem is that some customers are on 1 table and not the other - and vice versa. I want Table1 data to be the master list not Table2.


I have heard PowerPivot might be able to handle this - is that true? It could relate tables by CustomerID, mash the data per month, when Table1 does not have a Month field?

 

[scottsen]

So, the short answer is "yes, powerpivot can handle all this". It's probably a bit more complicated than your average "I'm just learning powerpivot" problem, but you will certainly know a lot by the end

Typically you end up with a separate calendar table (if nothing else, because the time intelligence functions like "Year to Date" require one). Likely you will have one as well, and that is where your Month field will come from. Probably a separate Customer table as well (as your other 2 tables sound more like fact tables that can have multiple CustomerID).

In general, in power pivot, "no, don't combine tables".

 

[Talisman of Death]

So, the short answer is "yes, powerpivot can handle all this". It's probably a bit more complicated than your average "I'm just learning powerpivot" problem, but you will certainly know a lot by the end

Typically you end up with a separate calendar table (if nothing else, because the time intelligence functions like "Year to Date" require one). Likely you will have one as well, and that is where your Month field will come from. Probably a separate Customer table as well (as your other 2 tables sound more like fact tables that can have multiple CustomerID).

In general, in power pivot, "no, don't combine tables".

Thanks for your reply - I was thinking that combining tables was a bad idea, but isn't straightforward. I asked on this forum because I was thinking of trying M.S. B.I. tools to see if I got anywhere - just wanted to know if it was going to be possible before I started all the effort.