Enabling HTTP access to SSAS

Technorati Tags: C#,SSAS,OLAP,HTTP,Business Intelligence

Due to recent requirement I had to go through several Data cubes which needs to be accessed via a web browser. In the client end I used Telerik pivot grid which provides a comprehensive way of processing the cube. I’ll explain the Telerik part later, lets focus on how to enable SSAS access via HTTP.
SSAS accessibility provides following capabilities,

• Client access over internet/intranet
• Client access from un-trusted domain via proper authentication
• Accessibility via HTTP only gateways
• Authentication – Ah ! some points to remember according to the Microsoft “Authentication methods other than Windows integrated security are required” Specifically, you can use Anonymous connections and Basic authentication when configuring Analysis Services for HTTP access. Digest, Forms, and ASP.NET authentication are not supported.

What is happening inside ?
We are going to create a ISAPI extension (MSMDPUMP that’s what they call it) which loads with IIS, which will eventually gives us the capability to access Analysis Services.
This Service instance can be on

• Same computer
• Remote Computer with in same domain

On IIS MSMDPUMP is connecting to SSAS using Ole DB provider over TCP/IP. Both IIS and SSAS instance MUST BE in same domain or trusted domains.

Find MSMDPUMP files

Simply there are two file you need to find from following location from your local machine, <drive>:\Program Files\Microsoft SQL Server\<instance>\OLAP\bin\isapi

Copy all the files inside the directory to new folder which needs be created as <drive>:\inetpub\wwwroot\OLAP, It is not mandatory to create this OLAP folder inside the inetpub place it where you normally place web site files.
Find and open Internet Information Services Manager by typing IIS in to search,

 

Right Click on application pools and select add new application pool,
there you must fill details as follows. Managed pipe line needs to be in classic mode.



Right click on the created OLAP application pool and choose advanced settings, will show the following screen.



There you have to concentrate on two settings, Enable 32-bit Applications if the MSMDUMP files comes from a 64-bit machine set this to false other wise set to true. Identity should be set to either Network Service or to Application Pool Identity depending on the windows version that you are using.

Now create a web site and map MSMDUMP file location to it.

Make sure to use same application pool you created previously to this web site.
Go to the Authentication module of the site and disable anonymous authentication and enable windows authentication.



Click on the OLAP site and Double-Click Handler mappings,



Click Add Module Map on right side pan, And fill the details as follows.






Click on Request Restrictions Button and Select verbs Tab, Make sure that All Verbs are selected.



Click Ok and then Click ‘Yes’ To allow ISAPI extension.
Open MSMDUMP file folder and open MSMDPUMP.INI file, If you have SSAS installed same machine where IIS is hosted then it should looks like follows. for example this sever name can be (<ServerName>TEST-SRV01:55555</ServerName>)



To granting permissions use SQL Server Management Studio. Under the Database | Roles folder, you can create roles, specify database permissions, assign membership to Windows user or group accounts, and then grant read or write permissions on specific objects.

Anonymous

Add to the Membership list the account specified in Edit Anonymous Authentication Credentials in IIS. For more information, see Anonymous Authentication,

Windows authentication

Add to the Membership list the Windows user or group accounts requesting Analysis Services data via impersonation or delegation.

Basic authentication

Add to the Membership list the Windows user or group accounts that will be passed on the connection string.
Testing Accessibility of SSAS Service via Excel.
Go to excel > Data > From Other sources > SSAS Service



in server name text box type your hosted address as follows,

http://localhost:8088/msmdpump.dll

and for the Authentication mode,
choose Use Windows Authentication if you are using Windows integrated security or NTLM, or Anonymous user.
For Basic authentication, choose Use the following User Name and Password, and then specify the credentials used to sign on. The credentials you provide will be passed on the connection string to Analysis Services.
If you are succeeded, you will see following screen with available Cubes.

Globalization WPF MVVM Application

When it comes to develop an international application, it is a must to consider globalization, where you application must support for different languages and regional settings. This post I’m going to demonstrate how to create a standard WPF MVVM application which supports different language interfaces.

1. First we have to create a C# WPF project named “WpfMultiLanguageApplication”

2. Add second Class Library project named “WpfMultiLanguageAplication.Resources” and reference it to previous project.

3. Create Resource file (.resx) with the name “LabelResources” and add following string to it. And make sure that you have to set Access Modifier to Public.

4. Make a copy of same file and re name it to “LabelResources.si-LK.resx”. This is the most important step that suffix you have to put comes from the list of ISO values mentioned here. You can find your intended language ISO code from here

Language Culture Names, Codes, and ISO Values

This suffix will help your application to automatically pick relevant resource file based on the current culture settings. If not appropriate available default will be shown. I have made following changes to the file named “LabelResources.si-LK.resx”

And please make sure that this file also has to have a public access modifier.

5. Now I’m going to create the ViewModel for WPF application main window.

Create following class inside the ViewModel Folder

   1:  //----------------------------------------------------------------------- 

   2:  // <copyright file="MainWindowsViewModel.cs" company=""> 

   3:  //     Copyright  2014. All rights reserved. 

   4:  // </copyright>

   5:  // <summary>

   6:  // The MainWindowsViewModel class. target CLR version 4.0.30319.18444.

   7:  // Created by : xx on xxx.

   8:  // Created date time: 7/10/2014 11:49:11 AM.

   9:  // </summary>

  10:  //-----------------------------------------------------------------------

  11:  using System;

  12:  using System.Collections.Generic;

  13:  using System.ComponentModel;

  14:  using System.Linq;

  15:  using System.Runtime.CompilerServices;

  16:  using System.Text;

  17:  using System.Threading.Tasks;

  18:  using WpfMultiLangApplication.Resources;

  19:   

  20:  namespace WpfMultiLangApplication.ViewModel

  21:  {

  22:      /// <summary>

  23:      /// The MainWindowsViewModel class.

  24:      /// </summary>

  25:      public class MainWindowsViewModel : INotifyPropertyChanged

  26:      {

  27:          private string windowTitle;

  28:   

  29:          public string WindowTitle

  30:          {

  31:              get { return windowTitle; }

  32:              set

  33:              {

  34:                  windowTitle = value;

  35:                  NotifyPropertyChanged();

  36:              }

  37:          }

  38:   

  39:   

  40:   

  41:          public MainWindowsViewModel()

  42:          {

  43:              this.WindowTitle = LabelResource.WindowTitle;

  44:          }

  45:   

  46:          public event PropertyChangedEventHandler PropertyChanged;

  47:   

  48:          private void NotifyPropertyChanged([CallerMemberName] String propertyName = "")

  49:          {

  50:              if (PropertyChanged != null)

  51:              {

  52:                  PropertyChanged(this, new PropertyChangedEventArgs(propertyName));

  53:              }

  54:          }

  55:      }

  56:  }

Now consider the line 43 I used the previously created resource text to set window title. This is one way to reference resource from programmatically. I’ll show others setting via XAML.

Add xml name space to window

<Window x:Class="WpfMultiLangApplication.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        xmlns:r="clr-namespace:WpfMultiLangApplication.Resources;assembly=WpfMultiLangApplication.Resources"
        xmlns:context="clr-namespace:WpfMultiLangApplication.ViewModel"
        Title="{Binding WindowTitle}" Height="84" Width="389">

Now you can reference your resource file contents ass follows.

<Grid>
        <TextBox HorizontalAlignment="Left" Height="23" Margin="10,10,0,0" TextWrapping="Wrap" Text="{x:Static r:LabelResource.TextBoxDefault}" VerticalAlignment="Top" Width="281"/>
        <Button Content="{x:Static r:LabelResource.OkButtonLabel}" HorizontalAlignment="Left" Margin="296,10,0,0" VerticalAlignment="Top" Width="75"/>
    </Grid>

6. Changing culture settings programmatically and test the application

Add following line to App.xaml.cs

WpfMultiLangApplication.Resources.LabelResource.Culture = new System.Globalization.CultureInfo("si-LK");

On screen color picker

Picking color from a web page.

Color them is playing a key role inside any application, it helps user to recognize rather remember certain information like Errors, Notifications and Information. A recent project that I had to work on (A Dashboard), had a number of different colors specified from the design. And also each and every client meeting ended up with list of new color suggestion for example “Can you pick a color similar to this one/that one/darker version of this …..”. Picking a color from a image is easy if you have tools like Photoshop, but In my Dev machine I don’t have one, Even if I had it takes bit of extra memory JUST TO PICK A COLOR CODE.

Some time ago I visited a page from MSDN Forum which is about developing a screen pixel color reader similar to this one How do I test the color of a certain pixel or point.

With the help of it I have developed following Color Picker Application which will capture a screen pixel and copies the color code to Clipboard.

How to use

1. Run the ColorPicker.exe from …\ColorPicker\bin\Release\

2. Once it opened place the mouse cursor inside the ColorPicker Window and press and hold the mouse button.

3. Release the mouse button on anywhere on the screen to reveal color.

Code Explanation

        [DllImport("user32.dll")]
        static extern IntPtr GetDC(IntPtr hwnd);

        [DllImport("user32.dll")]
        static extern Int32 ReleaseDC(IntPtr hwnd, IntPtr hdc);

        [DllImport("gdi32.dll")]
        static extern uint GetPixel(IntPtr hdc, int nXPos, int nYPos);

        public Form1()
        {
            InitializeComponent();
        }

        private void lblColor_MouseDown(object sender, MouseEventArgs e)
        {
            var x = e.X;
            var y = e.Y;
        }

        /// <summary>
        /// Get the pixel color once mouse button released
        /// </summary>
        /// <param name="x">x position of the cursor</param>
        /// <param name="y">y position of the cursor</param>
        /// <returns>Color</returns>
        static public System.Drawing.Color GetPixelColor(int x, int y)
        {
            IntPtr hdc = GetDC(IntPtr.Zero);
            uint pixel = GetPixel(hdc, x, y);
            ReleaseDC(IntPtr.Zero, hdc);
            Color color = Color.FromArgb((int)(pixel & 0x000000FF),
                         (int)(pixel & 0x0000FF00) >> 8,
                         (int)(pixel & 0x00FF0000) >> 16);
            return color;
        }

        /// <summary>
        /// Load the selected pixel's color in to background label, 
        /// and code in to text box at the bottom
        /// </summary>
        private void lblColor_MouseUp(object sender, MouseEventArgs e)
        {
            this.Cursor = new Cursor(Cursor.Current.Handle);

            lblColor.BackColor = GetPixelColor(Cursor.Position.X, Cursor.Position.Y);
            txtColor.Text = HexConverter(lblColor.BackColor);
            Clipboard.SetText(txtColor.Text);
        }

        /// <summary>
        /// Converts color in to Hex Format
        /// </summary>
        /// <param name="c"></param>
        /// <returns></returns>
        private static String HexConverter(System.Drawing.Color c)
        {
            return "#" + c.R.ToString("X2") + c.G.ToString("X2") + c.B.ToString("X2");
        }

Download Code Here

Power and Performance of C++ Comes to C# with .NET Native

Sometimes it is very difficult choice to make whether to go for C++ with its power and performance or choose C# with the managed code and development speed. Microsoft recently gave a resolution to above question. Microsoft “.Net Native” is a compiler (currently supports for x64) which improves performance of .NET applications by precompiling managed code in to native codes using Microsoft’s C++ backend infrastructure. This mechanism is different from the NGen. 

NGen will compile .NET managed code into processor specific native images and stores it in local computer’s native image cache. Then the runtime can use native image cache instead of Just-In-Time(JIT) compiler to compile the original assembly.

However .NET native initially generates MSIL which will next processed by C++ optimizer, generating MDIL (Machine dependent intermediate language). These optimizations have increased the start up time up to 60%, while the memory footprint has been lowered by 15%, according to Mani Ramaswamy, Project Manager of the .NET Native team. .NET Native is currently a preview and is targeted at Windows Store apps, but the team intends to extend it to the general desktop .NET apps.

Getting started with .NET Native

TypeScript

Concerning last couple of years JavaScript has gone through an explosive growth and with HTML5 it delivers a rich user environment compared to any other server side scripting models.
But what is wrong with the JavaScript;
even though it is easier to learn basics of JavaScript; writing a application scale JavaScript is hard. And most importantly it is meant to be a scripting language not a programming language, so it lacks proper structuring mechanism which larger application model necessarily look for. 
But people still writing large scale applications using JavaScript with the help of tools like Google’s GWT and scriptsharp (a C# => JavaScript code transformer ).
TypeScript helps to strengthen JavaScript with what is lacks features like classes, module and static typing.

TypeScript is a language for application scale JavaScript development

Ultimately all TypeScript codes will be compiled to a plain JavaScript code. And most importantly the compiled code and the compiler works on Any browser, Any host and Any OS. Simply all JavaScript codes are TypeScript and just copying and pasting will work, optionally TypeScript can contain Static types Classes and Modules where all of those disappear at runtime.
Compiler and all libraries related are Open Source.

Install TypeScript

Once you installed there will be a new project type as follows;

Once the project created solution explorer will looks like,

Simply I’m not going to do any modifications to the code it generated as an example. Lets just go through the app.ts file

class Greeter {
    element: HTMLElement;
    span: HTMLElement;
    timerToken: number;

    constructor(element: HTMLElement) {
        this.element = element;
        this.element.innerHTML += "The time is: ";
        this.span = document.createElement('span');
        this.element.appendChild(this.span);
        this.span.innerText = new Date().toUTCString();
    }

    start() {
        this.timerToken = setInterval(() => this.span.innerHTML = new Date().toUTCString(), 500);
    }

    stop() {
        clearTimeout(this.timerToken);
    }

}

window.onload = () => {
    var el = document.getElementById('content');
    var greeter = new Greeter(el);
    greeter.start();
};

For the moment forget about all the matters with JavaScript, code above is very simple (even though I’m not a JavaScript expert). 

window has “onload” event which is subscribed with an anonymous method contains a new Greeter object with parameterized constructor (taking an HTMLElement as argument)…. Once start method called time should be displayed with 500 milliseconds interval.

Lets just see the output



While its running lets peek in to debugger generated code,



it has this app.js (Which is JavaScript) mapped to app.ts (which is TypeScript) and with in app.js we can see pure JavaScript code rendered.

var Greeter = (function () {
    function Greeter(element) {
        this.element = element;
        this.element.innerHTML += "The time is: ";
        this.span = document.createElement('span');
        this.element.appendChild(this.span);
        this.span.innerText = new Date().toUTCString();
    }
    Greeter.prototype.start = function () {
        var _this = this;
        this.timerToken = setInterval(function () {
            return _this.span.innerHTML = new Date().toUTCString();
        }, 500);
    };

    Greeter.prototype.stop = function () {
        clearTimeout(this.timerToken);
    };
    return Greeter;
})();

window.onload = function () {
    var el = document.getElementById('content');
    var greeter = new Greeter(el);
    greeter.start();
};//# sourceMappingURL=app.js.map

And most importantly check the index.html, it refers the app.ts as

 <script src="app.js"></script>

LINQ to SQL Improve Query Performance and IEnumerable<T> vs. IQueryable<T>

CodeProject Most of the business developments involve with the Data tier integration; which has a data layer consist of queries. Entity Framework is one solution to write a data layer quickly. 
In writing a data layer completely using LINQ to SQL, it has to be precisely understood how the query is going to be executed.
I have a Table with 20,000 records and a typical select query statistics as follows.

Since my database on the same machine where result shows; it took very little time. but if we check the number of bytes received from the server is: 1410798 Bytes which is about 1.34 MB. So this is normal selection with all data.
Let say I wants to count number of rows, and that is my business requirement. Typical SQL Query would be,

SELECT COUNT(*)
  FROM [CodeFirstDemoAppDB].[dbo].[Students]

Now I’m going to do the same through LINQ, And I’ve used three different way to do so.

   1:  DAL.SchoolContext db = new DAL.SchoolContext();

   2:  // using var 

   3:  var results = db.Students;

   4:  Console.WriteLine(results.Count());

   5:  // using IEnumerable type

   6:  IEnumerable<Model.Student> resultsEnumerable = db.Students;

   7:  Console.WriteLine(resultsEnumerable.Count());

   8:  // using IQueryble type

   9:  IQueryable<Model.Student> resultsQueryble = db.Students;

  10:  Console.WriteLine(resultsQueryble.Count());

Following shows the respective query executed at the DB tracked with SQL profiler.

Type	Query
using var (which remains DbSet Type)	SELECT [GroupBy1].[A1] AS [C1] FROM ( SELECT COUNT(1) AS [A1] FROM [dbo].[Students] AS [Extent1] ) AS [GroupBy1]
using IEnumerable	SELECT [Extent1].[ID] AS [ID], [Extent1].[FirstMidName] AS [FirstMidName], [Extent1].[LastName] AS [LastName], [Extent1].[EnrollmentDate] AS [EnrollmentDate] FROM [dbo].[Students] AS [Extent1]
using IQueryble	SELECT [GroupBy1].[A1] AS [C1] FROM ( SELECT COUNT(1) AS [A1] FROM [dbo].[Students] AS [Extent1] ) AS [GroupBy1]

As we can see here use of IEnumerable type caused query executed at the line;

IEnumerable<Model.Student> resultsEnumerable = db.Students;

and retrieve all the records to the business layer to make count at business layer level while transporting approximately about 1.34MB. For quick information we can check the client statistics for other two types which has same SQL query output;



It is about 6 KB information transport through the network.

Conclusion

As a conclusion for business operations, which does not need to bring whole data set to the business layer Beware of your LINQ query and types you use to hold results.

For further learning about LINQ’s deferred execution please refer this article.

Writing Traceable Code

Some times we have to work independently on a particular layer with in multi layered project, For example working on a typical business layer with out knowing how the service layer provides its features to a consumer.

Basically in such layered architecture a developer cannot expect each and every argument (parameter) received is up to the requirement. so each and every time we have to check all the boundary conditions to make sure my own code works perfectly with out running in to any trouble.
Let me take the following example;

Through out the code until last few lines this NewUserRegistration Method checks for any faults in in coming parameters even though this looks an overhead believe me its going to save lots of user time when the code beings to consume by other layers.

As a software developer we cannot guarantee what might happen between the layers; if the system scales up your layer might be running as an isolated tier which has been exposed to multiple other consumers. So as said in your method you must guarantee that inputs are properly validated before process them. This approach saved a lots of developer time when it comes to QA and Developer testing.

Check-in SonarQube friendly code…!

Writing codes, even for a simplest program is not an easy task (only if you anticipate all movements of the end user ;)). Writing codes with higher readability is much harder. At the end of the day as a developer I check-in fairly readable (at least for me) code for everyone. But spacing, organizing of variables and commenting styles are somewhat different from developer to developer. Some people do not comment at all because their code exactly saying what’s happening throughout the code. These patterns and practices are debatable at the developer’s perspective, but at the industry level we have to go with a common pattern/practices which will be accepted by majority like SonarQube code quality measurement rules (most likely ethics). 

During the past couple of months we were able to maintain a good rule complacence with the help of few external tools. With the help of these we can easily clear most common simple issues like ordering and spacing across the project at once.

StyleCop - http://stylecop.codeplex.com/

Once you download and install stylecop make sure all of your projects' properties are in following manner. 

You will find following [Run StyleCop] command in right click context menu, once click this will show following warnings dialog. So we can easily double click on each and edit quickly (no needs to visit Sonar Dashboard each time). 

CodeMaid http://www.codemaid.net/

Which is an an open source visual studio extension to cleanup, dig through and simplify our C#, C++, F#, VB, XAML, XML, ASP, HTML, CSS, LESS, JavaScript and TypeScript coding. Once installed you will see following commands in right click context menu.

Clean up - Cleanup random white space into a simple standard order. Add unspecified access modifiers. Utilize Visual Studio’s built-in formatting capabilities. Remove and sort using statements. And do it all automatically on save or on demand, from an individual file to the entire solution.

Reorganizing - Reorganize the layout of members in a C# file to follow Microsoft’s StyleCop convention, or your own preferences.

Finally make changes to your class templates.

Find visual studios class template location: default location C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\ItemTemplates\CSharp\Code\1033\Class

Open the class file and paste following,

//----------------------------------------------------------------------- 
// <copyright file="$safeitemrootname$.cs" company="$registeredorganization$ i3"> 
//     Copyright $registeredorganization$ i3 $year$. All rights reserved. 
// </copyright>
// <summary>
// The $safeitemrootname$ class. target CLR version $clrversion$.
// Created by : $username$ on $machinename$.
// Created date time: $time$.
// </summary>
//-----------------------------------------------------------------------
using System;
using System.Collections.Generic;
$if$ ($targetframeworkversion$ >= 3.5)using System.Linq;
$endif$using System.Text;
$if$ ($targetframeworkversion$ >= 4.5)using System.Threading.Tasks;
$endif$
namespace $rootnamespace$
{
 /// <summary>
 /// The $safeitemrootname$ class.
 /// </summary>
 class $safeitemrootname$
 {
 }
}

Once done just close and restart the Visual studio and try adding new class file which may looks like following.

Using Pre-Build, Post Build Events in C# Project

I have a Project which runs as a Windows service – which is a WCF service hosted under self-hosting. For each build I want to stop the service and copy build files to the output and restart the service. Normally this would be possible through Build Events as shown in below

But with the TFS CI build integrations I had to face a problem since TFS server does not have such service(s) installed. It gave me an error saying no such service exists and also stopped the CI build popping out an error. What I had to do was modify the pre build and post build command line as follows

Pre-Build Event

Post Build Event

This worked fine when no service found at the environment, finally this resolved my problem.