Downloading data from a cube via MDX

By Evan Schmidt

Here are the steps required to download data from an SSAS cube using MDX within a SQL statement.

 

Avoid double hop authentication issues

Since need to use windows authentication to access the cube you may get an error due to “double hop” authentication. 

To avoid this use one of the following methods:

1)    Run the MDX/SQL on your local instance of SQL server

2)    Run the SQL under a SQL Agent job which where the service is a running under a valid domain account

3)    Set-up Kerberos to avoid the double hop issue

4)    Remote desktop into the host server

 

Create a linked server

EXEC master.dbo.sp_addlinkedserver @server = N’ LINKEDSERVERNAME’, @srvproduct=N’ssas’, @provider=N’MSOLAP’, @datasrc=N’SERVERNAME’, @catalog=N’CUBENAME’

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N’LINKEDSERVERNAME’,@useself=N’True’,@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

 

Use open query to execute the MDX

SELECT *

FROM OpenQuery(LINKEDSERVERNAME, ‘SELECT [MEASURES].[Count] on 0 FROM CUBENAME’) as X

 

Quick method to prevent RI errors and lost data

By Evan Schmidt

This mini-blog describes an ETL trick for validating data quality before loading a cube.

 

To build a proper cube with multiple data sources all new data should be joined or mapped to an existing dimension. A robust system may be used to manage these mappings or a quick hack can be used to ensure data quality. The following hack shows how to easily identify RI issues and prevent lost data.  RI issues and lost data will result in cube processing errors and incorrect results.

 

This example shows a list of targets which are typically provided in a spreadsheet and can change often. The list of countries is an existing dimension in the cube.

CountryName	RevenueTarget
Canada	100
US	500

 

CountryID	CountryName
2	Canada
3	France

 

Data quality in the targets table could be enforced by adding a foreign key to the table and requiring a valid Country ID. Alternatively the following code can be used to find RI issues. If an RI problem is encountered then an error is generated.

 

SELECT

   CASE WHEN C.CountryID IS NULL THEN ‘Error Missing Revenue Target Country’ ELSE C.CountryID END AS CountryID

   , T.RevenueTarget

FROM (   

          –List of Targets

          SELECT ‘Canada’ AS CountryName, 100 AS RevenueTarget

          UNION

          SELECT ‘US’ AS CountryName, 500 AS RevenueTarget

   ) T

LEFT JOIN (    

          –List of Countires, Country name must be unique

          SELECT 2 AS CountryID, ‘Canada’ AS CountryName

          UNION

          SELECT 3 AS CountryID, ‘France’ AS CountryName

   ) C ON C.CountryName = T.CountryName

 

 

Msg 245, Level 16, State 1, Line 1

Conversion failed when converting the varchar value ‘Error missing Revenue Target Country’ to data type int.

Import Excel File from a SharePoint site to SQL Server

By Evan Schmidt

This article explains the steps required to successfully import an Excel 2007 File, hosted on a SharePoint site, to SQL Server 2008 R2 using OPENROWSET.

 

1) Install the Office 2010 Data Access Components

Install the Office 2010 Data Access Components which contains the “Microsoft.ACE.OLEDB.12.0” provider.

 

2) Enable Distributed Queries

Change the settings for the SQL server database to allow distributed queries.

 

EXEC sp_configure ‘allow updates’, 1;

RECONFIGURE WITH OVERRIDE;

GO

EXEC sp_configure ‘show advanced options’, 1;

RECONFIGURE WITH OVERRIDE;

GO

EXEC sp_configure ‘Ad Hoc Distributed Queries’, 1;

RECONFIGURE WITH OVERRIDE;

GO

 

3) Ensure the SQL Server service account allows file system interaction

In my experience running the SQL Server service under the “Network Service” account does not correctly pass permissions.

Option A) Change the account which runs the SQL server service to Local System account

Option B) Change the account which runs the SQL server service to an account that has administrator rights on the machine

 

4) Ensure that account running the query has access to the SharePoint site

This will ensure that Excel files located on the SharePoint site can be accessed. Test this by downloading the file manually.

 

5) Ensure that Windows Server can browse SharePoint directories

On Windows Server 2008, install the Desktop Experience component and enable the WebService Service.

 

6) Ensure that the IE security settings allow browsing the SharePoint site

Add the SharePoint site to the trusted sites list and turn trusted site security level to low.

 

7) Use correctly Formatted SQL with $ after the sheet name

The SQL Statement should looks like this:

 

SELECT *

FROM OPENROWSET (          

            ‘Microsoft.ACE.OLEDB.12.0’,

            ‘Excel 12.0;Database=\\sharepoint directory\file name.xlsx’,

            ‘SELECT * FROM [Sheet Name$]’

)