Tuesday, August 30, 2011

SSIS: How SSIS Configurations are applied

SSIS configurations are applied on runtime.  Here are 2 details it’s very important to know about how configurations are applied:
Applied once only: Configurations are applied once only, at the beginning of the Package running. All values that are configured are loaded in this time, and not at real-time when they are used in the control flow task. It means that you cannot edit SSIS Configurations sources after package started running (more exactly it won’t apply on current running).
Configurations Order:  When you have multiple SSIS configurations, SSIS applies them is the order they are defined in SSIS configurations window, except “Parent variable” that is always applied at the end, no matter where it’s positioned. Keep it in mind if 2 configs overwrite the same value, to ensure the final value will be the one you want! You can edit the order with the arrows on right side  of the edit window.

If you use dtexec command with configurations in command line, read Microsoft article about changes between SSIS 2005 and 2008.

Thursday, August 4, 2011

SSAS: Which change makes the cube/dimension unprocessed?

In many cases, some of OLAP cubes are huge and Full Process takes a lot of time.
Therefore, it’s important to know which change, when the updated solution is deployed, causes the cube to move to “Unprocessed” status and needs new full process. Dimension process status is also critical since if dimension moves to unprocessed, all cubes that have connection to this dimension will be unprocessed too.

Here is the detailed tested list of different actions, and how they apply on cube (tested on SSAS 2008. I don’t remember any difference with  SSAS 2005, but not certified)
We can summarize the results in one sentence: Every action that affects data will move cube or dimension status to unprocessed. Such changes are also called structural changes, meaning any change that apply on cube/dimension structure (new element, edition on aggregation method, etc.) and therefore will require data loading or reloading.
One exception: Adding or deleting translation to dimension unprocesses it when deployed. I don’t know why..
And now to the detailed list:
Type
Action
Unprocess cube/dimension?
Cube
New measure group
Yes
Cube
New measure
Yes
Cube
Edit measure aggregation method
Yes
Cube
Measure format
No
Cube
Measure name
No
Cube
Measure display folder
No
Cube
ErrorConfiguration edition
No
Cube
Edit dimension usage
Yes
Cube
Calculations
No
Cube
Add, edit or delete kpi
No
Cube
Add, edit or delete action
No
Cube
Edit partition query
No, not applied till next process.
Cube
Add new partition
No, new partition unprocessed.
Cube
Edit partition storage mode
No, but data is empty.
Cube
Create,edit and assign aggregations
No, not applied till next process.
Cube
Add, edit or delete perspective
No
Cube
Add, edit or delete translation
No
Dim
Add attribute to dim
Yes
Dim
Edit attribute name
No
Dim
Order by property of attribute
Yes
Dim
AttributeHierarchyVisible property
No
Dim
Edit attribute relationship
Yes
Dim
Add or delete translation to dim
Yes
Dim
Edit translation
No
Other
Add, Edit, Delete Role
No
Other
Edit data source
No, not applied till next process.
Other
Edit DSV
No


Finally, 3 remarks:
·         BIDS Helper add-in gives you possibility to deploy MDX script or aggregations only, so you can be sure nothing else is affected.
·         Use Deployment Wizard tool when you want to deploy without overwriting Partitions or roles, in cases they were edited in the OLAP DB.
·         If your cube is down and you need to have one measure group/partition available without processing the entire cube (because of process long time), you can first process the cube with “Process structure” Process mode. It will move cube status to Processed but without any data. Then you can process the specific part you want and it will be available.