First, let’s take a look at how we create an instance if IApp, as we will need this for both iOS and Android. I am assuming the user has a basic understanding of how Xamarin UITest works.

When setting up your Test, you will using the FluentAPI built off of ConfigureApp to create an App for either Android or iOS. This creates a platform specific instance that implements IApp. 

public static IApp StartApp(Platform platform)
{
  if (platform == Platform.Android)
  {
    return ConfigureApp.Android.DoSomeCustomAndroidConfig.StartApp();
  } 
 
  return ConfigureApp.iOS..DoSomeCustomiOSConfig().StartApp();
}

In my base test class, I call StartApp and then check to see if I’m running on a phone or tablet.

[SetUp]
public void SetUp()
{
  App = StartApp(Platform);

 
  //Figure out what kind of Idiom we have.
  //Use the built in support for iOS and a backdoor method for Android
  var iOSApp = App as iOSApp;
  if (iOSApp != null)
  {
    IsPhone = iOSApp.Device.IsPhone;
  }
  else
  {
    var idiom = App.Invoke("BackdoorGetIdiom");
    IsPhone = idiom.Equals("Phone");
}

We first check to see if our App is of type iOSApp, and if it is, we’ll use the built in support in Xamarin UITest. If we are running on Android, then we’ll use the backdoor method approach, which I’ll outline next.

All I had to do was create my backdoor method and expose it from my MainActivity which is in my Xamarin Forms Android project.

[Export("BackdoorGetIdiom")]
public string BackdoorGetDeviceIdiom()
{
  return Device.Idiom.ToString();
}

You may need to add a reference to the Mono.Android.Export.dll. I found this in “C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\MonoAndroid\v6.0\Mono.Android.Export.dll”. Now, all you need to do is invoke the method as shown in my [SetUp] method.

There are various rules that you must follow when writing your backdoor methods which Xamarin outlines on their backdoor documentation page. The rules differ between iOS and Android so be sure to read them carefully.

Here’s some MDX that implements a rolling 6-week calculation that only includes non-empty dates.

Ancestor(Tail(NonEmpty(Descendants(StrToMember(@Calendar),[Date].[Calendar].[Date]),[Measures].[Registration Count])).Item(0),[Date].[Calendar].[Calendar Week of Year]).Lag(6):Ancestor(Tail(NonEmpty(Descendants(StrToMember(@Calendar),[Date].[Calendar].[Date]),[Measures].[Registration Count])).Item(0),[Date].[Calendar].[Calendar Week of Year])

This implementation uses the DESCENDANTS function to identify all child dates based on the current position in the Calendar hierarchy (represented by the parameter @Calendar).  We use the TAIL function to return the last date where the [Registration Count] measure is non-empty; then the ANCESTOR function returns the [Calendar Week of Year] associated to the date returned by the TAIL function. 

Once we identify the week based on the current position in the calendar hierarchy we can then use LAG(6) to navigate 6 weeks back.  The Range operator ‘:’ returns all the weeks from 6 weeks prior to the current week based on the calendar hierarchy position.

Here’s a complete MDX query using this logic.  Note: I’ve replaced StrToMember(@Calendar) with the member [Date].[Calendar].[Calendar Year].&[2015].&[Q2 2015].&[Apr 2015].

SELECT {[Measures].[Registration Count]} ON COLUMNS,  NON EMPTY [Date].[Calendar Week of Year].[Calendar Week of Year].MEMBERS ON ROWS
FROM [Cube]
WHERE Ancestor(Tail(NonEmpty(DESCENDANTS([Date].[Calendar].[Calendar Year].&[2015].&[Q2 2015].&[Apr 2015],[Date].[Calendar].[Date]),[Measures].[Registration Count])).ITEM(0),[Date].[Calendar].[Calendar Week of Year]).LAG(6):Ancestor(Tail(NonEmpty(DESCENDANTS([Date].[Calendar].[Calendar Year].&[2015].&[Q2 2015].&[Apr 2015],[Date].[Calendar].[Date]),[Measures].[Registration Count])).ITEM(0),[Date].[Calendar].[Calendar Week of Year])

The rolling 12 month calculation is straight-forward and can be implemented using built-in DAX formulas.  This implementation is based off of Alberto Ferrari’s blog post. http://www.sqlbi.com/articles/rolling-12-months-average-in-dax/.

Registration Count Rolling 12 Mos:=CALCULATE([Registration Count Current Period], DATESBETWEEN(‘Date'[Date], SAMEPERIODLASTYEAR(NEXTDAY(LASTDATE(‘Date'[Date]))),LASTDATE(‘Date'[Date])))

The LASTDATE function returns the last date contained within the active filter context of the cube.  For example, if a user has filtered to July 2015 then last date will return July 31, 2015 based on a standard calendar implementation.  The function SAMEPERIODLASTYEAR is using the day after the last date in our filtered dataset and identifies the same date in the prior year.  DATESBETWEEN then identifies all the dates between last year’s and this year’s date.

The rolling 6 weeks implementation was less straightforward. I ended up implementing this calculation using a combination of a calculated column and measure in the cube.

First, I added a column called ‘ThisSaturday’ to the Date table in the cube.  For each date in the Date table, it calculates Saturday’s date in the same week.

The calculated column formula is listed below:

=CALCULATE(MIN(‘Date'[Date]), FILTER(ALL(‘Date’),’Date'[Day of Week]=7 && ‘Date'[Date] >= EARLIER(‘Date'[Date])))

We’re basically using the FILTER function to retrieve all the Saturday’s that are on or after the present date and then the MIN function identifies the Saturday that is nearest to the date.

Once we have this calculated column added to the date table, we can then apply it in our rolling 6 week measure.

Registration Count Rolling 6 Wks:=CALCULATE([Registration Count Current Period], DATESBETWEEN(‘Date'[Date], LASTDATE(‘Date'[ThisSaturday])-41,LASTDATE(‘Date'[ThisSaturday])))

In this measure, we use LASTDATE to identify the last date associated to the current filter context.  We then subtract 41 to calculate the date 6-weeks prior and use DATESBETWEEN to retrieve all the dates between this date and the Saturday of the last date in the filter context. 

As a first step, I added two additional columns to my tabular cube date dimensions that represents the prior year date key and date.  An example of the query is included below (note: for simplicity in this blog post I have not included all the date dimension attributes, an actual date dimension would likely have many more attributes than what I have included).  One challenge I encountered is an occasional mismatch between the number of days in a given fiscal month when comparing two fiscal years (some fiscal years have more days in a particular fiscal month than the prior year);  therefore, I am using a LEFT OUTER JOIN and then ISNULL to return an unknown for the LastYearDateKey column.  In addition, this column needs to have a unique value so the unknown is set to be –1 multiplied by the prior year date key.

SELECT DateKey, [Date], ISNULL(c.LastYearDateKey,-1*DateKey) [LastYearDateKey],c.LastYearDate [LastYearDate]
FROM DimDate d
LEFT OUTER JOIN (
    SELECT c.DateKey CurrentYearDateKey
          ,l.DateKey LastYearDateKey
          ,l.Date LastYearDate
    FROM DimDate l
    INNER JOIN (SELECT DateKey,Date,FiscalYear – 1 [FiscalYear],FiscalMonth,FiscalDayOfMonth FROM DimDate) c ON l.FiscalYear = c.FiscalYear
    AND l.FiscalMonth = c.FiscalMonth
    AND l.FiscalDayOfMonth = c.FiscalDayOfMonth) c ON d.DateKey = c.CurrentYearDateKey

Now that I have the necessary columns added to my dimension, I created an inactive relationship from my sales fact table to the new LastYearDateKey column in the date dimension (The primary / active relationship from the date table to the sales fact table is based on the current year date key).  The following shows the inactive relationship between the two tables (note that the active checkbox is unchecked).

With the inactive relationship established, I can create measures using DAX calculations that reference this inactive relationship.

Here’s an example of a measure in my sales table that uses the inactive relationship:

Sales Amount PY:=CALCULATE(SUM([SalesAmount]),USERELATIONSHIP(Dates[LastYearDateKey],Sales[GLDateKey])).

Now, I can compare sales in the prior year to the current year using the custom date matching (based on fiscal month and day of month) defined in my date dimension query.

Originally this process was implemented via Powershell scripts using BCP, file compression (.zip) and FTP.  The data was first BCP’d from tables in the dealer’s database into a flat file format.  Then the dealer’s flat files were compressed into .zip format and FTP’d.  Another set of Powershell scripts ran on the receiving server (in Azure) and pulled the files down via FTP, unzipped them and executed BCP commands to move the data from the flat file into the database.  The scripts processed one table at a time for a single dealer and once all tables completed it would move onto the next dealer in the queue.  Overall, the total dataset was taking roughly 15-16 hours to load and was unsustainable.

To optimize the process further, the Powershell scripts were replaced with a C# application.  The C# application is multi-threaded to load multiple tables in parallel as well as multiple dealers, the number of dealers that can be loaded in parallel is controlled by a parameter.  After performing some benchmark testing, the parameter was set to 10 parallel locations since this appears to provide the best performance.  The C# application uses the SqlBulkCopyService which directly BCPs data from tables in the dealer’s database to the appropriate tables in the unified database residing in Azure.  This eliminated the flat files and the need to compress and FTP them.  The C# application’s performance provided a significant improvement, the data is now loading in roughly 9 hours versus 15-16 hours with the Powershell scripts, thanks in large part to the parallel loading of tables and dealers.

In addition to re-working the code to move the data, several optimizations were also implemented involving the unified database and virtual machine in Azure.  During initial performance testing while BCP’ing the data, the unified database log file was a bottleneck.  The log file was originally on a persistent VHD stored on Azure blog storage.  To improve log performance, the log file is temporarily moved to the D: drive (local non-persistent SSD drive) throughout the duration of the weekly load. Once the load completes, the log file is moved back to a persistent drive.  We also have the database data file on a drive spread across 10 disks to help improve the IO performance and to allow for future growth.  The index file is split across two disks for performance as well.

I’m sure this implementation will continue to evolve over time and I will write more blog posts as things progress.

It is possible to use Log4Net within SSIS script tasks/components, but the implementation is a bit different than when used within console or web applications. 

1.  Create or open an SSIS script task or component.  Click ‘Edit Script’.

2.  In the Solution Explorer window, right click on the solution and select ‘Manage NuGet Packages’. 

If you do not see the option ‘Manage NuGet Packages’, then you will need to install the package manager extension.  This can be done from within Visual Studio.

Go to Tools > Extension Manager.

Search the online gallery for ‘NuGet Package Manager’ and install.

3.  In the ‘Nuget Package Manager’ window, click ‘Online’ in the left navigation pane and then search for Log4Net.  Click ‘Install’ and then Close.

You should now see a Packages.config file added to your project.  The Packages.config file stores the version information for the nuget package(s) installed.  The following is an example:

When a package is restored, Nuget references the Packages.config file to pull down the correct version of the package (in this case version 2.0.3).

4.  In addition to adding the package via Nuget within the SSIS script task, the Log4Net.dll file will also need to be added to the GAC.  There are several ways to add a dll to the GAC, Powershell is one option:

Set-location “c:\temp”

[System.Reflection.Assembly]::Load(“System.EnterpriseServices, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a”)

$publish = New-Object System.EnterpriseServices.Internal.Publish

$publish.GacInstall(“c:\temp\log4net.dll”)

Reference: http://social.technet.microsoft.com/wiki/contents/articles/22618.how-to-install-a-dll-to-the-gac-on-windows-server-2012-using-only-powershell.aspx

5.  Now we have reached the point where we can start writing some code.  Generally, all Log4Net configurations are set in the App.config file or a custom Log4Net config file.  

Since the DTEXEC.EXE application runs the SSIS packages, it is not possible to directly associate config files to the package script task/component.  Therefore, all Log4Net configuration settings must be set programmatically.

I used the following StackOverflow post as a reference: http://stackoverflow.com/questions/2738130/using-log4net-as-a-logging-mechanism-for-ssis

First, add the following log4net references to the Namespaces

Now add a new member of type log4net.ILog to the Script class

Create a new method called InitLogger

Now add some logging to your main method.  In this example all levels of severity are being logged to the file h:\\ssis\TestLog_{Timestamp}.log

the line of code logger.Info(“Test Log Message”) writes a single line of output to the logging file.

You can adjust the severity level by calling the appropriate method.  This is a code sample from www.beefycode.com

logger.Debug( "Hello World!" );

logger.Info( "I'm a simple log4net tutorial." );

logger.Warn( "... better be careful ..." );

logger.Error( "ruh-roh: an error occurred" );

logger.Fatal( "OMG we're dooooooomed!" );

There are a few other nuances to be aware of.  When you close and re-open your SSIS project, you will need to restore the Log4Net Nuget package in the script task/component.  To accomplish this, simply re-open the Nuget Package Manager.  You will see a message stating ‘Some NuGet packages are missing from this solution.  Click to restore from your online package sources’.  Click ‘Restore’ in the upper right corner.  The Nuget Package Manager will restore the same version of the package referenced in the Packages.config file. 

If you want to Update to a newer version of the package, click the ‘Updates’ option in the left navigation pane.  All available updates should be displayed.

It’s unfortunate that the A-Series had to suffer this drop in performance, but it’s not too bad when you look at the pricing.

Azure Retail Pricing for Standard VMs:

D11 (2 cores, 14GB ram, 100GB SSD): $0.366/hr, $272.31/month
A5 (2 cores, 14GB ram): $0.30/hr, $223.20/month

D13(8 cores, 56 GB ram, 400 GB SSD): $1.318/hr, $980.60/month
A7 (8 cores, 56 GB ram): $1.20/hr, $892.80/month

In addition to the SSD on the D-Series, you also get 60% faster CPU over the A-Series.

dism.exe /online /enable-feature /featurename:NetFx3 /norestart /LimitAccess /Source:”\\FilleServer\Software\WindowsUpdates\Server2012R2-Update-SXS” /All

I have run into a couple of issues when running this on Azure VMs created using the latest versions of the Windows 2012 R2 Datacenter gallery image which I wanted to share.

1) The install media change between RTM and Update 1.

When this happened, I had to download the ISO that had update 1 slipstreamed an extract the SXS folder. You’ll notice in my example command above, the path contains \Server2012R2-Update-SXS. I have a corresponding folder that has the RTM bits.

2) Security Updates 2966827 or 2966828, fixed by 3005628.

The two security updates, 2966827 and 2966828, broke the ability to install .Net 3.5. Those two updates have been fixed so that they are only installed if .Net 3.5 has already been installed. Update 3005628 can be used to fix the issues if you have already installed one of the two problem updates, as was the case with the latest Azure gallery image.

I ended up using the MSU package (Download the Windows8.1-KB2966828-x64.msu package now), as I figured that the gallery images met the criteria: “Customers who are aware that the DISM “/StartComponentCleanup” and “/ResetBase” option was used on their system…”

1.  First you’ll need to add two references to the SSIS Script Component Visual Studio project.  To add a reference right click references and select ‘Add Reference’.

Click the .NET tab and then add the following two reference to your project:

• Microsoft.SharePoint.Client
• Microsoft.SharePoint.Client.Runtime

2.  Update the Namespaces region so it is similar to the following:

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Client;
using System.Security;
#endregion

Note: most of the namespaces are already included by default, the bottom three were added for this implementation.

3.  Add the following variables to the class definition:

• private SecureString secureStr;
• private ClientContext cc;
• private List spList;
• private String query = “<View><Query><OrderBy><FieldRef Name=’ID’/></OrderBy></Query></View>”;

The query variable stores the Caml query that will be used to retrieve all fields for all items in the list and sorts them by ID.  More information regarding Caml can be found here.

3.  When connecting to a list in SharePoint Online, you need to use the class SharePointOnlineCredentials to set the user and password credentials.  The Client Context class is used to establish a connection to SharePoint.  The following code is from the method called CreateNewOutputRows().

Some helpful notes:

• Variable.SharepointURL is a URL to the SharePoint site collection where the list resides.  Something similar to https://<SharePoint Instance>/sites/<site name>.
• The name of the SharePoint list being referenced is “Benchmark Category”.
• The SecureString class must be used for SharePoint Online Credentials.  cc.Credentials = new SharePointOnlineCredentials(User.SharePointUser, secureStr).
• When iterating through the items in the list, I am referencing each item based on the key associated to the list field.  In the SharePoint list we have a field with the name ‘Product Category’, but the key associated to that field is ‘Title’; therefore, I am referencing this field using item[“Title”].  We also have a field that is displayed as ‘Benchmark Category’ and the key for this field is ‘Benchmark_x0020_Category’.
• The ‘Benchmark Category’ field is defined as type ‘Choice’ in SharePoint (users can select from a list of items in a drop-down); therefore, I needed to cast item[“Benchmark_x0020_Category”] to a String array and then retrieve the item at the first index (0).