Analytics Software

2817195 wrote:

Hi friends,

We are developing new application reports and the tool requires star schema in the Oracle database. It is still in starting phase so we are trying to gather information on this.  I'm new to this so I have been reading a lot and researching on star schemas. I would like to get your inputs /suggestions from you experts as well. From what I'm reading it looks like star schema contains one fact table and can have multiple tables pointing
to the fact table as FK's.
One question is that are star schemas  enabled at schema level or does it have to be at the database level by making star_transformation_enabled='TRUE' in the ini file.
Also, are there any other parameters to use star schemas or just star_transformation_enabled should be TRUE?

    Thank you so much

http://docs.oracle.com/database/121/REFRN/GUID-B2E6145D-164A-4453-9839-0F6E6442A922.htm#REFRN10213

"STAR_TRANSFORMATION_ENABLED determines whether a cost-based query transformation will be applied to star queries."

A Layman's Understanding of Star Schemas. | Oracle FAQ

We are developing new application reports and the tool requires star schema in the Oracle database.

Hmmm - I have NEVER heard of a 'tool' requiring a start schema.

How would a tool know if a star schema even existed?

What 'tool' are you talking about?

From what I'm reading it looks like star schema contains one fact table and can have multiple tables pointing to the fact table as FK's.


One question is that are star schemas  enabled at schema level or does it have to be at the database level by making star_transformation_enabled='TRUE' in the ini file.

Also, are there any other parameters to use star schemas or just star_transformation_enabled should be TRUE?

I suggest you do a SIMPLE google search for 'oracle 12c star schema.

Almost the entire first page of results will be links to the various Oracle docs that discuss 'star schemas' and data warehouse design. Here are just a couple of the major ones.

About Star Schemas - in the Data Warehousing Guide

https://docs.oracle.com/database/121/DWHSG/ch2logdes.htm#DWHSG9233

Also in that same doc above at this link is 'optimizing star schemas'

https://docs.oracle.com/database/121/DWHSG/schemas.htm#DWHSG8591

See page 7 of this

http://www.oracle.com/technetwork/database/bi-datawarehousing/twp-dw-best-practies-11g11-2008-09-132076.pdf

Read through some of that material to get a foundation and then provide some info about those reporting requirements. Then post if you have any specific questions about HOW, WHEN or IF you should be using star schemas.

I also suggest you totally forget about how star schemas are 'enabled' and focus on the actual report requirements.

I have written thousands of reports in 25+ years and NONE of the reporting tools I have ever used 'required' star schemas. Those tools include Oracle Reports, Crystal Reports (now Business Objects), Brio, Cognos and others.

Requirements come first. Then you look for solutions that can implement those requirements.

I don't understand why that parameter defaults to false. In most environments it will do either nothing or only good things. Just change it. Remember that you'll need bitmap indexes on your dimension columns.

Thank you so much for your replies and references.  I did read all the documentation and understood better on the star schemas. Some basics of star schema: it includes one or more fact tables with multiple dimension tables pointing to one fact table. Bitmap indexes should be created on dimension columns to reduce number of blocks and star_transformtion_enabled should be set to TRUE to reduce I/O for star queries. I'm continuing to read more on this.. I do have a few basic doubts that I'm not able to find in the online resources and I would like to ask here..

We are using 3rd party analytics and it requires a star schema in our database to create data cubes for analytics.. Firstly to create star schema in a database, is there any specific parameter/clause or we can just create a user and create fact/dimension tables and call it a star schema with just how the data model looks like?  I had a question before if the regular schema can be made a star schema, but after reading the documentation it appears that we will have to create another database and create fact/dimension tables and set star_transformation_enabled to TRUE on this database. Since this parameter is set at database level we cannot have a regular schema and a star schema in one database. Please correct me if my assumption is wrong..

Looking forward to your replies.. Thank you all so much.

>Please correct me if my assumption is wrong..

Your assumption is wrong.

post SQL & results that show difference between star schema & non-star schema

STAR_TRANSORMATION_ENABLED=TRUE is NOT required for star schema.

STAR_TRANSORMATION_ENABLED=TRUE has NO impact on non-star schema

post DDL that creates star schema (just the user & not any objects)

post DDL that creates non-star schema (just the user & not any objects)

what is different between the two different users above?

Do you realize & understand that Oracle database invariably contains multiple schemas simultaneously

Thank you for your reply. Since we are still in researching and in starting phase on this I don't have SQL's or results to post.. I understand that Oracle database contains multiple schemas simultaneously,  I was not sure if the star schema(yet  to design) can be created on our existing database along with other non-star schemas and I was also confused about the star_transformation_enabled parameter as it is enabled at the database level.  But your response makes it clear that both star and non-star schemas can be created in the same database and we can set star_transformation_enabled to true at database level since this does not impact non-star schemas in the database.

post DDL that creates star schema (just the user & not any objects)
post DDL that creates non-star schema (just the user & not any objects)

what is different between the two different users above?

I think there will not be any difference in creating user for star or non-star schemas since only the table structure and design would be different in both.. Please correct me if I'm wrong...

Thanks again for all your help

Since we are still in researching and in starting phase on this I don't have SQL's or results to post..
. . .
I was not sure if the star schema(yet  to design) can be created on our existing database along with other non-star schemas

IMHO you still seem to be too focused on 'solutions' rather than the actual requirements you need to fulfill.

Any 'solution', such as star schemas, depends on knowing the requirements. You aren't posting your actual requirements - you keep talking about star schemas without any indication your 'unknown' requirements even call for them.

and I was also confused about the star_transformation_enabled parameter as it is enabled at the database level.

That is EXACTLY what I meant above. It is PREMATURE to talk about modifying parameters until you have a use case for them. You don't yet have any 'star schema'. And even if you did it doesn't mean you need to set/alter that parameter.

Quit worrying about the future and focus on the present.

1. determine your requirements

2. identify architecture and code solutions that can address those requirements

3. select one or two of those to prototype and test

4. select the 'best' one based on your testing

You use 'star schemas' as part of a solution to a problem. You have not yet identified, or posted, any actual problem.

But your response makes it clear that both star and non-star schemas can be created in the same database and we can set star_transformation_enabled to true at database level since this does not impact non-star schemas in the database.

Read some of the docs I provided.

The term 'star schema' does NOT relate to Oracle schemas/users. The term is really a misnomer because it has NOTHING to do with 'schemas'.

It has EVERYTHING to do with the architecture of a data model, or a subset of a data model.

A 'star schema' is typically some set of tables that are related to each other as those docs describe; a simple case being one FACT table and one or more DIMENSION tables. The fact table would have a column with a foreign key value to the dimension table.

That 'set of tables' might be in the same OLTP database or might be in a different data warehouse/reporting database.

The type of index (heap, bitmap, bitmap-join) on those FK columns depends on your specific requirements. They may, or may not, be bitmap indexes - it depends. Bitmap indexes are also a 'solution' to a problem. Until you identify a problem requiring bitmap indexes you don't use or need them.

Follow those four steps above - focus on the requirements. Any parameter modification would be the LAST thing you should be concerned about.

Thank you for your support. At this point, I myself I'm not aware of the actual requirements yet. I've just been told to gather some basic information on this.. Your suggestions/links helped me a lot and I will continue to read more on this..

Also, could you please provide suggestions on how to populate dimension tables in a star schema from tables in other schemas in the same database?

Thanks a  lot for all your help

2817195 wrote:
 
Also, could you please provide suggestions on how to populate dimension tables in a star schema from tables in other schemas in the same database? 

use appropriate DML statement