Create star schema in SQL Server 2005?

Consider me brand new to data warehousing. I'm looking for a simple,
real-world example taking a simple transactional table, and getting all
"data warehouse" on it.
I know there's a star schema to design, but wouldn't I just do that within a
regular (OLTP) database?
We're looking to get into data warehousing in baby steps. I'm just trying
to be able to put an extremely simple real-world example in front of my boss
to explain what we would do. I.e. not concepts, but 1) start with
tblEmployee in the OLTP database... step 2...
Any help at all would be greatly appreciated.
Thanks,
rscYou might want to read Ralph Kimball's first book; he walks step-by-step
through the process, in detail. See:
http://www.amazon.com/gp/product/04...5964650?ie=UTF8
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Ronald S. Cook" <rcook@.westinis.com> wrote in message
news:ual%23jsqrGHA.4252@.TK2MSFTNGP02.phx.gbl...
> Consider me brand new to data warehousing. I'm looking for a simple,
> real-world example taking a simple transactional table, and getting all
> "data warehouse" on it.
> I know there's a star schema to design, but wouldn't I just do that within
> a regular (OLTP) database?
> We're looking to get into data warehousing in baby steps. I'm just trying
> to be able to put an extremely simple real-world example in front of my
> boss to explain what we would do. I.e. not concepts, but 1) start with
> tblEmployee in the OLTP database... step 2...
> Any help at all would be greatly appreciated.
> Thanks,
> rsc
>|||if you are not confortable to transform your data from your OLTP to a DW
schema
and if your OLTP database has a simple schema
and if you don't have to cleanse your data and synchronize with other
sources
then you can try to use view to create a "star schema".
for example, create a view which join your order header and order items
tables (the header contain some important information like the customerid
while the item contain the productid, price etc...)
create views to flatten your tables to create your dimensions (merge the
country, address & customer table; convert null values to unknown or N/A
values to insure a good data integrity)
then create your cubes and dimensions against these views.
another approach is to starts from an empty model and use the AS2005
templates feature.
this will generate the dimensions and cubes you need
and then create the source database and the table required.
after this, you can populate these tables, or replace these table by views,
your job is to found the right column in your source database to fill the
right destination column.
you can also found some samples on the web or in books; I remember books
with sample DW models, but I don't have the name in mind.
if you want to demonstrate this to your boss, create a useable model, not
only a customer analysis but a (at least) customer & product & time
analysis.
good luck.
Jerome.
"Ronald S. Cook" <rcook@.westinis.com> wrote in message
news:ual%23jsqrGHA.4252@.TK2MSFTNGP02.phx.gbl...
> Consider me brand new to data warehousing. I'm looking for a simple,
> real-world example taking a simple transactional table, and getting all
> "data warehouse" on it.
> I know there's a star schema to design, but wouldn't I just do that within
> a regular (OLTP) database?
> We're looking to get into data warehousing in baby steps. I'm just trying
> to be able to put an extremely simple real-world example in front of my
> boss to explain what we would do. I.e. not concepts, but 1) start with
> tblEmployee in the OLTP database... step 2...
> Any help at all would be greatly appreciated.
> Thanks,
> rsc
>|||Hello Ronald,
A good example of Star schemas is the Adventure Works data warehouse
example that comes with SQL Server 2005. The example covers Finance,
Sales (Internet and Reseller), based on fictitious Bicycle Company.
It's a great example to play with.
As for ETL there a few examples included in the samples that come with
SQL Server, but if you are looking for an architecture overview check
out this web cast by Kimball Associates consultant Joy Mundy \Using
SQL Server 2005 Integration Services to Populate a Kimball Method Data
Warehouse (Level 200).
http://msevents.microsoft.com/cui/W...&CountryCode=US
Joy co-wrote The Microsoft Data Warehouse Toolkit, which I can
recommend as a great starting point. The book includes the example of
populating the Adventure Works data warehouse from the Adventure Works
OLTP sample database.
The Microsoft Data Warehouse Toolkit: With SQL Server 2005 and the
Microsoft Business Intelligence Tool Set.
http://www.amazon.com/gp/product/04...r.blogspot.com/