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