Friday, 13 February 2009
SSAS: Optimisation tips
1. Use NON_EMPTY_BEHAVIOR on all base measures which make up calculated member: NON_EMPTY_BEHAVIOR = { [Value 1], [Value 2] }E.g.CREATE MEMBER CURRENTCUBE.[MEASURES].[Base Cost Price] AS iif([Measures].[Quantity] <> 0, [Measures].[Base Cost]/[Measures].[Quantity], null) , FORMAT_STRING = "#,#.00" , NON_EMPTY_BEHAVIOR = { [Base Cost], [Quantity] } , VISIBLE = 1 ;
2. To speed up initial cube load, set default measure of cube to measure not using (Properties at the Measure Group level): Then for that default cube measure scope it-
SCOPE([MEASURES].[COUNT]); This = Null;END SCOPE;
3. Set defaults on Cube by scoping.
4. MDX PERFORMANCE:
a. Use IS When you need to check the value of a member, use IIF [Customer].[Company] IS [Microsoft] and not IIF [Customer].[Company].Name = “Microsoft”. The reason that IS is faster is because the Query Execution Engine does not need to spend extra time translating members into strings.
b. Use the Format String propertyInstead of applying conditional logic to return customized values if the cell is EMPTY or 0, use the Format String property. The Format String property provides a mechanism to format the value of a cell. You can specify a user-defined formatting expression for positive values, negative values, zeros, and nulls. The Format String display property has considerably less overhead than writing a calculation or assignment that must invoke the Query Execution Engine. Keep in mind that your front-end tool must support this property.
c. Avoid late-binding functionsWhen writing MDX calculations against large data sets involving multiple iterations, avoid referencing late binding functions whose metadata cannot be evaluated until run time. Examples of these functions include: LinkMember, StrToSet, StrToMember, StrToValue, and LookupCube. Because they are evaluated at run time, the Query Execution Engine cannot select the most efficient execution path.
D. Eliminate redundancyWhen you use a function that has default arguments such as Time.CurrentMember, you can experience performance benefits if you do not redundantly specify the default argument. For example, use PeriodsToDate([Date].[Calendar].[Calendar Year]) instead of PeriodsToDate([Date].[Calendar].[Calendar Year], [Date].Calendar.CurrentMember). To take advantage of this benefit, you must ensure that you only have one default Time Hierarchy in your application. Otherwise, you must explicitly specify the member in your calculation.
E. Ordering expression argumentsWhen writing calculation expressions like “expr1 * expr2”, make sure the expression sweeping the largest area/volume in the cube space and having the most Empty (Null) values is on the left side. For instance, write “Sales * ExchangeRate” instead of “ExchangeRate * Sales”, and “Sales * 1.15” instead of “1.15 * Sales”. This is because the Query Execution Engine iterates the first expression over the second expression. The smaller the area in the second expression, the fewer iterations the Query Execution Engine needs to perform, and the faster the performance.
4. Use Scope instead of case where possible.
5. IIF is better for performance then Case
2. To speed up initial cube load, set default measure of cube to measure not using (Properties at the Measure Group level): Then for that default cube measure scope it-
SCOPE([MEASURES].[COUNT]); This = Null;END SCOPE;
3. Set defaults on Cube by scoping.
4. MDX PERFORMANCE:
a. Use IS When you need to check the value of a member, use IIF [Customer].[Company] IS [Microsoft] and not IIF [Customer].[Company].Name = “Microsoft”. The reason that IS is faster is because the Query Execution Engine does not need to spend extra time translating members into strings.
b. Use the Format String propertyInstead of applying conditional logic to return customized values if the cell is EMPTY or 0, use the Format String property. The Format String property provides a mechanism to format the value of a cell. You can specify a user-defined formatting expression for positive values, negative values, zeros, and nulls. The Format String display property has considerably less overhead than writing a calculation or assignment that must invoke the Query Execution Engine. Keep in mind that your front-end tool must support this property.
c. Avoid late-binding functionsWhen writing MDX calculations against large data sets involving multiple iterations, avoid referencing late binding functions whose metadata cannot be evaluated until run time. Examples of these functions include: LinkMember, StrToSet, StrToMember, StrToValue, and LookupCube. Because they are evaluated at run time, the Query Execution Engine cannot select the most efficient execution path.
D. Eliminate redundancyWhen you use a function that has default arguments such as Time.CurrentMember, you can experience performance benefits if you do not redundantly specify the default argument. For example, use PeriodsToDate([Date].[Calendar].[Calendar Year]) instead of PeriodsToDate([Date].[Calendar].[Calendar Year], [Date].Calendar.CurrentMember). To take advantage of this benefit, you must ensure that you only have one default Time Hierarchy in your application. Otherwise, you must explicitly specify the member in your calculation.
E. Ordering expression argumentsWhen writing calculation expressions like “expr1 * expr2”, make sure the expression sweeping the largest area/volume in the cube space and having the most Empty (Null) values is on the left side. For instance, write “Sales * ExchangeRate” instead of “ExchangeRate * Sales”, and “Sales * 1.15” instead of “1.15 * Sales”. This is because the Query Execution Engine iterates the first expression over the second expression. The smaller the area in the second expression, the fewer iterations the Query Execution Engine needs to perform, and the faster the performance.
4. Use Scope instead of case where possible.
5. IIF is better for performance then Case
SQL: Connecting to other servers
/*Add linked server*/
EXEC sp_addlinkedserver
'Server name', 'SQL Server'GO
EXEC sp_addlinkedserver
'Server name', 'SQL Server'GO
Wednesday, 4 February 2009
SSRS: Changing the Stylesheet in Reporting Services
This blog applies for both SQL Server Reporting Services 2005 & 2008.
There are 2 steps to modifying the stylesheet -
Step 1: Create the stylesheet
1-Navigate to
2-Modify the stylesheet as appropriate and then save in the style folder
Step 2: Point to the new StyleSheet
1-Navigate to:
C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services
2-Modify the RSReportServer.config file and add the following text:
3-Now navigate to: http://ServerNameHere/ReportServer and select your reports.
Alternatively you can just add the StyleSheet to the URL:
&rc:Stylesheet=MyStyleSheet
Subscribe to:
Posts (Atom)