Refreshing Cube Data using a Sheduled Job

Question

Refreshing Cube Data using a Sheduled Job

pcooper

Grasshopper

Points: 19
More actions
April 17, 2002 at 7:23 am

#104793

Do you know the syntax for refreshing a cube. I would like to schedule a job that performs this task each night. I'm using 7.0 with sp3.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

jsqldba SSChampion Points: 11257 More actions · Answer 1

Set up a DTS task with an OLAP Services Processing task then schedule the job to run the DTS task. I believe the OLAP Services Processing task is an add-in included in the Service Packs.

Gary Billins SSC-Addicted Points: 454 More actions · Answer 2

When processing a cube using the DTS task one can select the big box icon (a cube) vs a 4 little box icon (a partition). If I select a partition the option Refresh Data is greyed out. I have been selecting the cube and the Refresh Data option.

When does one use the Full Process on the Cube or the Full Process on the Partition option?

mhweiss SSCarpal Tunnel Points: 4284 More actions · Answer 3

It is my understanding that you would need to do a full process when the underlying structure of the cube or dimension changes, ie adding new fields, deleting fields, etc. If simply adding additional data, I believe you would only need to either refresh the cube and dimensions or do an incremental update taking care not to cause duplicate aggregation of values...

hth,

Michael

quote:
When processing a cube using the DTS task one can select the big box icon (a cube) vs a 4 little box icon (a partition). If I select a partition the option Refresh Data is greyed out. I have been selecting the cube and the Refresh Data option.
When does one use the Full Process on the Cube or the Full Process on the Partition option?

Michael Weiss

Gary Billins SSC-Addicted Points: 454 More actions · Answer 4

All my dimensions are shared.

All my dimensions (except time) are defined to be changing dimensions.

Do I have to process my dimension data incrementally?

I though Dimension incrementaly handled only new dimension members where as full would handle changes to existing members like member properties changes and changes to relationships between members.

Thanks

Gary

mhweiss SSCarpal Tunnel Points: 4284 More actions · Answer 5

It is my understanding that incremental processing does not reflect changes to existing members unless those members are included in the where clause for the incremental processing. Full processing does take into account all members - existing and new. The available literature on this has always been rather vague to me...for example, if I only add new data to the supporting data warehouse, can I rely on the refresh method only? Others' thoughts, experiences would be appreciated on this matter...

Michael

Michael Weiss

Scot J Reagin SSC-Addicted Points: 468 More actions · Answer 6

Incremental updates of dimensions (meta data) and cubes (data) operate under somewhate different rules. Incrementally updating a non-changing dimension will add new members as well as update member property values. It will not, however, delete members removed from the underlying data source not will it update relationships between members (IE: moving a member to another parent).

Incremental updates of cubes creates a potential issue with data duplication since existing intersection values are retained and added to with 'new' data rows. It's imperative, therefore, that incremental cube updates are defined with valid where clauses or unique data sources.

Finally, whether a cube schema has been optimized has an impact on dimension and cube updates. If the cube schema has not been optimized, fact data has been added for new members, but the dimensions have not been updated, the facts will not be processed into the cube (since these facts are not in the result set from the cube processing fact to dim joins). In the same scenario, with the cube schema optimized, an error will occur during cube processing since facts will be found without corresponding dimension members.

Scot J Reagin

sreagin@aspirity.com

Scot J Reagin
sreagin@hitachiconsulting.com

Gary Billins SSC-Addicted Points: 454 More actions · Answer 7

Regarding the statement:

It will not, however, delete members removed from the underlying data source nor will it update relationships between members (IE: moving a member to another parent).

Is this the only benefit of having a changing dimension? If so then I would expect that changing dimensions would be not used very often....

if a data warehouse is to contain historical information then members would never be deleted.

For example

I have a sales Cube. My product dimension is defined to be changing. I will never delete a product because there an historical sale for that product. But a product's classification my change over time. It may be

be associated with a parent Category 1 for a year and is reclassified as Category 2 later. Is this what is meant by the expression "moving a member to another parent". But my current design of the cube have implemented all these "relationships" as

member properties of product.

In order to take "advantage" of my changing

product dimension, I would have to

create a independent dimension for Category and then I would have a relationship not stored as a member property of product and therefore would have different parents over time.

Does this sound correct?

Thanks

Gary