How to introduce "Market Data" into an existing DW
3 posters
Page 1 of 1
How to introduce "Market Data" into an existing DW
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...
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
Re: How to introduce "Market Data" into an existing DW
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).
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: How to introduce "Market Data" into an existing DW
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?
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
Re: How to introduce "Market Data" into an existing DW
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.
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
Similar topics
» fact tables for stock market analysis
» Add New or Drop existing conformed dimension in existing DWH
» Non-existing Dimension Attribute
» Market Share in Fact Table
» Fact table for stock market transactions
» Add New or Drop existing conformed dimension in existing DWH
» Non-existing Dimension Attribute
» Market Share in Fact Table
» Fact table for stock market transactions
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|