Home » SQL & PL/SQL » SQL & PL/SQL » Need your help in redesign a table (Oracle 10 G)
Need your help in redesign a table [message #428755] Thu, 29 October 2009 18:33 Go to next message
Sumango
Messages: 4
Registered: January 2006
Junior Member
Hi All,
Need your valuable suggestions to effectively redesign.

Scenario:
I have a table which contains forecast data and each record has 1 month of forecast. The way reporting is done based on this table is that the table is used 12 times in the query to get forecast related information for different months. I wanted to redesign this table to make it have 12 months of forecast in one record, the catch here is if the person wants to see forecast for cross year then there will be a problem. I want your expertise to design the table in such a way that even it satisfies the cross year case.


Current query used to get data.

SELECT
sp.*,mo1.*..................
FROM
zgmi_combined_with_sp_pos_v sp,
zgmi_sp_forecast_by_period_v m01,
zgmi_sp_forecast_by_period_v m02,
zgmi_sp_forecast_by_period_v m03,
zgmi_sp_forecast_by_period_v m04,
zgmi_sp_forecast_by_period_v m05,
zgmi_sp_forecast_by_period_v m06,
zgmi_sp_forecast_by_period_v m07,
zgmi_sp_forecast_by_period_v m08,
zgmi_sp_forecast_by_period_v m09,
zgmi_sp_forecast_by_period_v m10,
zgmi_sp_forecast_by_period_v m11,
zgmi_sp_forecast_by_period_v m12
WHERE
sp.flt_request_id=32386 AND
sp.position_id = m01.forecast_position_id(+)
AND m01.period_type_name(+) = 'Month'
AND m01.period_start_date(+) = TO_DATE('2009-01-01','YYYY-MM-DD HH24:MI:SS')
AND sp.position_id = m02.forecast_position_id(+)
AND m02.period_type_name(+) = 'Month'
AND m02.period_start_date(+) = ADD_MONTHS(TO_DATE('2009-01-01','YYYY-MM-DD HH24:MI:SS'),1)
AND sp.position_id = m03.forecast_position_id(+)
AND m03.period_type_name(+) = 'Month'
AND m03.period_start_date(+) = ADD_MONTHS(TO_DATE('2009-01-01','YYYY-MM-DD HH24:MI:SS'),2)
AND sp.position_id = m04.forecast_position_id(+)
AND m04.period_type_name(+) = 'Month'
AND m04.period_start_date(+) = ADD_MONTHS(TO_DATE('2009-01-01','YYYY-MM-DD HH24:MI:SS'),3)
AND sp.position_id = m05.forecast_position_id(+)
AND m05.period_type_name(+) = 'Month'
AND m05.period_start_date(+) = ADD_MONTHS(TO_DATE('2009-01-01','YYYY-MM-DD HH24:MI:SS'),4)
AND sp.position_id = m06.forecast_position_id(+)
AND m06.period_type_name(+) = 'Month'
AND m06.period_start_date(+) = ADD_MONTHS(TO_DATE('2009-01-01','YYYY-MM-DD HH24:MI:SS'),5)
AND sp.position_id = m07.forecast_position_id(+)
AND m07.period_type_name(+) = 'Month'
AND m07.period_start_date(+) = ADD_MONTHS(TO_DATE('2009-01-01','YYYY-MM-DD HH24:MI:SS'),6)
AND sp.position_id = m08.forecast_position_id(+)
AND m08.period_type_name(+) = 'Month'
AND m08.period_start_date(+) = ADD_MONTHS(TO_DATE('2009-01-01','YYYY-MM-DD HH24:MI:SS'),7)
AND sp.position_id = m09.forecast_position_id(+)
AND m09.period_type_name(+) = 'Month'
AND m09.period_start_date(+) = ADD_MONTHS(TO_DATE('2009-01-01','YYYY-MM-DD HH24:MI:SS'),Cool
AND sp.position_id = m10.forecast_position_id(+)
AND m10.period_type_name(+) = 'Month'
AND m10.period_start_date(+) = ADD_MONTHS(TO_DATE('2009-01-01','YYYY-MM-DD HH24:MI:SS'),9)
AND sp.position_id = m11.forecast_position_id(+)
AND m11.period_type_name(+) = 'Month'
AND m11.period_start_date(+) = ADD_MONTHS(TO_DATE('2009-01-01','YYYY-MM-DD HH24:MI:SS'),10)
AND sp.position_id = m12.forecast_position_id(+)
AND m12.period_type_name(+) = 'Month'
AND m12.period_start_date(+) = ADD_MONTHS(TO_DATE('2009-01-01','YYYY-MM-DD HH24:MI:SS'),11)

Hope to get your valuable inputs. Thanks.
Re: Need your help in redesign a table [message #428763 is a reply to message #428755] Thu, 29 October 2009 20:20 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
why do you want to change the design?

what is your reason?

Based on the basic description, my first impression is there is no simple design that will handle cross year calculations. The bottom line is not matter how big you make your period of time covered by your table, you will always have the issue of what to do if you need to span period boundries.

I look forward to seeing other opinions.

Kevin

[Updated on: Thu, 29 October 2009 20:20]

Report message to a moderator

Re: Need your help in redesign a table [message #429232 is a reply to message #428763] Mon, 02 November 2009 11:35 Go to previous messageGo to next message
Sumango
Messages: 4
Registered: January 2006
Junior Member
Hi Kevin,
Thanks for your reply. To give more insite about my question. There is a Application which is the source and people built views on top of the application for reporting.

The reason I am asking for redesign is because, I have a requirement to build a environment which will support adhoc reporting. So the existing views will not really make things easy for adhoc reporting. Thats why I am asking to redesign the table or build a view which will support adhoc reporting.

Hope this should give you a good picture of what I am intending to do. Kindly let me know your thoughts and ideas. Thanks.
Re: Need your help in redesign a table [message #429233 is a reply to message #429232] Mon, 02 November 2009 11:40 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
Still, I think the following:

1) the design you have now is looks like the right design and maybe it should not be changed!

2) you can build any view you like without changing the table

3) you need to get more info on what people mean by AD-HOC querying. Get some examples of the queries people will want to execute.

In the end, you may need to point out that there will be limitations that you cannot work around.

Maybe some STAR design could help you out.

Good luck, Kevin
Re: Need your help in redesign a table [message #429249 is a reply to message #429233] Mon, 02 November 2009 15:01 Go to previous messageGo to next message
Sumango
Messages: 4
Registered: January 2006
Junior Member
Hi Kevin,
Thanks again for your reply. Yes you got it, I am trying to build a Star schema for adhoc reporting. But the problem what I am seeing with the data being stored rowise. So if the person wants to look at 12 months of forecast in a row, they have to do a cross tab report.

For the kind of situation which I have, do you think is there any standard when it comes to reporting adhoc from a star schema. Kindly let me know your ideas. Thanks.
Re: Need your help in redesign a table [message #429252 is a reply to message #429249] Mon, 02 November 2009 15:58 Go to previous message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
NO, star schemas are designed usually with a time period for each row. To change the layout of a star schema as you have described would render it useless. It would be the worst thing you could do.

Yes if your users want a cross tab report then let them build a cross tab report. If you want to provide a view that does the flattening of the cross tab then by all means go ahead. Oh wait, you did that. See, you have the right idea already.

If you are building a star schema, then do not abandon the principles of star design.

Indeed, no matter what design philosophy you use, it will never be good for all kinds of reporting. Suppose I want to do a cross tab for a six month period... how does your 12 month view make that easier. Again there is no perfect solution for all reporting needs.

Remember also that many third party tools exist that can do reporting off star schemas. But if you change the table to be a flat row of months then these tools can not help you because they expect to see a correct star model.

So... I'll say it again... if you are creating a star schema, use star design techniques and use views on top of it to present alternative pictures of the data for specific situations, but only if you really need them. This sounds to me like what you have already done.

If someone is driving you to "MAKE IT EASIER" then put the responsibility back on them to describe what "MAKE IT EASIER" really means. If they cannot carry the idea forward from some vague concept to actionable material, then why should they expect that you could build that which they cannot describe.

Good luck, Kevin

[Updated on: Tue, 03 November 2009 11:12]

Report message to a moderator

Previous Topic: dbms_crypto_toolkit_types (merged)
Next Topic: merging select into one
Goto Forum:
  


Current Time: Fri Sep 30 05:29:10 CDT 2016

Total time taken to generate the page: 0.28836 seconds