Deployment Options for BISM Project

 

There are two Query Mode options you could use when you deploy the BISM project to the AS Tabular Model server.

• In-Memory
• DirectQuery

By default, data in tabular model is processed, compressed using the xVelocity in-memory analytics engine (VertiPaq).  This in-memory columnar storage engine has been optimized for high performance analysis and exploration of data.  It provides fast query times for aggregation queries.

However, there are some drawbacks:

• Data is not updated when the source data changes. Model needs to be processed to refresh the data.
• When you turn off the computer hosting the model, the cache is saved to disk and must be reopened when you load the model.
• The save and load operations can be time-consuming.
• Need lots memory to hold the large fact data

On the other hand, DirectQuery mode uses data that is stored in a SQL Server database. It lets users retrieve data directly from a SQL Server data source in  real time.  This mode also lets you create models and build reports for large data sets that cannot reside in memory.

Here are additional benefits:

• The data is guaranteed to be up-to-date.
• Use the advantage of provider-side query acceleration, i.e. SQL 2012 column indexes.
• Could use row-level security provided by the backend  database.
• Analysis Services can perform optimization to ensure the query plan against the backend database will be as efficient as possible.

There are some design considerations if you are planning to use DirectQuery mode:

• During design phase, you may need to use Preview or Filter function to load subset data into your project.
• MDX queries are not supported for a model in DirectQuery mode. You cannot use PPS or other clients that only issue MDX queries to consume it.
• Currently this model only supports one data connection. You cannot query two or more SQL Servers from a DirectQuery enabled model.
• Calculated columns and some DAX functions are not supported. You may need to use SQL View or other technique for that purpose.

DirectQuery supports a hybrid deployment mode that can use either the cache or the relational source. For more information, see DirectQuery Mode (SSAS Tabular).

 

Reference

Formula Compatibility in DirectQuery Mode

DirectQuery Mode (SSAS Tabular)

White Paper: Using DirectQuery in the Tabular BI Semantic Model

Sort a MDX Dataset by Multiple Criteria

Recently I work on a SSRS report that needs a parameter to return a set of user to be selected as the filter for the report.  The list of user has to be sorted by Department Code and User’s name.

In MDX, sorting a dataset based on one criterion is directly supported
through the Order( ) function.  However, it doesn’t support multiple
criteria for sorting explicitly.  In order to sort the dataset with multiple criteria, some tricks will be needed to complete the task.

Sample Data

Here is how we could return a user list dataset that are sorted by Department Code and User Name.

Trick One:

Concatenate the two attributes into one and then use Order() function to sort it accordingly. 

WITH MEMBER [Measures].[ParameterCaption] 

AS [User].[User].CURRENTMEMBER.MEMBER_CAPTION 

MEMBER [Measures].[ParameterValue] 

AS [User].[User].CURRENTMEMBER.UNIQUENAME 

MEMBER [Measures].[Department] 

AS [User].[User].CURRENTMEMBER.Properties( "Department Code")

SELECT {  [Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[Department] } ON COLUMNS ,  

{

    ORDER(

                [User].[User].Children ,

                [User].[User].CURRENTMEMBER.Properties( "Department Code") + [User].[User].CURRENTMEMBER.MEMBER_CAPTION, 

                BASC

                )

} ON ROWS

FROM [Test]

As you could see, the result set returned has been sorted by Department first and then Username in alphabetical ascending order.

You may use this trick when the sorting order of both members are in the same direction either ascending or descending.

Trick TWO

When the sorting criteria are in different alphabetical order i.e. one in ascending order and the other in descending order, we will need to use two ORDER() functions.

WITH MEMBER [Measures].[ParameterCaption] 

AS [User].[User].CURRENTMEMBER.MEMBER_CAPTION 

MEMBER [Measures].[ParameterValue] 

AS [User].[User].CURRENTMEMBER.UNIQUENAME 

MEMBER [Measures].[Department] 

AS [User].[User].CURRENTMEMBER.Properties( "Department Code")

SELECT {  [Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[Department] } ON COLUMNS ,  

{

 ORDER( 

        ORDER(

                [User].[User].Children ,

                [User].[User].CURRENTMEMBER.MEMBER_CAPTION, 

                BASC

                ),

    [User].[User].CURRENTMEMBER.Properties( "Department Code"),

    BDESC

    )

} ON ROWS

FROM [Test]

As you could see, the result set returned now will be sorted by the Department Code in descending order first, then the Username in ascending order.

Reference

http://msdn.microsoft.com/en-us/library/ms145587.aspx

Display the No Row Information in the SSRS Report Footer

Recently there is a reader asked about how to display row count information in page footer if no data returned.  Since RowNumber() function can only be used in the report body to retrieve the number of rows in the specified scope, we could not use it in either the Header or Footer area.

We could use other counting function such as CountRows() or Count() to retrieve the number of rows retuned for the dataset.

Here is what we can do:

• Add a textbox in the report footer

• Use CountRows() function passing in the dataset name in the expression for the textbox as:

Result

AS you could see, the report footer shows the number of row returned by the dataset now.

Reference:

http://msdn.microsoft.com/en-us/library/dd255237(v=sql.110).aspx

http://msdn.microsoft.com/en-us/library/ms157163(v=sql.110).aspx

http://msdn.microsoft.com/en-us/library/ms156330(v=sql.110).aspx

Use DAX Earlier Function in Calculated Column to Mimic Group By Clause

It is very easy to group or slice the measures in the Pivot table by adding a attribute to the row or slicer.  But, how do we create the same effect in the Calculated Column?

Here is the Sample Data

If we treat this table as a SQL table, we could use the following T-SQL query to find the total queued time for each queue per date.

SELECT [Date], [QueueName], SUM([QueuedTime]) AS [TotalQueuedTime]

FROM CallDetail

GROUP BY [Date], [QueueName]

In PowerPivot, we could use the EARLIER function to achieve the same effect.  EARLIER is a very useful DAX function when you want to use a certain value as input to produce a calculation.

Syntax:

EARLIER(<column>, <number>) 

Solution:

Step 1: Calculate the total of the QueuedTime by using SUM(), and then use FILTER() to obtain the subset of the table for the Date and the QueueName needed.

=CALCULATE(

    SUM(CallDetail[QueuedTime]), 

    FILTER(CallDetail, CallDetail[Date] = ?) 

           && CallDetail[QueueName]= ?

            )

    )

Step 2: In the question mark area, we need to plug in a value/expression to restrict the rows been returned.

If we set they to the static value as “2012-02-18” and “Queue A”, the Sum of QueuedTime will be 900 for all the rows which won’t be correct for the Queue not equal to “Queue A” or the Date not equal to “2012-02-18”.

Step 3: To pass in correct expression, we could use the EARLIER() as the input for the certain value (i.e. Date or Queue name) that the calculation will be based on it. 

=CALCULATE(

    SUM(CallDetail[QueuedTime]), 

    FILTER(CallDetail, CallDetail[Date] = EARLIER(CallDetail[Date])

           && CallDetail[QueueName]= EARLIER(CallDetail[QueueName])

            )

    )

You can see the result now is correct based on the Date and QueueName

The EARLIER function is based on the current row context. If there is no row context, it will return an error.

Implementation Example:

Here is a scenario that I have encountered: Create a measure that contains the cumulated Queued Time only for the last queue.

To solve it, I add the following condition to the pervious formula based on the LastQueue flag

=IF(CallDetail[LastQueue] = 1, 

    CALCULATE(

        SUM(CallDetail[QueuedTime]), 

        FILTER(CallDetail, CallDetail[Date] = EARLIER(CallDetail[Date]) 

           && CallDetail[CallID]=EARLIER(CallDetail[CallID])

            )

        )

     ,BLANK()

    )

This way, only the last queue will have the total queued time in the column.

When summing up in the Pivot table, the total of QueueTimeHandled per day will now match the total of QueuedTime.

My colleague Javier Guillen has another nice blog entry to discuss how to use the EARLIER() in DAX measure. You may want to check it out.

Reference:

http://technet.microsoft.com/en-us/library/ee634551.aspx

Using Page and CAML Query to Extract SharePoint List in SSIS

 

There are several ways to read SharePoint List as data source in SSIS.  The easiest way is using the SharePoint List Adapter.  You could download it from the Codeplex site.  However, you may want to have more control during extracting the list to avoid failure, to manipulate data before sent to the output rows, or you are experiencing timeout issue with lots of item in the list. 

Here is the solution I used:

Add For Loop Container in Task Component with a Data Flow

• Since the SharePoint list could be large, use the for loop container to loop through the paging.

• Set InitExpression to the Page number @PageCount = 1
• Set the EvalExpression to a Variable: @LoopNext  which will be determine in the data flow.
• Set the AssignExpression to be @PageCount = @PageCount + 1

 

Create a Script Component  with Source Type to Read SharePoint List

• In the data flow, add a Script Component with Source type.

• Pass in @LoopNext  and @SharePointPageInfo variables as Read Write Variables.
• Pass in @SharePointSite, @SharepointListname, @SharePoinbtView, @SharePointRowLimit variables as Read Variables.
• Create two Outputs. One for the default SharePoint list data set, the other will hold the error data.
• Click Edit Script… button to add .net code
• Right click the project to add Server Reference that point to the SharePoint Site

• In CreateNewOutputRows(), instantiate the SharePoint Web Service List object
• Create several XMLNode objects as the parameters to call System.Xml.XmlNode GetListItems()

//Retrieve the list of available fields from the GetList call to Sharepoint.

Lists SPS = new Lists();

SPS.UseDefaultCredentials = true;

SPS.Url = strSharepointURL;

//Create XML Node

XmlDocument xmlDoc = new System.Xml.XmlDocument();

XmlNode ndViewFields = xmlDoc.CreateNode(XmlNodeType.Element, "ViewFields", "");

XmlNode ndQueryOptions = xmlDoc.CreateNode(XmlNodeType.Element, "QueryOptions", "");

XmlNode ndQuery = xmlDoc.CreateNode(XmlNodeType.Element, "Query","");

XmlNode ndListItems = null;

ndListItems = SPS.GetListItems(strListName, strViewName, ndQuery, ndViewFields, strRowLimit, ndQueryOptions, null);

• Once the result gets back, iterate through the XML nodes to populate the default output data row.
  
• If there is an exception thrown
  
  
  
  • Set the @LoopNext to false which will stop the For Loop container
    
  • Add a row that contains the exception message to error output.

catch (System.Web.Services.Protocols.SoapException ex)

     {

         string strError = "Message:" + ex.Message + "\nDetail:" + ex.Detail.InnerText + "\nStackTrace:" + ex.StackTrace;

         ErrorBuffer.AddRow();

         ErrorBuffer.ErrorData = strError.ToString().Substring(0, strError.ToString().Length > 4000 ? 4000 : strError.ToString().Length);

         bLoopNext = false;

     }

• In the data flow, sent the SharePoint list items to the destination table and the error data to error table. This way the SSIS package will not fail to execute with any SharePoint Site issue. Other process could utilize the data in the error table to recover it softly.
  
• In the Add Metadata component, add the @PageCount as an output column. It could be used for recovery or debug purpose.
  

 

Narrow Down the Needed Fields

Don’t select all the fields from the list. Sometime the list may contain binary data, meta data or image that may take long time to load. To narrow down the fields, just specify the fields in the ViewFields XML Node as:

XmlNode ndViewFields = xmlDoc.CreateNode(XmlNodeType.Element, "ViewFields", "");

//Get List of Field needed

ndViewFields.InnerXml = "<FieldRef Name='ID' />";

ndViewFields.InnerXml += "<FieldRef Name='Created' />";

ndViewFields.InnerXml += "<FieldRef Name='Author' />";

ndViewFields.InnerXml += "<FieldRef Name='Editor' />";

ndViewFields.InnerXml += "<FieldRef Name='Modified' />";

Add CAML Query to Extract Only the Delta

To pull only the delta records in, just add filters in the query. The SharePoint list has a system field called “Modified” which will be time stamped when the record get changed. You could utilize the CAML query to get the items that have Modified Date greater than a Date value or within an arrange of date. If you don’t know the syntax of the CAML Query, you could use some SharePoint Utility such as U2U CAML Builder.

Here is a sample CAML Query that will filter the result by range of date:

<Where><And>

<Geq><FieldRef Name="Modified" IncludeTimeValue="TRUE" /><Value Type="DateTime">2012-01-01 00:00:00</Value></Geq>

<Leq><FieldRef Name="Modified" IncludeTimeValue="TRUE" /><Value Type="DateTime">2012-01-31 23:59:00</Value></Leq>

</And>

</Where>

Page though Large Number of List Items in the Script

Depend on the size of the item, you may set the row limit to 1000 while calling the GetListItems().  The function supports service-side paging. When the XML result returned, it includes a ListItemCollectionPositionNext attribute that contains the information to support the paging.  You need to save it for the next call.  Make sure you do not modify the string. 

In the example below, I set the ListItemCollectionPositionNext attribute to a local variable first and the bLoopNext to true. I then save the string to a SSIS Read/Write variable to be use for next loop.

//Get page related meta data

XDocument meta = XDocument.Parse(ndListItems.InnerXml);

if (meta.Root.Attribute("ListItemCollectionPositionNext") != null)

    {

       strPage = meta.Root.Attribute("ListItemCollectionPositionNext").Value;

       bLoopNext = true;

    }

else

    {

       strPage = string.Empty;

       bLoopNext = false;

    }

When next loop starts, I set the saved attribute string to the QueryOptions parameter to be used to fetch the next set data back.

//Set page Query option

if (strPage.Length > 0)

    {

       ndQueryOptions.InnerXml = @"<Paging ListItemCollectionPositionNext='' />";

       ndQueryOptions.ChildNodes[0].Attributes["ListItemCollectionPositionNext"].InnerText = strPage;

    }

else

    {

        ndQueryOptions.InnerXml = "";

    }

 Result

After executing the SSIS package, you can see the SharePoint List been extracted page by page as below:

 

The PageCount column stores the SharePoint List page number.

Reference:

http://msmvps.com/blogs/ivansanders/archive/2011/07/24/ssis-sharepoint-list-adapters.aspx

http://msdn.microsoft.com/en-us/library/lists.lists.getlistitems(v=office.12).aspx

http://sqlsrvintegrationsrv.codeplex.com/releases/view/17652

Tip to Change SSIS Variable Scope

Recently I work on a SSIS project that has a Sequence Container with lot of variables that I need for a data flow. Later on I want to change the sequence container to be a For Loop Container.   It is easy to copy and paste the the data flow in the container to the other. But, how to move the SSIS variables in the visual studio?

If you are familiar with the SSIS XML code, you may be able to copy or move the variables from one XML Node section to the other without corrupt it.  For others, recreating the variable with correct value or expression could be cumbersome.

Here is a easy way to handle it:

• Install BIDS Helper from CodePlex site.
• Select the variable you want to copy or move

• Click on the Move/Copy Variables from SSIS Variables tool bar to open a new window

• Click on the DTS executable that you want the variable scope to change to , then click OK

• The variable shows up in the SSIS Variables Window with new Scope

SQL Dump After Either Upgrade or Downgrade the PowerPivot

Recently I upgraded the PowerPivot Excel add on to the version 2 to work with new DAX. I experienced excel crush when I opened the workbook.  The error message showed as:

SQLDUMPER.EXE
Unable to open file \\?\Program Files\Microsoft Analysis Services\AS OLEDB\10\FlightRecorderCurrent.trac error 2

I tried to uninstall and re-install the program, but it didn’t work.  I have to manually delete the directory C:\Program Files\Microsoft Analysis Services\AS OLEDB\ and repair the PowerPivot, then it works.

Same situation happened again when I downgrade my PowerPivot back to version 1 to be compatible with my client’s environment.  Once I manually remove the C:\Program Files\Microsoft Analysis Services\AS OLEDB\ directory and then repair the program, it works again.