Archive for February, 2009

Excel 2007 subcubes/subselects Issue

February 19, 2009 Leave a comment
I had problems with different results generated by Excel 2007 and BIDS. Michael Barrett well explained the reason in its blog. The below is his original post at

MDX: EXISTING operator and sub-selects

by Michael Barrett

This blog entry is
about the use of the EXISTING operator in MDX calculations (well,
really it is about one of the pitfalls of using EXISTING). The operator
is new to Analysis Services 2005 and can be used to filter a set by the
current coordinate in the cube. This is very useful and can for
instance be used for making MDX calculations "multi-select friendly"
(for more details on this, see Mosha’s blog post here: The following MDX query (running on the Adventure Works cube database) illustrates the use of EXISTING.

WITH MEMBER [Measures].[MyDate] AS
Tail(EXISTING [Date].[Calendar].[Date], 1).item(0).membervalue
SELECT {[Measures].[MyDate]} ON 0
FROM [Adventure Works]
WHERE [Date].[Calendar Year].&[2003]

This query will return the last date in calendar year 2003, which of
course is december 31 2003. It does this because the WHERE clause
slices on calendar year 2003 and the last date to exist with this is
december 31 2003.

What happens if we rewrite the query a bit to use a subselect
to filter on calendar year 2003 instead of specifying this in the WHERE

WITH MEMBER [Measures].[MyDate] AS
Tail(EXISTING [Date].[Calendar].[Date], 1).item(0).membervalue
SELECT {[Measures].[MyDate]} ON 0
(SELECT [Date].[Calendar Year].&[2003] ON 0
FROM [Adventure Works])

Now, I might have been the only one, but I certainly expected this
query to return the same date as the first one: December 31 2003. This is not the case.
Our new, rewritten, query will return August 31 2004 (which is the last
available date in the date dimension in the Adventure Works cube
database). What this really means is that our subselect does not
set the current coordinate in the cube (in Moshas words it "merely does
top level Exists with axis and applies visual totals"). This is really
important to remember if you ever want to use the EXISTING operator for
some fancy MDX calculations, because if your client tools use
subselects (which, for example, Excel 12 does extensively I am told),
you might run into some unexpected results.

Now, it is not all bad – if our query for instance sets the
current coordinate on the date dimension by using one of the
hierarchies from this dimension on an axis, we will get our expected

WITH MEMBER [Measures].[MyDate] AS
Tail(EXISTING [Date].[Calendar].[Date], 1).item(0).membervalue
SELECT {[Measures].[MyDate]} ON 0,
[Date].[Calendar Year].[Calendar Year] ON 1
(SELECT [Date].[Calendar Year].&[2003] ON 0
FROM [Adventure Works])

UPDATE 20060610: The problems with subselects mentioned above are
not limited to the use of the EXISTING operator. They apply in all
situations where the current context is needed in an MDX expression in
order to return a correct result.

Categories: SQL Analysis Services

Processing SSAS

February 19, 2009 Leave a comment

The below are the notes I took when I read the books and Internet posts before. I just organize them into one paragraph and remind me of different methods.

There are several options for processing SSAS objects

  • BIDS
    • BIDS can track only metadata changes. E.g. it cannot detect changes made to data source
  • SSMS
    • Connect to the SSAS server, specify one or more objects and right-click to process
    • You can generate the XMLA script for processing
  • SSIS – SSIS Analysis Services Processing Task
  • Proactive Caching
    • Silence interval – Defines the minimum period to wait for a quiet time in the relational database.
      • Predefined Setting: -1 (infinite) Ignore database notifications
    • Silence override interval – Defines the threshold period after which the server will start rebuilding the cache, if no quiet period occurs, that is, if there are perpetual data changes.
      • Predefined Setting: -1 (infinite) No override period
    • Latency – Defines the lifetime of the old MOLAP cache during which it is still used to respond to queries; data retrieved by those queries is stale.
      • Predefined Setting: -1 (infinite) Never revert to Relational Online Analytical Processing (ROLAP)
    • Rebuild Interval – Schedules the new cache to be build automatically, whether there is data activity or not.
      • Predefined Setting: -1 (infinite) No periodic update
    • Bring online immediately – When set to Immediate (On), the server satisfies queries from ROLAP while the cache is being rebuilt; when set to OnCacheComplete(Off), the server will never revert to ROLAP but will be in infinite latency.
    • Enable ROLAP aggregations – Attempts to create materialized SQL views for aggregations while queries are answered in ROLAP mode.
    • Apply settings to dimensions – Applies storage mode and proactive caching settings to associated dimensions.
    • If query performance is the highest priority, you shouldn’t allow proactive caching to revert to ROLAP al tall. In the Storage Options dialog box, disable the Latency setting (the Not Enabled item in the drop-down list) and clear the Bring Online Immediately checkbox. The result of this combination will be that the server will satisfy the queries from the old MOLAP cache until the new cache is ready.
    • Notification Options – three options
      • SQL Server Notifications – it uses the SQL Server trace events, which are raised when data is changed.

        Ø  Requires SSAS to connect to the data source with administrator rights. (When setting up the data source connection, you have to specify credentials of an account that has administrator rights to the data source.)

        Ø  SQL server notification doesn’t support for even queuing. For example, if Analysis Services is restarted while the data is changed in the relational database, proactive caching will not pick up the data change events.

        Ø  SQL Server notifications always result in Full Process for partitions and Process Update for dimensions. One scenario in which this might be a problem is when only new data will be added to a partition or dimension. In this case, Process Add would be a more efficient option. (alternatively, you can consider scheduled polling notifications.)

            • Client-Initiated Notifications
              • Use an external application to notify the server when the data is changed.
                • Send a NotifyTableChange XMLA command page 384
            • Scheduled Polling Notifications
              • Most commonly used
              • Assumes that the relational table has a column that indicates an update event
                • e.g. a column called ModifiedDate column.

                  • select max(modifieddate) as ModifiedDate from SrcTable

                • the query must be singleton query (1 row, 1 column) . Each query tracks data changes from a single table. If you have a measure group that spans multiple tables, based on a named query or SQL view, you need to add as many polling queries to the grid as there are tracked tables. Often, data is only added, never changed or deleted. In this case, you can improve the proactive caching performance by incrementally updating the cache. To do so, specify an additional processing query next to the polling query.

          • Categories: SQL Analysis Services

            MDX Functions for Navigating Time

            February 19, 2009 Leave a comment

            Functions for Navigating Hierarchies:

            • PrevMember
            • Children
            • Parent

            Functions for Navigating Time

            • Lag
              • Lag(1) = PrevMember
              • Lag(-1) = NextMember
              • Lag(n) = Lead(-n)
              • SELECT [Date].[Fiscal].[Month].[February 2002].Lag(2) ON 0 FROM [Adventure Works]
                • December 2001
              • SELECT [Date].[Fiscal].[Month].[February 2002].Lag(-1) ON 0 FROM [Adventure Works]
                • March 2002
          • ParallelPeriod( [ Level_Expression [ ,Index [ , Member_Expression ] ] ] )
            • Level_Expression – A valid Multidimensional Expressions (MDX) expression that returns a level.
              • Default value = parent of the specified member
            • Index – A valid numeric expression that specifies the number of parallel periods to lag.
              • Defuault value  = 1
            • Member_Expression – A valid Multidimensional Expressions (MDX) expression that returns a member.
              • Default value = Level_Expression.Hierarchy.CurrentMember
            • For Example:

            SELECT ParallelPeriod ([Date].[Calendar].[Calendar Quarter]

               , 3

               , [Date].[Calendar].[Month].[October 2003])   ON 0

            FROM [Adventure Works]

            Result = January, 2003


            SELECT ParallelPeriod ([Date].[Calendar].[Calendar Semester]

               , 3

               , [Date].[Calendar].[Month].[October 2003])   ON 0

               FROM [Adventure Works]

            Result = April, 2002

            Categories: SQL Analysis Services

            SSIS Checkpoint

            February 19, 2009 Leave a comment

            You can set
            package transactions at the entire package level or at any control flow
            container level or task level. Windows Distributed Transaction Coordinator
            (DTC) services needs to be started on the machine for transactions to work.

            SSIS Package
            Restartability: To use Package Restartability, in addition to set checkpoint
            settings, you also need to set FailPackageOnFailure
            to True for each task or container within the package. However, if you want to rerun
            any successful tasks that occur before the failure task, you need to use a
            sequence container around the group of related tasks that require transactions.

            Sometimes, a
            series of tasks will need to be rolled back if one of the tasks fails, but you
            only want to restart from where it fails. To do this, enable checkpoints in the package level, then
            use a Sequence Container that holds
            all the associated tasks. The Sequence Container needs to have the transactions
            turned on by setting the TransactionOption
            to Required at the container level.

            To Check DTC: Control Panel à Administrative Tools à
            Services Console
            Distributed Transaction Coordinator Service.

            To specify a
            task should fail intentionally, set ForceExecutionResult
            to Failure. This is very useful in
            development stage. 

            SSIS event
            handlers assigned to an executable scope will propagate down to child events.
            E.g. if you assign an OnError even to the package and an error even occurs at
            the task, the OnError event handler would file for both the task and the
            package and for any containers in between.

            error information with the OnError event such as ErrorCode, ErrorDescription and SourceName (the task) etc. In
            addition, they can used to audit the execution and the event handler Send Mail Task can be used for

            Event handlers
            can be turned off for any task or
            container by setting the DisableEventHandlers
            property of the Task or Container to True. In other words, if you have an even
            handler defined, but you specifically do not want it to be invoked for a
            specific task, then you can turn off event handlers for that task only.

            the commit level is configured on a table-by-table basis, all the data flow and
            control flow tasks that operate on a single task need to be grouped together in
            a container, and the TransactionOption must be set to
            Required” for each container. You
            should also implement checkpoints on the containers, which will let you restart
            the packages at the point of failure after you have resolved any problems. You
            can simplify this implementation by creating a master package that has
            checkpoints turned on and that uses the Execute Package Task to call child
            packages for each destination table that has transactions enabled.

            CompareStrings_DataAccuracy – loop through characters to compare two strings

            February 19, 2009 Leave a comment
            USE [YourDatabaseName]
            /****** Object:  UserDefinedFunction [dbo].[CompareStrings_DataAccuracyTest]    Script Date: 10/25/2007 16:16:28 ******/
            SET ANSI_NULLS ON

            CREATE FUNCTION [dbo].[CompareStrings_DataAccuracyTest] (@FirstString varchar(max), @SecondString varchar(max)) 
            RETURNS @strCompare TABLE
                — columns returned by the function
                FirstStringlength int NOT NULL,
                num_of_errors int NOT NULL,
                accuracy decimal(3,2) NOT NULL,
                errorType nvarchar(255) null

                –First of all, remove all spaces within the string   
                set @FirstString = replace(@FirstString,’  ‘,”)
                set @SecondString = replace(@SecondString,’  ‘,”)
                set @FirstString = replace(@FirstString,’ ‘,”)
                set @SecondString = replace(@SecondString,’ ‘,”)

                DECLARE @Difference AS INT,
                        @MaxLength AS INT,
                        @Position AS INT
                declare @accuracy as decimal(3,2)
                Declare @errorType as nvarchar(255)   

                If isnumeric(@FirstString)=1 and isnumeric(@SecondString)=1
                –Start of Check if the answer is nermeric
                –if it is nemeric, check whether 2 parameters are equal. if equal, write to tem db and return
                –if not equal, continue next block of code to compare them with String Mode.
                    declare @tempFirstString as money
                    declare @tempSecondString as money
                    set @tempFirstString=convert(money,@FirstString)
                    set @tempSecondString=convert(money,@SecondString)

                    if @tempFirstString = @tempSecondString
                        set @Difference=0
                        set    @accuracy=1
                        set    @errorType=’Unknown’           
                        — copy the required columns to the result of the function
                        INSERT @strCompare
                        SELECT 0, @Difference, @accuracy,@errorType
                –End of Check if the answer is nermeric

                Begin –Start of not nermeric

                Returns a count of the number of characters different in the 2 strings passed.

                If each string contains the same number of each letters and there are 2 differences,
                this will be counted as 1 difference as it is likely a typo: EG ‘String’ and ‘Strnig’

                –Create Temporary table to hold the count of letters in words
                DECLARE @LettersInString TABLE
                    Letter CHAR(1) NOT NULL,
                    FirstStringCount INT NOT NULL DEFAULT 0,
                    SecondStringCount INT NOT NULL DEFAULT 0

                –Difference is the counter of characters that are different in the 2 strings
                SET @Difference = 0
                –Position is the position of the character in the string that is being compared
                SET @Position = 1

                –determine the length of the longest string
                IF len(@FirstString) < len(@SecondString)
                    SET @MaxLength = len(@SecondString)
                    SET @MaxLength = len(@FirstString)

                –loop through each string until the maximum length is reached
                WHILE @Position <= @MaxLength
                    –check to see if the character in the first string is in the temp table. If not add
                    IF NOT EXISTS (SELECT Letter FROM @LettersInString WHERE Letter = SUBSTRING(@FirstString, @Position, 1))
                        INSERT INTO @LettersInString SELECT SUBSTRING(@FirstString, @Position, 1), 0, 0
                    –add 1 to the count of the character in the temp table
                    UPDATE @LettersInString
                    SET FirstStringCount = FirstStringCount + 1
                    WHERE Letter = SUBSTRING(@FirstString, @Position, 1)

                    –check to see if the character in the second string is in the temp table. If not add
                    IF NOT EXISTS (SELECT Letter FROM @LettersInString WHERE Letter = SUBSTRING(@SecondString, @Position, 1))
                        INSERT INTO @LettersInString SELECT SUBSTRING(@SecondString, @Position, 1), 0, 0

                    –add 1 to the count of the character in the temp table
                    UPDATE @LettersInString
                    SET SecondStringCount = SecondStringCount + 1
                    WHERE Letter = SUBSTRING(@SecondString, @Position, 1)

                    –check to see if the same position in each string holds the same character   
                    IF SUBSTRING(@FirstString, @Position, 1) <> SUBSTRING(@SecondString, @Position, 1)
                        SET @Difference = @Difference + 1

                    –move to the next position
                    SET @Position = @Position + 1

                    –If each string contains the same number of each letters and there are 2 differences, this will be counted as 1 difference as it is likely a typo: EG ‘String’ and ‘Strnig’
                    –IF (SELECT COUNT(Letter) FROM @LettersInString WHERE FirstStringCount <> SecondStringCount) = 0 AND @Difference = 2
                    —    SET @Difference = 1
                    set @accuracy = 1 – cast(@Difference as decimal) / cast(len(@FirstString) as decimal)

                    set @errorType=’Unknown’
                    set @errorType=
                            when len(@FirstString) < len(@SecondString) Then ‘Type Extra Characters’
                            when len(@FirstString) > len(@SecondString) Then ‘Missing Characters’

                    — copy the required columns to the result of the function
                    INSERT @strCompare
                    SELECT len(@FirstString), @Difference, @accuracy,@errorType
                End  –end of not nermeric


            Categories: SQL Script

            Understand Nested Transaction

            February 19, 2009 Leave a comment

            Nested Begin Transaction and Commit/Rollback Transaction is
            very confused. From my memory, rollback transaction within the nested begin transaction
            will roll back the nested transaction as well as all parent begin transaction.
            However the commit transaction only commit its corresponding transaction and if
            there is a rollback outside, the transaction will still rollback unless you use
            Save Tran cmd. You can play with the below script and exam the result.

            USE [YourDatabase]

            SET ANSI_NULLS ON

            IF  EXISTS (SELECT * FROM sys.objects WHERE object_id =
            OBJECT_ID(N'[dbo].[test]’) AND type in (N’U’))
            DROP TABLE [dbo].[test]
            CREATE TABLE [dbo].[test](
                [value] [int] NOT NULL
            ) ON [PRIMARY]

            insert into test values(1)
            insert into test values(2)
            insert into test values(3)
            insert into test values(4)

            DECLARE @TranCounter INT;
            SET @TranCounter = @@TRANCOUNT;
            begin tran
                update test
                set value=111111
                where value=1   
                select @@TRANCOUNT

                IF @@TRANCOUNT >
                   SAVE TRANSACTION t1
                    BEGIN TRANSACTION

                update test
                set value=222222
                where value=1
            rollback tran t1

            Categories: SQL Script

            Calculating Age

            February 19, 2009 Leave a comment
                    WHEN Month(GetDate()) < Month(c.[BirthDate])
                        THEN DateDiff(yy,c.[BirthDate],GetDate()) – 1
                    WHEN Month(GetDate()) = Month(c.[BirthDate])
                    AND Day(GetDate()) < Day(c.[BirthDate])
                        THEN DateDiff(yy,c.[BirthDate],GetDate()) – 1
                    ELSE DateDiff(yy,c.[BirthDate],GetDate())
                END AS [Age]
            Categories: SQL Script

            Detect Overlapping DateTime

            February 19, 2009 Leave a comment
            declare @starttime as datetime
            declare @endtime as datetime
            declare @inputstarttime as datetime
            declare @inputendtime as datetime

            set @starttime=’2007-04-01 00:00:00′
            set @endtime=’2007-04-10 00:00:00′
            set @inputstarttime=’2007-03-10 00:00:00′
            set @inputendtime=’2007-04-01 00:00:00′

                    when @inputstarttime>@starttime and @inputstarttime<@endtime
                        Then ‘Yes’
                    when @inputendtime>@starttime and @inputendtime<@endtime
                        Then ‘Yes’
                    when @inputstarttime <= @starttime AND @inputendtime >= @endtime
                        Then ‘Yes’
                end as [Overlapping?]

            Categories: SQL Script

            Clear SSAS Database Cache

            February 19, 2009 Leave a comment
            <Batch xmlns=""&gt;

            Categories: SQL Analysis Services

            Registry file to point to SharePoint Data Connection Library

            February 19, 2009 Leave a comment

            We use SharePoint 2007 to host the centralized Data
            Connection Library (DCL) to help the end users easily connect to SSAS cubes. From
            the end users’ point of view, they don’t have to type server name, select cube
            etc every time they are trying to query against SSAS. Instead, they will click
            the “Existing Connection” to browse the specific folder and select the specific
            Pre-defined DCL.

            To do so, we need to create a registry file (.reg) and
            publish onto the SharePoint. End users need to click the link and press “OK” to
            import the .reg file. The below code is what you should have in the registry
            file. The "http://MOSS2007/Reports/BI/Data
            is the actual URL to the DCL we setup in SharePoint. You
            can write multiple entries here if you do have more than one DCL.

            Once you copy the code into a notepad and modify the URL
            path, you can save it as a .reg file. Now it is ready to distribute.

            /* Start of the code –don’t put this line in the .reg file. This is comment
            Windows Registry Editor Version 5.00


            "Data Connection Library"="http://MOSS2007/Reports/BI/Data

            Categories: SharePoint 2007