Data modelling - Old data with Hourly data

[Paul91S]

Hi,
I'm trying to mash two extracts of data together. The first is from a Teradata Warehouse which contains data from the day before, and updates daily. The second extract is a txt file that is produced hourly with intra day updates.
The main things that change in the data is dates and times (i.e. the date and time will be populated when the task is completed).
I've loaded both of these connections into Power Pivot and created a relationship based on the primary key on both extracts being the same.
I'd now like to put the two sources together to provide an up-to-date snapshot of the data.
What's the best way to go about this? I've thought about just having a series of IF statements in one of the extracts to bring back the latest information from the other extract (if anything has changed).
I've also been looking into creating a perspective, but I'm not sure how to start and can't find much in terms of documentation.
If anyone can assist me that would be greatly appreciated.

Many thanks,
Paul

 

[Tianbas]

Can you provide an extract or an example from these both tables as this might determin what is the best way to put the sources together ? If its just a few columns to compare like your task completed date and time I would just create a pivot table out of the data model and pull both data columns in the Pivot and write a measure in the pivot to show me the delta. If its more columns that change I would go with some calculated columns with IF statements and RELATED() to pull the updated values from the hourly in the daily table, maybe add a column that tells me it has been updated or not that I could use in a slicer later in a pivot table to show only what has changed

 

[Paul91S]

Thanks for your reply.
Table A below is the Daily snapshot data.

ID	Start Date	End Date
1234	12/07/2014	23/07/2014
1235	15/07/2014	16/07/2014
1236	23/07/2014	26/07/2014
1237	24/07/2014	25/07/2014

<tbody> </tbody>

Table B has similar columns but is refreshed hourly.

ID	Start Date	End Date
2468	25/07/2014	01/08/2014
1236	24/07/2014	29/07/2014
4568	31/07/2014	15/08/2014
4545	16/08/2014	19/08/2014

<tbody> </tbody>

As can be seen from above, Table A can have IDs that don’t appear in B (they may have completed), and Table B can have IDs that don’t appear in A (they may have just been created in the past hour).
I would need to be able to see all of these IDs in one table together.
ID# 1236 appears in both tables so a relationship between the two tables would join on this one, and using IF(RELATED formula I could get the changes in the start and end date.
In the actual data there are more columns then this which would need ‘updating’ through this model (planned/actual/schedule/estimated start and end dates).
If I base the IF statements in table A , I won’t be able to get the IDs that only appear in table B, likewise if the IF statements were in table B.
I hope this helps to explain, basically I think I need a lookup table for all existing IDs that can join to both tables. But as the two lists of IDs come from two different sources I’m not sure how to get a lookup table.

 

[Tianbas]

Depending on which Excelversion you have you could use PowerQuery or something like that to join the IDs from both extracts. In the 2010 version I have that is not available and I would go to our IT BI guys to provide me that unique ID list as a 3rd database extract (should be no big deal with SQL).

With an ID table you could relate all columns from the hourly table to the ID and IF nothing is in hourly than take the daily list

 

[Paul91S]

Hi Tianbas,
Thank you for your response.
I'm working on Excel 2013, so I could potentially user PowerQuery (the company I work for have a slight issue with the Teradata connectivity using Power Query so it hasn't been rolled out company wide, though I am able to connect to it).

So just to confirm, it doesn't look like it's fully possible to combine the two IDs into one table using Power Pivot?

How would I go about it in Power Query? I tried using Append but it seemed to want to append the entirity of both datasets, not just the column I needed.