Configure web server to access SSAS cube using Excel

This post contains step-by-step instructions for configuring web server in order to access cube using excel. After successful configuration users can access the cube using excel and can create reports using cube data.

1. Connect to the server on which you want to configure web server for accessing cube using excel.
2. Create a folder named CUBE under C:\Inetpub\wwwroot.
3. Copy all the contents from the folder C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\bin\isapi into the C:\Inetpub\wwwroot\CUBE directory.
4. Connect to “Internet Information Services” console. Follow following steps for the same. Click on “Start” -> click on option “Run” -> type “inetmgr” and press OK button. Refer following screen shot for the same.

     5. When you click on OK button, it will open an “Internet Information Services (IIS) Manager” console.   IIS Manager Console looks like following one.          

     6. Create “Application Pool”: 

• Right click on “Application Pools” node and select option “Add          Application Pools”. Refer following screen shot for the same.

You will get following screen when you click on “Add Application Pool”. Mention following details       under “Add Application Pool” window.

• Name: CUBE

• .Net Framework version: .Net Framework v2.0.50727

• Managed pipeline mode: Classic

• Start application pool immediately should be in a checked state.

         

      7. Convert to Application:

• Expand “Sites” folder and then expand “Default website” node. Right click on “CUBE” folder and select option “Convert to Application”. Refer following screen shot for the same.

• When you click on “Convert to Application”, it opens “Add Application” form.

• Click on Select button of “Add Application” window and that will open “Select Application Pool” window. Select “CUBE” under “Application Pool” drop down and click OK button.

• When you click on OK button, you will find that CUBE folder now appears as an application. Refer following screen for the same.

       8. Directory Property settings:

• Click on CUBE node and double click on “Handler Mappings” option. Refer following screen shot for the same.

• When you double click on “Handler Mappings” option, you will find following screen.

• Click on “Add Script Map” option which will open “Add Script Map” window. Insert the details as per following screen shot.

• When you click on OK button, you will get following message box. Click Yes.

       9.  Setting Authentication:

• Select CUBE node under IIS manager console. Double click on “Authentication” option.

• When you double click on “Authentication” option that will open Authentication details screen which looks like following one.

• Right click on “Anonymous Authentication” and select “Edit” option. When you click on “Edit” option, it opens “Edit Anonymous Authentication Credentials” window which looks like following one.

• Click on “Set...” button and insert credentials of user.

       10. Change Binding settings:

• Click on “Default Web Site” under IIS Manager Console and click on “Bindings” option. Refer following screen shot for the same.

• When you click on “Bindings” option, it will open “Site Bindings” window. Select a row which contains Port 80 and click on “Edit” button. Refer following screen for the same.

• When you click on “Edit” button, it will open “Edit Site Binding” window. Change Port to 8081 and click on OK button.

        

         11. Start “Actions”:

• Select “Default Web Site” node under IIS manager console. On the right side there is “Actions” pane, click on Start option.       

Conclusion: You have successfully established the configuration settings of web server and now user can access cube using excel.

Create Tabular Project (for newbie)

Tabular model is very new to most of the developers and if someone wants to create his/her first tabular model then this article will help. Following are the steps to create a tabular model. I am giving an example considering only 2-3 dimensions and one fact.

1. Open "Microsoft SQL Server 2012" folder and launch "SQL Server Data Tools"

2. When you launch the wizard, you will get the Start Page of Microsoft Visual Studio. Click on "New Project" and you will get "New Project" wizard. Expand "Business Intelligence" node and click on "Analysis Services" node.

3. Click on "Analysis Services Tabular Project" and give appropriate name and Location to your project. Click on OK button.

4. Click on "Model" menu from a menubar and click on "Import From Data Source.." option.

5. When you click on "Import From Data Source.." option, you will get "Table Import Wizard" wherein you can see different relational databases options which you can use to create your tabular model.

As I am using AdventureWorks sample database, I am selecting "Microsoft SQL Server" option. So select "Microsoft SQL Server" and click on "Next" button.

Note: You can download a sample database named AdventureWorksDW2012 Data File from a link.

When click on "Next" button, you will be get "Connect to a Microsoft SQL Server Database" wizard. Give server name on which you have your database restored and select "Database name"

6. Click on Next button and select the Impersonation information i.e. you can give your windows credentials or you can use service account. Click on Next button.

When you click on Next button, you will get "Choose How to Import the Data" wizard. As I am going to demo this project through tables, I am selecting option "Select from a list of Tables and views to choose the data to import". you can even write a query to import data. Click on Next button.

When you click on Next button, you will get "Select Tables and views" wizard. Select tables which you want to use to create your tabular model. For sample purpose, I am selecting "DimDate", DimProduct, DimProductCategory, DimProductSubCategory and FactInternetSales. After marking the tables as checked, Click on "Finish" button.

When you click on Finish button, you will get following "Importing" wizard.

Your Tabular Project is ready to use. Bydefault you will get Data View of your tabular model. You can check the model by selecting "Diagram View" from Model View option of Model menu.

7. You can create measures on the columns of fact table. let me show you one example. toggle to Data view model. go to FactInternetSales table and select "SalesAmount" column. Click on summation icon and select SUM option. this will create a measure on SalesAmount column with SUM as aggregate function.

8. Open the properties of measure and change name to "Internet Sales Amount". Save the changes. Right click on project and select Deploy option.

9. After successful deployment, you can browse the data using Excel pivot tables similar to multi-dimensional cube. You can hide attributes, measures which you don't want to show to client using "Hide from Clients tools". Right click on column and select option "Hide from Clients tools".

SSAS Tabular with error "OLE DB or ODBC error: Login failed for user 'domain\instance'.; 28000"

If you are trying to create a SSAS Tabular model and if you are getting error similar to following one then you are reading appropriate post as I am going to explain the solution for resolving the issue.
Error looks similar to following one;


OLE DB or ODBC error: Login failed for user 'Domain\instancename$'.; 28000.

A connection could not be made to the data source with the DataSourceID of 'e2d72aea-e51d-4816-a6f6-c47e98716eff', Name of 'SqlServer localhost AdventureWorks2012DW 2'.

An error occurred while processing the partition 'DimCustomer_80fa8624-8c3f-43f0-9e17-950292667334' in table 'DimCustomer_80fa8624-8c3f-43f0-9e17-950292667334'.

The current operation was cancelled because another operation in the transaction failed.

Resolution:

If you are trying to create a tabular model with "Impersonation Information" as "Service Account" then you will have to add "NT AUTHORITY\NETWORK SERVICE" in the "Security" of relational database.

Connect to relational database and expand "Security" folder, add new login i.e.NT AUTHORITY

Errors in the OLAP storage engine: The attribute key cannot be found when processing

The most common error while processing cube and which everyone face when they are newbie to cubes is "The attribute key cannot be found". If the person is newbie to SSAS then probably he/she will find it hard to  get into the exact root cause of error so today I am going to explain this error message and then the resolution for the same. I have created the following example of error message to explain the error in more detailed way.
Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: 'dbo_FactSales', Column: 'ProductID', Value: '1111'. The attribute is 'Product ID'.

The above error explains that the fact table named "FactSales" contains column ProductID with value "1111" but the same  ProductID  is not present in your dimension table. There is a primary key - foreign key relationship exist between the ProductID column of dimension table and fact table named "FactSales" and cube is unable to find ProductID with value 1111 in the dimension table. So the first step one should do is to check either your dimension and fact table contains the value mentioned in the error message (  Value: '1111' in the above example) and the most possible chance is your fact table contains the value (ProductID=1111) but the dimension table for the same does not contain the value (1111). If this is the case then your dimension table is not populated properly so try to bring the ProductID = 1111 in your dimension table. If the ProductID with value 1111 is present in both dimension as well as in fact table then your cube dimension is not updated yet so you can do that by doing "ProcessUpdate" on the corresponding dimension first and then try to process the measure group or partitions.
If you are doing daily processing of your cube (using sql job) then always do ProcessUpdate of your dimensions first and then process your measure groups/partitions.

MDX for getting data for last 7 or 15 days

This is a very common requirement in most of organizations where they want to analyze the sum of last 7 or 15 days data and if you are asked to write MDX for such requirements then you can write your MDX in the following way;

MDX for getting the SUM of last 7 days.


WITH
  MEMBER [Measures].[Sum Of Last 7 Days] AS
    Sum
    (
      {
          [Date].[Calendar].CurrentMember.Lag(6)
        :
          [Date].[Calendar].CurrentMember
      }
     ,[Measures].[Internet Sales Amount]
    )
SELECT
  {
    [Measures].[Internet Sales Amount]
   ,[Measures].[Sum Of Last 7 Days]
  } ON COLUMNS
FROM [Adventure Works]
WHERE
  [Date].[Calendar].[Date].&[20070827];

MDX for getting the SUM of last 15 days.

WITH
  MEMBER [Measures].[Sum Of Last 15 Days] AS
    Sum
    (
      {
          [Date].[Calendar].CurrentMember.Lag(14)
        :
          [Date].[Calendar].CurrentMember
      }
     ,[Measures].[Internet Sales Amount]
    )
SELECT
  {
    [Measures].[Internet Sales Amount]
   ,[Measures].[Sum Of Last 15 Days]
  } ON COLUMNS
FROM [Adventure Works]
WHERE
  [Date].[Calendar].[Date].&[20070827];

You can use above MDX for getting the sum of last any number of days by just changing the value of Index in .Lag(Index) function.

Read partition QueryDefinition (query) using AMO

I found some similar questions on msdn forums where some guys are interested to know how to get the SQL query using AMO which is used there in partition Querydefinition. I have answered one of the similar question on MSDN forum (MSDN thread) but I thought lets share the same on blog too so others can check if they need.


For using AMO, you will need a "Microsoft.AnalysisServices.dll". Reference the dll in your project so you can use all the classes of AnalysisServices namespace. After referencing, you can use following code to get the query used in partition Querydefinition.



        Dim objServer As Server
        Dim objDatabase As Database
        Dim objCube As Cube
        Dim objMeasureGroup As MeasureGroup
        Dim objPartitionSource As QueryBinding
        Dim strQuery As String

        objServer = New Server
        objServer.Connect("localhost")

        objDatabase = objServer.Databases.FindByName("Adventure Works DW 2008R2")
        objCube = objDatabase.Cubes.FindByName("Adventure Works")
        objMeasureGroup = objCube.MeasureGroups.FindByName("Internet Sales")

        For Each objPartition As Partition In objMeasureGroup.Partitions
            objPartitionSource = objPartition.Source
            strQuery = objPartitionSource.QueryDefinition
        Next

        objServer.Disconnect()

While executing above code,  strQuery will return the query from partition  QueryDefinition

No mapping between account names and security IDs was done...

Sometimes you may come across the following error message while deploying cube "No mapping between account names and security IDs was done...". I have answered similar question on msdn forums and the thread for the same is MSDN Thread.

This error comes while deploying cube if one of the active directory user has removed from your active directory but still the reference of that AD user name exist in your SSAS cube Roles.
Connect to Analysis services and go to the "Roles" folder. Open the Role and go to the "Membership" tab, check if "Specify the user and groups for this role" contains any SID (Security Identifier) which looks somewhat like this "S-1-5-21-3623811015-3361044348-30300820-1013". If yes, then remove that and if any such SID is not present then remove and re-add the active directory group and check by doing the deployment.