SSAS The connected user is not an Analysis Services server administrator.

Problem

On a new development system where my windows account has local admin rights, and sysadmin rights in the SQL Server 2012 with SSAS installation.

With SQL server Data Tools 2010 I want to create and deploy an Analysis Services Data mining project. When the Data source, views and Mining structures are setup, I want to deploy on the server for the first time, But I get this error message:

‘The connected user is not an Analysis Services server administrator.’

Solution

Reason of the problem is the UAC on de development OS. Solution is to add my windows account explicitly as an Analysis Services System account. But when I use SSMS to connect to analysis services and try to add my name I will get a similar error message.

the solution is:

• run SSMS as an administrator
• connect to the Analysis Services Server
• right-click on the server name and select ‘Properties..’
• In the Security page of the Server Properties window, click on the ‘Add…’ button to add a new account to the Server administrators, select your own windows account and add to the servers.

By explicitly adding your own account to the Analysis Services Server administrators, you can now create and deploy projects to the Analysis Services Server without running SSMs or SQL Server Data Tools.

SSAS error Unable to connect to the localhost server. Make sure that the server is started.

Problem

An error message on a new Development system with SQL Server 2012. The first time I created a SSAS Data Mining project with BI studio 2010.

When the Data source connection and a view was made, I switched over to the Mining Model Viewer, and got this error message:

‘Unable to connect to the localhost server. Make sure that the server is started.‘

This occurs because the SQL server was setup with a named instance. When you create a new Analysis Services project, the project defaults to the ‘localhost’ as Deployment Server.

Solution

Localhost is fine as long as the default instance is the standard instance.

To fix the problem, we need to set the Deployment Server in the project to the Named instance of the local SQL server.

In the Solution Explorer, right-click on the Project and select Properties…

In the Project property Pages tree view, under Configuration Properties, Select Deployment

In the pane on the right-hand side, in the Server property replace ‘localhost’ by the named instance, e.g. ‘MyServer\MyInstance’ and click OK to close the dialog.

In the Mining Model Viewer, click on the little refresh button next to the empty viewer dropdown box.

then the name of the Viewer selected during the Views setup appears in the dropdown together with a dialog box to build and deploy the project.

From then on you can continue your Analysis Services project.

A quick way to convert integer to nchar right aligned with leading zeroes

For an existing database table I urgently needed to translate integer values into nchar with leading zeroes

e.g. for an nchar destination column of length 2:

• convert value 1 to ‘01’
• convert value 2 to ‘02’
• convert value 11 to ‘11’

The simplest solution that came to my mind at that moment was to add an integer value that was a multiple of base 10 with enough trailing zeroes to act as leading zeroes in the nchar field. In cas of the nchar(4) column I need 10000, in case of nchar(2) I need to add 100.

• convert value (100 + 1) to ‘101’
• convert value (100 + 2) to ‘102’
• convert value (100 + 11) to ‘111’

The result has one character too much, therefore I need to subtract this first character sith the SUBSTRING function:

• SUBSTRING(‘101’, 2, 2) to ‘01’
• SUBSTRING(‘102’, 2, 2) to ‘02’
• SUBSTRING(‘111’, 2, 2) to ‘11’

T-SQL example: I have a dimension table Dim_YearMonth that needs to be populated with data from a Dim_Year and Dim_Month table. The table Dim_YearMonth has one nchar(7) column calle [YearMonthName] that is used as display value in dropdown lists. this has to be populated with datalike: ‘2014-01’, ‘2014-02’.

I have a table Dim_Year with one smallint column YearId ranging from 2005 to 2025.

There is a second table Dim_Month with smallint column MonthId ranging from 1 to 12.

Part of the T-SQL statement to combine year and month into ‘YYYY-MM’ is:

, CAST(y.YearID as nchar(4))+ '-' + SUBSTRING( CAST( (100+m.MonthId) as NCHAR(3)),2,2) as YearMonthName

The T-SQL statement to select Year and month combined and insert into the table Dim_YearMonth:

select (y.YearID *100 + m.MonthId) as YearMonthId

    , CAST(y.YearID as nchar(4))+ '-' + SUBSTRING( CAST( (100+m.MonthId) as NCHAR(3)),2,2) as YearMonthName

    , YearID, MonthId

    from dbo.Dim_Year as y, dbo.Dim_Month as m

The result looks like this:

YearMonthId    YearMonthName    CurrentYear    CurrentMonth
201308         2013-08          2013           8
201309         2013-09          2013           9
201310         2013-10          2013           10
201311         2013-11          2013           11
201312         2013-12          2013           12
201401         2014-01          2014           1
201402         2014-02          2014           2
201403         2014-03          2014           3