Friday, February 24, 2012
How to build good Data Warehouse Structure
DTS. Now I'm arrive at the step where I must concern about performance.
There some questions that I want to ask, there're:
1. Where should I store OLTP database and OLAP database, should they in
separate database or even in separate server?
2. Which should i choose, create fact tables by create new tables or
views?
3. What best technique to transfer data from OLTP to OLAP except DTS
(or better than DTS) ?
Could you give me suggestion how to build good structure of Data
Warehouse?
Thx in advance
There is a lot of point to analyse:
* Data volume size
* Usage of the data (who, how, when, how many time)
* Frequency of updates
If your current OLTP environnement is not used at 100% and if the volume is
small, then you can use the same server for both OLTP & OLAP databases. If
we talk about more then 10Gb of data, moving to another server could help
you (because the hard drive setup will be different). in my case I have some
installations which shared a lot of databases, operationnal + olap etc... on
1 server only, due to a small amount of data.
If you plan to make some cleansing processes, or plan to query directly your
OLAP database (thourgh SQL syntaxes like reports), then use tables to make
sure you can create specific indexes. If you plan to just fill an OLAP cube,
and your OLTP database is clean and there is no cleansing or transformation
to do, then use views, but this impact the OLTP database during cube
process.
And finally, there is a lot of ETL tools on the market. And again, choose
the right one in term of performance, transformations capabilities, etc...
Do you plan to have a "big" project? what is the budget? 10 000$, 100
000$...?
Do you talk about 1Gb of data, 10Gb, 100Gb, 1Tb?
"Resant" <resant_v@.yahoo.com> wrote in message
news:1110869095.453586.311680@.f14g2000cwb.googlegr oups.com...
> I'm new to OLAP, and just tried build OLAP & Data Warehousing using
> DTS. Now I'm arrive at the step where I must concern about performance.
> There some questions that I want to ask, there're:
> 1. Where should I store OLTP database and OLAP database, should they in
> separate database or even in separate server?
> 2. Which should i choose, create fact tables by create new tables or
> views?
> 3. What best technique to transfer data from OLTP to OLAP except DTS
> (or better than DTS) ?
> Could you give me suggestion how to build good structure of Data
> Warehouse?
> Thx in advance
>
How to build good Data Warehouse Structure
DTS. Now I'm arrive at the step where I must concern about performance.
There some questions that I want to ask, there're:
1. Where should I store OLTP database and OLAP database, should they in
separate database or even in separate server?
2. Which should i choose, create fact tables by create new tables or
views?
3. What best technique to transfer data from OLTP to OLAP except DTS
(or better than DTS) ?
Could you give me suggestion how to build good structure of Data
Warehouse?
Thx in advanceThere is a lot of point to analyse:
* Data volume size
* Usage of the data (who, how, when, how many time)
* Frequency of updates
If your current OLTP environnement is not used at 100% and if the volume is
small, then you can use the same server for both OLTP & OLAP databases. If
we talk about more then 10Gb of data, moving to another server could help
you (because the hard drive setup will be different). in my case I have some
installations which shared a lot of databases, operationnal + olap etc... on
1 server only, due to a small amount of data.
If you plan to make some cleansing processes, or plan to query directly your
OLAP database (thourgh SQL syntaxes like reports), then use tables to make
sure you can create specific indexes. If you plan to just fill an OLAP cube,
and your OLTP database is clean and there is no cleansing or transformation
to do, then use views, but this impact the OLTP database during cube
process.
And finally, there is a lot of ETL tools on the market. And again, choose
the right one in term of performance, transformations capabilities, etc...
Do you plan to have a "big" project? what is the budget? 10 000$, 100
000$...?
Do you talk about 1Gb of data, 10Gb, 100Gb, 1Tb?
"Resant" <resant_v@.yahoo.com> wrote in message
news:1110869095.453586.311680@.f14g2000cwb.googlegroups.com...
> I'm new to OLAP, and just tried build OLAP & Data Warehousing using
> DTS. Now I'm arrive at the step where I must concern about performance.
> There some questions that I want to ask, there're:
> 1. Where should I store OLTP database and OLAP database, should they in
> separate database or even in separate server?
> 2. Which should i choose, create fact tables by create new tables or
> views?
> 3. What best technique to transfer data from OLTP to OLAP except DTS
> (or better than DTS) ?
> Could you give me suggestion how to build good structure of Data
> Warehouse?
> Thx in advance
>
Sunday, February 19, 2012
How to build cubes from Oracle database?
Hi, can anyone show me the steps to use SQL Server
to perform OLAP analysis for data from Oracle relational database?
Thanks,
Chris
Do you already have a dimensional model (fact and dimension tables) set up in your Oracle database?
Bryan
|||
Bryan C. Smith wrote:
Do you already have a dimensional model (fact and dimension tables) set up in your Oracle database?
Bryan
Not yet.
Should I build the dimensional model in SQL Server or Oracle, and how?
Is there any document to show the steps?
Thanks,
Chris
|||Traditionally, the OLAP database/cube exists as a cached layer on top of a series of fact and dimension tables (referred to as a dimensional model) in a relational database. SSAS 2005 can support dimensional models built in both Oracle and SQL Server (as well as other relational database technologies).
Technically speaking, you can skip the implementation of the dimensional model in the relational database. You do this by assembling sets in the DSV of your cube through named queries. Some folks with smaller data marts have successfully pulled this off, but it is not recommended.
If you are new to dimensional modeling, I highly recommend Ralph Kimball's "The Data Warehouse Toolkit". It will give you a solid foundation in this stuff and SSAS 2005 is aligned with the design principle he expouses.
Good luck,
Bryan