Star schema

Question

Star schema

newbieuser

SSCrazy Eights

Points: 9423
More actions
March 15, 2016 at 10:51 am

#316956

Hi friends,
We are in the development phase of generating new reports(using 3rd party) tool from the application. Currently, the application connects to the Oracle database where there are relational schemas(original) and star schema(fact and dimension tables). Star schema tables are populated from the other relational schemas(original)in the database. The 3rd party reporting tool pulls data from the Oracle star schema to generate reports. It's more like the 3rd party tool requires a star schema to be built in the database to generate reports.
Similarly, we are looking to create star schema in our SQL server database as well. Currently we have all our tables under DBO. I'm researching to see if we can create star schema(fact and dimension tables) under same DBO user or should it be under different user account. Also, are the star schema optimization features applied at the database level or at the server level? Please give your thoughts.. Any suggestions would be really helpful.
Looking forward to your replies . Thank you

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 1

Please note that we are not looking to create a data warehouse. We just need to create tables in star schema so the 3rd party tool can access these tables for reporting purpose. So we are wondering if the fact and dimension tables can be created under same DBO account on the production database? If we enable star schema optimization features in future will it affect the other tables in the database?

Please give your thoughts. Thanks so much

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 2

Please note that we are not looking to create a data warehouse. We just need to create tables in star schema so the 3rd party tool can access these tables for reporting purpose. So we are wondering if the fact and dimension tables can be created under same DBO account on the production database? If we enable star schema optimization features in future will it affect the other tables in the database? Or is it recommended to create a seperate database for this purpose?

Please give your thoughts. Thanks so much

The Danish Dynamo SSC-Addicted Points: 464 More actions · Answer 3

The Danish Dynamo

SSC-Addicted

Points: 464

March 15, 2016 at 11:40 am

#1865381

Yes, the tables can be created under the same dbo.

newbieuser SSCrazy Eights Points: 9423 More actions · Answer 4

Thanks. If we create fact and dimension tables under same dbo schema, can we still implement the star join optimization features like bitmap filtering etc on these tables?

Thanks again

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 5

Instead of creating a raft of new tables to hold identical data for a reporting tool, can you cheat and just create views that look like tables or, perhaps, use synonyms?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)