January 19, 2010 at 8:25 am
Hi all,
I am new to SQL Server reporting Severvices 2005.
I am trying to do reseach on how to Generate report in 2 dimentional and also multidimensional.
By Reading the book and multiple articles, I understood that with my Transactional database,I can
Use SSRS to build the report definition file then deploy to Report Server and user can access the
report by using the Report manager.
The part that I am totally confuse is: How can I start with the multidimension when I only have the
transactional database?
1) Do I have to create the DATA WAREHOUSE from the Transactional database? How do I do that?
2) what is the different between Transactional DB and Data warehouse?
3) Can I generate multdimentional using traditional database? How can I do that?
4) Is SSIS is the tool to Extract-Transform-and Load data from tradiditional DB to DB warehouse ?
5) Only when we have datawarehouse, then I can use the SSAS to designed and create my "CUBE" with multidimensional report?
6? Can I use SSAS from my traditional database?
Sorry for so many questions. I would appreciate very much for any help/explain/example to lead me to a correct path.
Thank you.
Regards,
Ddee
January 19, 2010 at 8:46 am
Dee Dee-422077 (1/19/2010)
Hi all,The part that I am totally confuse is: How can I start with the multidimension when I only have the
transactional database?
You can create a Datawarehouse off your transaction database.Data warehouse data are often stored multiple times—in their most granular form and in summarized forms called Aggregates. Data warehouse data are gathered from the operational systems and held in the data warehouse even after the data has been purged from the operational systems.
1) Do I have to create the DATA WAREHOUSE from the Transactional database? How do I do that?
[/Quote]
Yes. For this you need to identify your facts and dimensions and also what schema is appropriate for your needs. Example Star or Snowflake.
Please read this article for DW concepts
2) what is the different between Transactional DB and Data warehouse?
Major difference is DW is part of an OLAP system, which is historical data. You can search on it more on the internet.
3) Can I generate multdimentional using traditional database? How can I do that?
The above article explains it better
4) Is SSIS is the tool to Extract-Transform-and Load data from tradiditional DB to DB warehouse ?
Yes
5) Only when we have datawarehouse, then I can use the SSAS to designed and create my "CUBE" with multidimensional report?
That's correct. Cube is 3 dimensional representation of your data for which you need to build a Datawarehouse.
6? Can I use SSAS from my traditional database?
Did you mean to ask if you can use SSAS and create cubes without having a Datawarehouse?? I don't think so. As Cube in itself is 3D representation of your data which is done using Aggregates as described earlier
Sorry for so many questions. I would appreciate very much for any help/explain/example to lead me to a correct path.
Thank you.
Regards,
Ddee
No problem at all. Hope this helps you to start on building a Datawarehouse. Get back with your replies and any questions you may have.
Hope this helps...
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
January 19, 2010 at 9:13 am
5) Only when we have datawarehouse, then I can use the SSAS to designed and create my "CUBE" with multidimensional report?
That's correct. Cube is 3 dimensional representation of your data for which you need to build a Datawarehouse.
6? Can I use SSAS from my traditional database?
Did you mean to ask if you can use SSAS and create cubes without having a Datawarehouse?? I don't think so. As Cube in itself is 3D representation of your data which is done using Aggregates as described earlier
Not wanting to be pedantic (but i guess i am :hehe:)
re: #5 - a cube is not necessarily 3 dimensional, it's dimensionality is dictated by the number of dimensions included, so it could be much higher than 3. People often like to think of these multidimensional databases as 'cubes' as it allows you to explain the interaction of dimensions (e.g. the sides of the cubes) with the facts contained within at the intersection of the dimension values.
re: #6 you can definitely build a cube (or cubes) from your transactional system/s. Should you? Generally, NO. re: the aggregates, unless you're using (say) Oracle, the aggregates are not stored/generated in the EDW they are contained in the multidimensional database, that is, the cube. Not to say you can't have aggs in your EDW, but, again, unless you are using a reasonably smart tool (like Oracle) the queries won't natively access/use the aggregates, you'd need to write the queries specifically to reference them (unlike a cube, where it references the aggregations as often as it can).
Good luck with your foray into data warehousing, hope you find it interesting, it's a great area to work in!
Steve.
January 19, 2010 at 9:21 am
Thank you so much for your reply. It's really help me to understand the basic concept that I need for starting working with Data ware house.
I have one more question 1 and 4:
1) Do I have to create the DATA WAREHOUSE from the Transactional database? How do I do that?
Yes. For this you need to identify your facts and dimensions and also what schema is appropriate for your needs. Example Star or Snowflake.
Please read this article for DW concepts[/quote]
4) Is SSIS is the tool to Extract-Transform-and Load data from tradiditional DB to DB warehouse ?
Yes
Is that mean:
With my transactional database, I will need to identify your facts and dimensions and schema, then I will use the SSIS to extract and Load my data from tranditional DB to New data warehouse. Am I correct?
Thank you so much for your help.
Best regards,
Ddee
January 19, 2010 at 9:31 am
re: #5 - a cube is not necessarily 3 dimensional, it's dimensionality is dictated by the number of dimensions included, so it could be much higher than 3. People often like to think of these multidimensional databases as 'cubes' as it allows you to explain the interaction of dimensions (e.g. the sides of the cubes) with the facts contained within at the intersection of the dimension values.
Yes there could be a many dimensions, way more than 3 ;-)...
re: #6 you can definitely build a cube (or cubes) from your transactional system/s. Should you? Generally, NO. re: the aggregates, unless you're using (say) Oracle, the aggregates are not stored/generated in the EDW they are contained in the multidimensional database, that is, the cube. Not to say you can't have aggs in your EDW, but, again, unless you are using a reasonably smart tool (like Oracle) the queries won't natively access/use the aggregates, you'd need to write the queries specifically to reference them (unlike a cube, where it references the aggregations as often as it can).
I thought I will not mention about stuff that one shouldn't do..
Thanks for the clarifications though..:-)
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
January 19, 2010 at 9:37 am
That's correct. You need to identify your facts and dimensions. I guess a Star Schema would be your best bet, as it is the simplest style of dw schema.
You can use SSIS to load data into your DW and build your cubes off your DW using SSAS.
HTH...
The_SQL_DBA
MCTS
"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply