How to introduce "Market Data" into an existing DW

View previous topic View next topic Go down

How to introduce "Market Data" into an existing DW

Post  amir2 on Mon Jun 25, 2012 8:01 am

Hello

I am relatively new to Dimensional Modelling and I have a new requirement for which I am seeking guidance.

Our existing DW has banking account daily snapshots as its primary fact table. The fact table has a star schema with various dimensions such as Date, Account, Customer, Manager, ....

Our Products team now want to store a handful of Bloomberg market data (e.g. Swap rates, Base rates, Equity index value, Credit Rating) in the DW. The only dimension that this data has a natural relationship with is the Date dimension. But, our analysts need to be able to anlalyse what products were being offered at a particular point in time and see the Market data values at that point. In fact, they want to be able to "link" data from all existing dimensions to the Bloomberg data.

Questions:
- What is the recommended approach for introducing this new fact table? As I say, the only natural relationship that I can think of is the Date dimension. None of the other dimensions have a relationship with the Bloomberg data.

- The users are happy to have all of the 10 new measures lumped as one fact table (this is how they use it now from Excel). Is this OK or should I create multiple fact tables i.e. one for Swap rates, one for base rates, etc. Using this approach, each fact table would contain1 or 2 columns only - this doesn't feel right to me, or is it ok?

Thanks in advance...

amir2

Posts : 29
Join date : 2010-07-29

View user profile

Back to top Go down

Re: How to introduce "Market Data" into an existing DW

Post  BoxesAndLines on Wed Jun 27, 2012 9:12 am

If the market data are measures, then it is fact data. I would consider either a new fact table (if there are other fact tables that need the data as well) or simply including in the current fact table (if there isn't a need to share the data).
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: How to introduce "Market Data" into an existing DW

Post  amir2 on Fri Jun 29, 2012 10:49 am

Thanks.

We are considering making this a dimension because the users want to be able to link market data to all existing dimensions and we want to avoid snowflaking.

By adding the new data as a dimension, users can use the cube to "navigate" to other dimensions through the fact table (daily balances snapshots) .

Does this seem a reasonable approach?

amir2

Posts : 29
Join date : 2010-07-29

View user profile

Back to top Go down

Re: How to introduce "Market Data" into an existing DW

Post  Jeff Smith on Fri Jun 29, 2012 6:39 pm

My experience has been that there are 2 types of market data. One type of market data is when you send your customer data out to a vendor and they send it back with all kinds of information about the customer: net worth, Credit rating, Market Segment info, etc. This is typically attribute information and put into a dimension or series of dimension tables.

A second type of market data that I've worked with is geographic based. Census data or data built on census are examples. I've done 2 things with this type of data. First, the measures (households, population, market potential) is fact information usually at a Census tract (or block group) level. I usually aggregate fact data to the same geographic level. Makes it easy to show market penetration levels by product or by market segment at a geographic level. Great stuff when combined with mapping software. You can also use the geographic based market data to segment geogrpahy. You could use it to create attributes for census tracts, which can be added back to you're customer data.

It's really cool when you combine both types of data and show your penetration of various market segments in various geographies.

We had a series of maps showing our household penetration of our target segment before and after we expanded into a new market. Cool stuff.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: How to introduce "Market Data" into an existing DW

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum