Industry standard data warehouse models

Are there any resources for industry standard data warehouse models? I'm especially interested in financial models regarding securities (stocks, commodities, etc.).

Are you looking for data models in general?

Or are you looking for designs of data mining models that fit a particular schema?

|||

Both! Rather than reinvent the wheel, it would be nice if such resources existed.

However, my current project regards the securities markets in particular.

|||I'm going to move this thread to the OLAP forum where you may find a better audience to answer about data models. Once you get a data model, then it's easier to talk about data mining (at least from where you're starting)|||Where's the OLAP forum? I thought Analysis Services was the place to ask OLAP questions.|||

Take a look at the Kimball group http://www.kimballgroup.com/

See if you can find datawarehousing model you are looking for.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Indexing in SQL Server Star Scheme Data Warehouse

Hi all,

Our star schema design has one fact table and 3 dimensions.

The FK's in the fact do not necessarily make up the primary key. So I have an identifier in the fact table as PK. Here is my index assignment:

Fact Table - Clustered Index on PK
Non Clustered Index 1 on FK1
Non Clustered Index 2 on FK2
Non Clustered Index 3 on FK3

Each Dimension Table - Clustered Index on PK
Non Clustered Index on Attribute. This is the attribute that will be used in reports / cubes.

Is the above design good to start with?

Thanks,

VThe indexing looks fine, but as to whether this is a good design or not you only need to check my sig below to get my opinion...|||Thanks Blindman.

The one issue that we are encountering is, we didnt create a separate time dimension (a mistake in design). We have a smalldatetime field (72 distinct values only, one for each month, so 6 years in total) in the fact table.

We are not able to query this smalldate time field efficiently because we didnt index it (as it was not part of the dimension). We would like to change the design now.

We would like to create a time dimension using the following:

1. Create Time Dimension Table
2. Create new column Time_Key in Fact Table
3. Create Non clustered Index on smalldatetime field in fact table.
4. Join on smalldatetime fields in Time Dimension and Fact table to populate time_key in 2 from Time Dimension table.
5. Drop the index and column of smalldatetime field in fact and reassign non clustered index to Time_Key (FK)

Let me know how the above approach sounds to you guys.

V|||My gut feeling is that creating and dropping the temporary index on the smalldatetime column will take as long as doing a non-indexed join. Generally, indexes are only valuable because they are used more than once, so the investment involved in creating them is saved over each subsequent operation.
An index on a set of 72 discreet values may not even give you much performance boost across millions of records.|||I suppose the question I would pose to you, more than your design, would be, have you chosen the right granularity for your fact table? I haven't seen too many fact tables that stop at a monthly level unless they are being used for forecasting or budgeting purposes and even then they align to pre-existing warehouses, like a sales warehouse. My best recommendation would be to take some time and study warehousing and ensure you are providing a solution that isn't going to have to be reworked a couple of months down the road when the end users want to be able to drill down into details.