November 19, 2016 at 11:03 pm
Can you post the DDL (creat table) script for the SUMMARY and the JASPER_REBATES_SUMMARY tables, the full code of the stored procesure and the actual execution plan please?
😎
November 21, 2016 at 9:12 am
Thank you for trying to post DDL. But tables must have a key, by definition. You have mixed data and metadata in the same table; a measurement name and a timescale are metadata, not the value. And why the heck did you pick the Oracle default numeric (38, 11) for a column? You copying this from something else, aren't you? Also, we would never materialize summary data in SQL. This was one of the major steps of RDBMS over filesystems; a virtual table is just as real as a base table; we put the calculations in a view that is always correct. Every time it is accessed.
The next thing you missed is that we have a convention from MySQL of using "yyyy-mm-00" for the month within the year and "yyyy-00-00" for the year as a whole. This is up for in ISO standard because it matches the ISO 8601 convention.
Also, a brand usually has a name and not an identifier. Can we try again with better postings?
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
November 21, 2016 at 9:43 am
Eirikur Eiriksson (11/19/2016)
Can you post the DDL (creat table) script for the SUMMARY and the JASPER_REBATES_SUMMARY tables, the full code of the stored procesure and the actual execution plan please?😎
I'd be curious to the grain of these 2 tables as well. Given your WHERE clauses like:
WHERE MEASURE_NAME='PRICE PROTECTION'
AND @Measures = 'AVERAGE PRICE '
AND @Time_Scale = 'MONTH'
It looks like there is multiple grains within the same SUMMARY table, creating a Centipede fact table?
If you're using something like Analysis Services on top of these tables, it might be better to utilize hierarchies within that to summarize data:
Viewing 3 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply