Guys,
         I have been trying to locate the proper resources in Data Warehousing for SQL Server 2005. I have this question if anyone can help pls.

Q1) I am starting a Project in Data Warehousing Field. I have a relational Database with lots of tables. I need to create a data warehouse/mart from my existing relational database and eventuall create Cube for data analyzation. When I looked at the tutorial at SSAS and SSIS in 2005, I did not find tutorials on how to create fact tables and dimensions from the existing relational data source. In SSAS 2005, tutorial starts up with the AdventureWorksDW and go forward on how to create cubes etc but does not tell me how they created the facts and dimensions from the relational database AdventureWorks. I read that i have to have a UDM and then start from there. Does any one have any good link to resources or suggestion on this issues.

Q1) lets say I have a table in RDBMS called "Employee " and it has a date field called "Hire Date" of datatype smalldatetime. I want to to create a fact table using the Time Dimension table and "Employee" table. How do I create surrogate key and the Fact table, is there a wizard or need to create manually doing the Joins? I saw the sample DB AdventureWorksDW  that come with SQL'05 and wanted to know the steps on how the fact tables were created?

Q3) what schema should I use if I have multiple fact table and multiple Dimensions. Is that SNOWFlake Schema? Any resources on this? All I see in the examples is One fact table and Multiple Dimension, all joining to the same fact table. Any resources or live sample,

Thanks Again!

Anyone Please Help!!!!!!

Fact Table consists records of lowest level of granularity. In a real world scenario, I see Employee as a dimension not as a Fact table. Fact table will have employee surrogate keys pointing to the employee table(Dimension) and some measures(quantitative values). 

*** Your solution is a simeple StarSchema

In another word, a Fact table will have only surrogate Keys and measures, no such value as employee fullname or any other hierarchical members in it. Those values will be stored in the Dimension tables

You can create a Hiredate (time dimension) and insert its surrogate keys to the Fact table.

update Staging_FactTable
set HireDateKey = Dm_Time.TimeKey
from FactTable (nolock)join DM_Time (nolock)
on cast(convert(varchar,staging_FactTable.hiredate,101)as datetime)= DM_time.Date

For starters, make a staging_FactTable and do most of the scrubbings using SSIS, and put your end product in
the Final Fact table.Which will also help when you do incremental loads

HTH ..DataWarehousing/Business intelligence is Fun!!!

Thanks a Lot.
It's good to know I found someone from Nepali community who has DW knowledge and willing to help.
You are right, Employee in Dimension rather than a fact table, I should have given better example.

This is What I am trying to do. I have Relational DB in Sql with about 200 tables, I need to do the financial analytic so I am trying to Build a data mart in SQL'05 from my RDMBS. So I can use the Oracle Business Intelligence on data mart to run the trends over time period and transactions such as payments, reserves etc to create some financial reports etc. At this moment, I am little knowledgeable on DW concepts as of yet.

Please Suggest how to create a star schema from this example below:

1) DimClaim (Its as 1 to Many rel to DimUnits and  also to DimDiary)
2) DimEmployee (DimEmployee Many to One -->DimClaim)
3) DimDiary (DimDiary Many to One-->DimClaim
3) DimUnit (DimUnit Many to One ->DimClaim)
4) FactPayments
5) FactReserve
6) FactRefunds
etc
FactPayments, FactReserve,FactRefunds has Many to One->DimUnit

As explained above:
All Fact tables are joined to DimUnit in Many-1.(All the transactions are at Unit level)
DimUnit is Joined to DimClaim in Many-1
DimEmployee,DimDiary are Joined to DimClaim in Many-1

After I build a Schema using above relationship,
If I say "Give me particular's Employee's Total Payment", I get error and it says that Employee is not linked with payments.
(However it is defiened in my joins because,
*Employee is Linked with DimClaim
*DimClaim is linked with DimUnit and
* DimUnit is linked with FactPayments)
All the data warehousing resources i see, there is a not single example that gives concpt of this, would you please help and suggest where to start to learn DW. MSDN?
Thanks

                                                                                           

 

 
 

 You need to further de-normalize your tables. You're still looking at from the normalized point of view.

1. Fact table will have - > relationships to dimensional tables..meaning inorder for you to reach a certain "emp_id" in the DIM_employee from Fact_payments, you can transcend through other dimensions as well. You can create this by Creating a Factless_facttable.

For example,
2) DimEmployee (DimEmployee Many to One ->DimClaim)

  Create a table called Factless_emp_claim with three columns
   F_emp_claimID PK
   empID FK----to employee table
   ClaimID FK---to claims table

In this table you just store every possible combinations of emp and claim ID's that exist in your rdbms

You can use this "Factless" fact table as a  bridge for your Facttable  to reach both dimensional tables.

There's not a single theory in DW world that solves all the isssues. You just have to know about them and use it occordingly to come up with the best solution for your application.

Also, I suggest you Read about  "dimensional modelling for business intelligence applications". There are many white papers you can just google it.Also thories about Ralph Kimbal and  Bill Inmon.


hth