![]() ![]() If we look at the Grand Total – it is only 3140 which is the total of Q3 only (House number 40 is excluded as it was on last Q1).īy discussing the above technique we have now understood how we can use this in our favor to produce different insight of data, and we love data because it gives us more valuable information than the entity/incident itself.įormer CEO of HP, Carly Florina once said – “The goal is to turn data into information, and information into insight.”, and this is exactly we want to do using BI and Analytics. So the resulting analysis is as following: Last Time’s Rent Selecting the last summation from Step 1 (i.e. Order by c1, c6, c5, c2, c3, c4 ) D1 where rownum <= 5000001Īs per the above physical sql – we are selecting the sum() with group by on the other attributes in the innermost query segment and then in subsequent steps we are selecting the last() value of that measure. Max(D1.c9) over (partition by D1.c6) as c9, SAWITH4 AS (select LAST_VALUE(D1.c3 IGNORE NULLS) OVER ( ORDER BY D1.c4 NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as c2 SAWITH3 AS (select sum(T285817.RENT_AMOUNT) as c3, LAST_VALUE(D1.c10 IGNORE NULLS) OVER (PARTITION BY D1.c9 ORDER BY D1.c9 NULLS FIRST, D1.c13 NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as c8, LAST_VALUE(D1.c12 IGNORE NULLS) OVER (PARTITION BY D1.c7, D1.c2, D1.c9 ORDER BY D1.c7 NULLS FIRST, D1.c2 NULLS FIRST, D1.c9 NULLS FIRST, D1.c13 NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as c6, LAST_VALUE(D1.c11 IGNORE NULLS) OVER (PARTITION BY D1.c7, D1.c4, D1.c5, D1.c2, D1.c9 ORDER BY D1.c7 NULLS FIRST, D1.c4 NULLS FIRST, D1.c5 NULLS FIRST, D1.c2 NULLS FIRST, D1.c9 NULLS FIRST, D1.c13 NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as c3, SAWITH2 AS (select distinct LAST_VALUE(D1.c10 IGNORE NULLS) OVER (PARTITION BY D1.c2, D1.c9 ORDER BY D1.c2 NULLS FIRST, D1.c9 NULLS FIRST, D1.c13 NULLS FIRST ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) as c1, SAWITH0 AS (select T42404.Per_Name_Month as c2,īISAMPLE.WC_APARTMENT_D T285805 /* Bi Data Solution Demo1 – Dim – Apartment */ ,īISAMPLE.SAMP_TIME_DAY_D T42404 /* D01 Time Day Grain */ ,īISAMPLE.BILL_RATE_FACT T285817 /* BI Data Solution Demo2 – Fact – Bill Rate */ Here we have set the summation of house as the first rule and then selection of last on time slice.Īnd this ordering yields the below physical sql: Let us re-order the dimension rules as follows: Let us start where we had ended our last discussion, we had created 3 dimension based rules as following order: Dimension Based Aggregation OrderingĪnd the above setting yields the physical sql which is selecting the last rent for every house and then sum it up. We will discuss about ordering of dimension based rule and how that changes the physical sql in OBIEE and which in turn changes the outcome of analysis. ![]() And we have also had a glimpse on ordering of selected dimension. We have already discussed about how to achieve Dimension based Aggregation Rule in OBIEE here. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |