September 21, 2009
Posted in OBI EE tagged Business Model and Mapping Layer, OBI EE - Data Sorting, OBI EE RPD at 5:59 am by itsourteamwork
In our Time dim, we have a column, which displays the month as
FY2009 Nov
FY2009 Dec
…
When the data is sorted, BI treats the data as character data, i.e. FY2009 Dec will come first and FY2009 Nov will follow that.
The physical query fired by BI is
select distinct T5686.FISCAL_MTH_NAME as c1
from
D_PERIOD_MST T5686
order by c1
Our requirement was to sort the data as per the month nos while displaying the month names so that FY2009 Nov will come first then FY2009 Dec.
To do this, in the RPD, open the properties of the column whose auto-sort needs to be modified, click on ‘Set…” button. A window will open with the available columns in the dimension. Select the column which needs to be used for sorting and click on ok. Click on OK again to accept the changes.
Save the RPD and run query again. Now the month names will be dispalyed in the sorted order of the month no.
Here is the physical query fired by BI.
select distinct T5686.FISCAL_MTH_NAME as c1,
T5686.FISCAL_MTH as c2
from
D_PERIOD_MST T5686
order by c2
For the same query fired above, BI added the column which was set as the “Sort Order Column” as part the physical query
In this way, we can tell OBI EE to use different column for sorting the data of a column.
Permalink
September 19, 2009
Posted in Oracle, SQL tagged Functions in Oracle, LISTAGG Function in Oracle, New Features in Oracle 11gR2, New function in Oracle 11gR2 at 9:09 am by itsourteamwork
Prior to the release of Oracle 11gR2, if the list of employees needs to be displayed as ‘Semi-colon’ separated values along with the department id, the following SQL was used
|
SELECT t.department_id Dept
, MAX(SYS_CONNECT_BY_PATH(first_name,’ ;’)) as Employees
FROM (SELECT department_id
, first_name
, ROW_NUMBER() OVER (PARTITION BY department_id
ORDER BY department_id
, first_name) rn
FROM ( SELECT DISTINCT department_id , first_name
FROM employees)) t
START WITH t.rn = 1
CONNECT BY t.rn = PRIOR t.rn + 1
AND department_id = prior department_id
GROUP BY department_id; |
Output
|
Dept
|
Employees
|
|
10
|
; Jennifer
|
|
20
|
; Michael; Pat
|
|
30
|
; Alexander; Den; Guy; Karen; Shelli; Sigal
|
|
40
|
; Susan
|
|
50
|
; Adam; Alana; Alexis; Anthony; Britney; Curtis; Donald; Douglas; Girard; Hazel; Irene; James; Jason; Jean; Jennifer; John; Joshua; Julia; Kelly; Kevin; Ki; Laura; Martha; Matthew; Michael; Mozhe; Nandita; Payam; Peter; Randall; Renske; Samuel; Sarah; Shanta; Stephen; Steven; TJ; Timothy; Trenna; Vance; Winston
|
|
60
|
; Alexander; Bruce; David; Diana; Valli
|
|
70
|
; Hermann
|
|
80
|
; Alberto; Allan; Alyssa; Amit; Charles; Christopher; Clara; Danielle; David; Eleni; Elizabeth; Ellen; Gerald; Harrison; Jack; Janette; John; Jonathon; Karen; Lindsey; Lisa; Louise; Mattea; Nanette; Oliver; Patrick; Peter; Sarath; Sundar; Sundita; Tayler; William
|
|
90
|
; Lex; Neena; Steven
|
|
100
|
; Daniel; Ismael; John; Jose Manuel; Luis; Nancy
|
|
110
|
; Shelley; William
|
| |
; Kimberely
|
Now, Oracle 11gR2, Oracle provided a columnar function called LISTAGG
Syntax:
|
LISTAGG (measure_column_expr [, ‘delimiter’])
WITHIN GROUP (order_by_clause) [OVER query_partition_clause]
|
This LISTAGG function flattens the results into a single record by combining the values. This function can be used in one-to-many constructions.
One of the fun little features – to switch gears here – that is added to SQL is in analytics. We now have something called LISTAGG. LISTAGG allows you to flatten a result set into a single record. This is often
used in 1:many constructions such as hierarchical data and bill of materials.
The above output can be achieved by
|
SELECT department_id ,
LISTAGG(last_name, ‘; ‘)
WITHIN GROUP (ORDER BY hire_date) "Employees"
FROM employees
GROUP BY department_id;
|
For more info, click here
Permalink
September 16, 2009
Posted in Poem, Telugu tagged Maa telugu thalliki, Telugu, Telugu Song at 8:56 am by itsourteamwork
This is for all those Telugu people and Telugu language lovers
మా తెలుగు తల్లికి మల్లెపూదండ,
మా కన్నతల్లికి మంగళారతులు,
కడుపులో బంగారు కనుచూపులో కరుణ,
చిరునవ్వులో సిరులు దొరలించు మాతల్లి.
గలగలా గోదారి కదలిపోతుంటేను
బిరాబిరాక్రిష్ణమ్మ పరుగులిడుతుంటేను
బంగారు పంటలే పండుతాయీ
మురిపాల ముత్యాలు దొరులుతాయి.
అమరావతినగర అపురూప శిల్పాలు
త్యాగయ్య గొంతులో తారాడు నాదాలు
తిక్కయ్య కలములొ తియ్యందనాలు
నిత్యమై నిఖిలమై నిలచి వుండేదాకా
రుద్రమ్మ భుజశక్తి మల్లమ్మ పతిభక్తి
తిమ్మరసు ధీయుక్తి, కృష్ణరాయల కీర్తి
మా చెవులు రింగుమని మారుమ్రోగేదాక
నీ ఆటలే ఆడుతాం, నీపాటలే పాడుతాం
జై తెలుగు తల్లి ,జై తెలుగు తల్లి!!!
Permalink
September 14, 2009
Posted in Quotes tagged Motivation at 7:17 am by itsourteamwork
IMPOSSIBLE also say’s I’M POSSIBLE.
Permalink
September 12, 2009
Posted in Songs, Telugu tagged Poem from Premalayam, Songs, Telugu at 7:19 am by itsourteamwork
This is a poem is one of my favorites. This is from the move Premaalayam (dubbing version of Hum Aap Ke Hain Koun?)
ఎదురు చూపుల బాధలోనే ఎందుకో ఈ తీయదనం
విరహమన్నది లేకపోతే వలపుకేది వెచ్చదనం
ఎదురుపడితే ఎదను తెరచి చెప్పలేని మౌనతనం
కనులుపలికే మౌన భాషే తెలుపుతుంది చిలిపితనం
Permalink
September 9, 2009
Posted in Quotes at 7:45 am by itsourteamwork
Don’t promise anything when you are HAPPY or EXCITED
Permalink
September 7, 2009
Posted in Quotes at 7:14 am by itsourteamwork
Don’t take any decision when you are angry or hungry.
Permalink
September 4, 2009
Posted in Misc at 12:34 pm by itsourteamwork
Hi Welcome to Teamwork…
Before starting, we would like to thank
for their guidence.
Teamwork
Permalink
September 1, 2009
Posted in Misc at 10:53 am by itsourteamwork
its first post
Permalink
Next page
Itsourteamwork : More 'WE' Less 'I' 