The MDX script on Microsoft Analysis Service (SSAS) 2008 and 2005 are different from SQL 2000. Here’s an example how to calculate average using MDX script on Analysis Service 2008. SSAS 2008, How to calculate Net Value By Date (NetValue / Date)
|
1 2 3 4 5 6 7 |
AVG( Descendants( [Time].[Calendar].CurrentMember, [Time].[Calendar].[Date] ), [Measures].[NetValue] ) |
Old one, SSAS 2000, How to calculate Net Value By Date (NetValue… Continue reading
The last method (workaround) to solve a lock status is by killing the process. Write this line to Kill a process in sql server:
|
1 2 3 4 |
--This example shows how to terminate SPID 55. KILL 55 --This example generates a status of the rollback process for the specific spid. KILL 55 WITH STATUSONLY |
–This is the progress report.spid 55: Transaction rollback in progress. Estimated rollback completion: 70% Estimated time left: 15 seconds.
IIf function often used in MultiDimensionalExpression (MDX), It’s quiet simple to implement single block of IIf block. This an example of Multiple IIf logic block. Example1:
|
1 2 3 4 |
IIf(Measures.[Store Sales] > 50000, "Good", IIf(Measures.[Store Sales] > 30000, "Average", IIf(Measures.[Store Sales] > 10000, "Poor", "No"))) |
A logical script in MDX to perform an “If..Then.. Else..” block.
|
1 |
IIf(«Logical Expression», «Numeric/String Expression1», «Numeric/String Expression2») |
Example1:The following example returns 0 if Measures.CurrentMember is an empty cell, 1 otherwise:
|
1 |
IIf(IsEmpty(Measures.CurrentMember), 0, 1) |
Example2:The following string returns the string “Yes” if Measures.CurrentMember is an empty cell, the string, “No” otherwise:
|
1 |
IIf(IsEmpty(Measures.CurrentMember), "Yes", "No") |
Example3:
|
1 2 3 4 5 |
IIf( [Scenario].CurrentMember IS [Scenario].[Budget], [Measures].[Budget Unit Sold], [Measures].[ActualUnit Sold] ) |
Example4:Average Sales (Measure) By Time Day (Dimension)
|
1 2 3 4 5 6 7 |
IIf ( ([Time].currentmember.level.name = "(All)" OR [Time].currentmember.level.name = "Year" OR [Time].currentmember.level.name = "Quarter" OR [Time].currentmember.level.name = "Month" OR [Time].currentmember.level.name = "Day" ),AVG(Descendants(Time.CurrentMember,[Time].[Day]),Measures.[Sales]),0) |
Source:… Continue reading
There’s a few scenario to calculate a percentage of a measure in OLAP CUBE (Analysis Service 2000). A Percentage is common calculation in MDX , and it can be solved by divide a measure to total value (Grand Total). Sample 1:Measures.[Value Contribution to Total]:
|
1 |
(Measures.[Value] / (Measures.[Value], Time.[All Time], Products.[All Products],…) |
Sample 2:Measures.[Value Contribution to Total]:
|
1 |
(Measures.[Value] / (Measures.[Value], Products.[All Products],Warehouses.[All Warehouses]) |
Sample 3:Measures.[Product Contribution to… Continue reading
sql server 2000 one of most powerful and easy to use database application. It’s nicely handle giga size data. Unfortunately the growth of the log file can be very frustrating. Sometimes there’s a certain difficulty when we want to shrink the log file, mainly because the database still running a process. usually we can use… Continue reading