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.

How to find a hidden unicode character using SQL Server

By Evan Schmidt

This mini-blog describes how to analyze every character in a unicode text string in order to find hidden characters, variable-byte characters, and unexpected unicode characters. This can be a useful because certain unicode characters can cause some applications to fail unexpectedly. This code will help you analyze the data and find the problem character.

 

 

DECLARE @nstring NVARCHAR(100)

SET @nstring = N’TeSt’

 

DECLARE @position int

SET @position = 1

 

DECLARE @CharList TABLE (

Position INT,

UnicodeChar NVARCHAR(1),

UnicodeValue  INT

)

 

WHILE @position <= DATALENGTH(@nstring)

   BEGIN

   INSERT @CharList

   SELECT @position as Position

      , CONVERT(nchar(1), SUBSTRING(@nstring, @position, 1)) as UnicodeChar

      , UNICODE(SUBSTRING(@nstring, @position, 1)) as UnicodeValue

   SET @position = @position + 1

   END

 

SELECT * FROM @CharList

 

 

The results of this query will display the unicode value for every character in the string:

Position	UnicodeChar	UnicodeValue
1	T	84
2	e	101
3	Ș	536
4	t	116

 

Note the particularly large unicode value which shows this character is clearly not a standard ASCII value.  

How to create a comma delimited list using SQL Server

By Evan Schmidt

This mini-blog describes a quick method to create a comma separated field within a SQL query using SQL Server.

 

In this example there are two teams with multiple sponsors for each team.

TeamID	TeamName
1	Ducks
2	Bears

 

TeamID	SponsorName
1	Fuller Co.
1	Martin & Chase
2	Bo Thomas Inc

 

You would like the query to return a delimited list that contains the sponsors for each team.

TeamID	TeamName	SponsorList
1	Ducks	Fuller Co.; Martin & Chase
2	Bears	Bo Thomas Inc

 

 

The following query will takes the first two tables as inputs and creates the third table. Please note that the “FOR XML PATH” statement will cause xml restricted characters to be replaced.

 

SELECT

L.TeamID

, L.TeamName

—replace xml friendly ‘&amp;’ with ‘&’

, REPLACE(

   —limit the final output to 100 characters

   CONVERT(NVARCHAR(100),

          —the first 3 characters of this new field are removed

          SUBSTRING(

                (

                —the xml output is created which creates the delimited list

SELECT ( ‘; ‘ + TMP.SponsorName) –requires no column name

                FROM (

                       —sponsor list

                       SELECT 1 as TeamID, ‘Fuller Co.’ as SponsorName

                        UNION ALL

                       SELECT 1 as TeamID, ‘Martin & Chase’ as SponsorName

                       UNION ALL

                       SELECT 2 as TeamID, ‘Bo Thomas Inc’ as SponsorName

                ) TMP

                WHERE TeamID = L.TeamID

                FOR XML PATH(”) —required empty xml path

 

          ), 3, 100)

   )

,‘&amp;’,‘&’) AS SponsorList

  

FROM (

                —team list

                SELECT 1 as TeamID, ‘Ducks’ as TeamName

                UNION ALL

                SELECT 2 as TeamID, ‘Bears’ as TeamName

) L

 

How to create an auto generated row count within a query using SQL Server

By Evan Schmidt

This mini-blog describes how to dynamically generate an ID column within a SQL Query using SQL Server 2008.

 

We will start with a simple table called “Table1”:

Column1

Yes

No

Maybe

 

 

We want to create an Auto Generated ID for each row returned from the table. For Example:

Column1	AutoGeneratedID
Maybe	1
No	2
Yes	3

 

 

The following SQL Statement can be used to generate this identity column:

select Column1

, ROW_NUMBER() over (order by Column1) as AutoGeneratedID

from dbo.Table1

 

Note how the row count is assigned based on the ORDER BY clause specified within the OVER statement.

 

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$]’

)