Home » Other » General » Required suggestion on peculiar dimensional model (merged) (11g)
Required suggestion on peculiar dimensional model (merged) [message #611665] Sun, 06 April 2014 14:54 Go to next message
viju_05
Messages: 3
Registered: April 2014
Junior Member
Hi ,

I am follower of kimball approach to star schema model where snowflake should be avoided except in special cases.I am working on project where dimensions are having multiple hierarchies and client is asking to create normalized dimensions i.e. snowflaking.
They have an opinion that having seperate table for each hierarchy level will help them to isolate the change data values (if needed) will impact that particular level information.
But I think though they can create sepearte tables but instead of having snowflaking they should link directly to fact table as separte dimension.

Wants to know the opinion? Please suggest.

Thanks.
Vipul


[EDITED by LF: fixed topic title typo; was "Reuired"]

[Updated on: Sun, 06 April 2014 23:59] by Moderator

Report message to a moderator

Re: Reuired suggestion on peculiar dimensional model [message #611667 is a reply to message #611665] Sun, 06 April 2014 15:18 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Welcome to the forum. Please read our OraFAQ Forum Guide and please read How to use [code] tags and make your code easier to read

I would suggest that you should not blindly "follow" an "approach" without considering the environment. For example, a design that would perform well in DB2 might be a disaster in SQL Server.

Oracle databases occasionally perform badly with normalized snowflake schemas if you don't put any Oracle-specific thought into it. For example, intelligent use of materialized views, bitmap join indexes, hierarchies and dimensions, and reference partitioning may solve many problems. It isn't as simple as "de-normalize or not".
Re: Required suggestion on peculiar dimensional model [message #611684 is a reply to message #611665] Mon, 07 April 2014 02:24 Go to previous messageGo to next message
viju_05
Messages: 3
Registered: April 2014
Junior Member
Thanks Watson for the response.

I agree, we have ways to do the implementation based on database features. What I was saying having denormalize star schema has its own performance benefits so better to implement it whenever possible.
In my specific case, if you see example PPT, instead of having one denormalized "Department Structure" dimension with all hirerchiecal data they wants one table at each level & connect to fact "Cost center" in snowflake as shown.
The reason is , in case if client need to change any data at any level e.g. "Group" they only need to do updates on "Group" Table rest will not impact.
Is this a right approach? Do you agree?
I am not convinced as for simplicity of updates, we are doing snowflake which will impact reporting semantic layer (universe) and also needs to create MVs, join views, etc. for performance tunining.
To implement the same I feel let us create table at each level but connect them individually to result is star schema only instead of snowflaking.
Request to the group to share thoughts?

Thanks in advance.

Regards,
Vipul
Need suggestion to use Star or Snowflake for specific scenrio. [message #611726 is a reply to message #611665] Tue, 08 April 2014 02:13 Go to previous message
viju_05
Messages: 3
Registered: April 2014
Junior Member
Hi Modelling experts,

I have an question on Start schema Vs Snowflake.
In my specific case, if you see example PPT, instead of having one denormalized "Department Structure" dimension with all hirerchiecal data they wants one table at each level & connect to fact "Cost center" in snowflake as shown.
The reason is , in case if client need to change any data at any level e.g. "Group" they only need to do updates on "Group" Table rest will not impact.
Is this a right approach? Do you agree?
I am not convinced as for simplicity of updates, we are doing snowflake which will impact reporting semantic layer (universe) and also needs to create MVs, join views, etc. for performance tunining.
To implement the same I feel let us create table at each level but connect them individually to result is star schema only instead of snowflaking.
Request to the group to share thoughts?
Previous Topic: How to view current date audit records from DBA_AUDIT_TRAIL
Next Topic: high water mark
Goto Forum:
  


Current Time: Thu Mar 28 08:30:41 CDT 2024