Desktop productivity for business analysts and programmers

Updating an OLAP CUBE: Measures and aggregations

Reply
New Contributor
Posts: 4

Updating an OLAP CUBE: Measures and aggregations

[ Edited ]

Hi,

 

I'm using EG to visualize the cube, but i've built it with the wizard. The source data is a star model.

 

In my process, I need to have the option to rebuild completely or to update it with new information. The code that i use to update is:

 

Captura.JPG

 

The data, wants to calculated the average of the prima and to count the number of observation of each type and subtype of policy in the period of time selected. (Time Dimension is Year - Month - Week - Day, type = STANDARD). Maybe we see this info by days or by month,etc.

 

The measures built are:

 

Capturaº.JPG

 

I have some questions about that:

 

      1. What means that the STAT = AVG needs N and SUM measures? I don't know exactly how to connect these two measures with AVG. How would be correct?

 

       2. It is always needed an Aggregation? In that case, the measure won't do all job? Whats the difference between an aggregatino and a measure?

 

       3. Why when I add some data, my dimension tables are updated and the dimension of the cube also, but if i delete one member of the dimension, it isnt delete from SAS OLAP CUBE?

 

       4. PRINCIPAL QUESTION: Why when I update the cube with the code shown, the measure Num.policies, SUM again?

 

                     p.e  -> Creation of the CUBE :  on 20160601, there is 2 policies of TYPE = 1 and SUBTYPE = 2.

                            -> Update the CUBE, without adding nothing (so it shouldnt do anything), i get : 

                                                                      on 20160601, there is 4 policies of TYPE = 1 and SUBTYPE = 2.

                           -> every update is the same:  

                                                                      next update without adding new information: 6 policies

                                                                      next update without adding new information: 8 policies

 

        I have defined wrong the measure? It only happend with STAT = N. Why first time the measure is okay? The code of updating           it is wrong?

 

If you know any of these answers, I would very happy to know them.

 

Thanks,

 

 

 

Esteemed Advisor
Posts: 5,198

Re: Updating an OLAP CUBE: Measures and aggregations

1. It just means that for calculating AVG you need to know the SUM and no observations that contributed to that SUM - N. Those Nedd to be specified during cube build time. AVG can be defined later since N and SUM is already there. You don't have to do anything other than specifying AVG, it uses N and SUM automatically.

2. You don't need aggregations for the cube to work, but if the cube is large aggregations are recommended to speed up queries.
Aggregations are calculated for a limited set of member levels, but for all stored measures. I think this fairly well described in the documentation.
Data never sleeps
Ask a Question
Discussion stats
  • 1 reply
  • 152 views
  • 3 likes
  • 2 in conversation