1

Topic: Architecture for Oracle BI

Day kind!
I want to consult to skilled colleagues as it is better to make architecture for the type table:
1 Mesjats1 Summa1 Status1 Polzovatel1
2 Mesjats1 Summa2 Status2 Polzovatel1
3 Mesjats2 Summa3 Status1 Polzovatel2
4 Mesjats2 Summa4 Status1 Polzovatel2
And
In that that the totals on the user it is impossible to add complexity (?), but it would be desirable to solve questions like:
1. To receive the last total on 1 for each user and to add their totals.
In that case I thought to use different grouping on dimension, I as understand if there is a calendar  it works as though to search for analytically maximum date and then the total to it. But I yet did not check. What nuances can be at such structure? It is a lot of simply such statuses and almost on everyone to calculate a separate index, and there not only the total, and still an amount and , seems to me not abruptly.
2. Still want, more difficult variant when it is necessary to find the last date on 1 on each user but so that was previous 2 and not is mandatory sequentially previous.
Here to me in a head came to hold separately only a field on necessary before. To statuses, but it certainly far imperfect variant:)
I will be glad to any help, can eat any articles on a similar subject, I unfortunately could not find. Well and generally if had experience with similar decisions how not to collect all rake?

2

Re: Architecture for Oracle BI

And in what a problem and why it is impossible to make as you drew? Only what for a separate field with a month to store?
I so understand that your label can quite look as  without  by date as it will be necessary to scan your requests precisely not one, two partitions in that case.  a field with the link to measurement with the user, the status and all by and large. If think that ID the user in _F to the table will change at it should eat to be the invariable identifier in most dimension which will use.

3

Re: Architecture for Oracle BI

Be or not to be...;
Simply I did not check such decision, and I am afraid that missed something, or will be considered too slowly. I certainly test, but it would be desirable to listen to those at whom it is used in fight. Month is necessary for partitions on the period, and within the limits of it the last date under the status, instead of under all table will be searched. And generally it is simply unusual to me to do storage on dates, instead of on months,  in "a broad gull" format, instead of on the end of month, thought can advise what to esteem with similar examples.

4

Re: Architecture for Oracle BI

It is not necessary to store month in  to the table only for the sake of , it can and be made by date
Top levels of hierarchies store separately normally if an index  (as at you with users it turned out) i.e. if the total for all days of month is not equal to the data for a month and they need to be stored separately, rarely but happens
Make normal dimension dates and connect it to the fact,
It makes sense to think about separate dimension with the user and the status, on the user the fact , can it turns out  to make it? It is necessary to collect all wishes on requests and to twist them, on the basis of one request many outputs you will not make

5

Re: Architecture for Oracle BI

Nenormalka;
To you is, of course, more visible about an essence of your entities, but it seems that  by date just  quite classical. And if are necessary both day, and the monthly facts monthly aggregates are under construction. But, besides, it depends on coming requests. For tables of type of operations, payments and all other it is absolutely not pertinent  as requests can ask the data both for a day, and for a month, 20 days, "date of the last cancellation" more often, "date of the first output", etc. that all leads to the full table scan. How many the data at you, tells? Perhaps there 100 lines only...

6

Re: Architecture for Oracle BI

Be or not to be...;
Tried as a result on an example and it does not work: (
Dimension... Formula
Other............ SUM ("ModelName". "F actTableName".Sales)
Date............. LAST ("ModelName". "FactTableName".Sales)
That is it takes the last total of the period, for example for two months, but does not consider the user, in every possible way tried to palm off on it of the user, but it did not turn out: (there Can be someone knows as it to make?
I want from such data set:
1 Mesjats1 Summa1 Status1 Polzovatel1
2 Mesjats1 Summa2 Status2 Polzovatel1
5 Mesjats2 Summa5 Status1 Polzovatel1
3 Mesjats2 Summa3 Status1 Polzovatel2
4 Mesjats2 Summa4 Status1 Polzovatel2
To receive at a choice of Mesjatsa1 and Mesjats2, where =1:
5+4
It is possible generally such to make?

7

Re: Architecture for Oracle BI

~

with t as (
select ' Data1'a, ' Mesjats1'b, 1 c, ' Status1'd, ' Polzovatel1'e from dual
union select ' Data2 ', ' Mesjats1 ', 2, ' Status2 ', ' Polzovatel1 ' from dual
union select ' Data5 ', ' Mesjats2 ', 5, ' Status1 ', ' Polzovatel1 ' from dual
union select ' Data3 ', ' Mesjats2 ', 3, ' Status1 ', ' Polzovatel2 ' from dual
union select ' Data4 ', ' Mesjats2 ', 4, ' Status1 ', ' Polzovatel2 ' from dual
), t2 as (
select e, last_value (c) over (partition by e order by c) c
from t
)
select e, sum (c)
from t2
group by e;

8

Re: Architecture for Oracle BI

orawish;
Remarkable request, I such too am able to write:) but a question that as it to make in OBIEE?

9

Re: Architecture for Oracle BI

Nenormalka;
The best answer, to itself it is visible:) Suddenly to whom it is useful, for to me the order seemed to the strange.
That  added the totals on the user, then took them on the last date and then again added the order it is necessary to specify such-> Others sum (), dim_calendar last (), dim_ sum ()