Hi there -
i have multiple data sources, 12 to be precise. One for each month.
the data structure is exclatly the same i.e same dimension and measures. it's just that the period (date e.g. 2013.05 ... 2013.08) and the values change.
what i previously did was to connect the first data source then went to Extract and 'Add data form file' in order to add the other months and easily report on the full data set.
the issue i have with this method is that occasionnaly i may have to update a specific month (data source), replcaing all existing data with updated once (structure is still the same). if i use the existing method it means i have to recreate the full data set everymonth instead of just refreshing the one month data set that was changed.
How best should i blend this data? same structure each month just new period and new set of values for some of the columns.
thanks
Antonio
Antonio, since your structure is exactly the same for each one, you can use a Custom SQL statement. When you connect to the first data source and click Custom SQL, T generates the first statement. Open up the large editor [...] and delete everything from between the SELECT and the FROM. Now type in something like this:
SELECT *,
"Jan." AS [TYPE]
FROM [JanData$]
Click Preview to make sure you got the syntax right. Now add all the other data sources like this:
SELECT *,
"Jan." AS [TYPE]
FROM [JanData$]
UNION
SELECT *,
"Feb." AS [TYPE]
FROM [FebData$]
UNION
(etc.)
By using the '*' wildcard you're able to eliminate all individual declarations of the fields. After you do the connection then you can pull the extract. When you need to make changes, take the extract off, refresh the connection, then do another extract. You won't have to re-created any tables or rewrite any SQL code. And by using the wildcard, you won't have to change the SQL even if you add new fields (columns) to your data sources.
The only rule to remember is the field have to be the same in all the data sources.
Good Luck.
--Shawn
