Failed to add a data source to the dashboard designer (PerformancePoint Services 2010)

I got the IT department to install SharePoint 2010 Enterprise edition on the server for me and specifically requested to have the PerformancePoint services started. So I was not aware if the PerformancePoint services has been configured properly. Never mind that, I was very happy already that I got SharePoint 2010 for me to play with :).

So I opened up SharePoint Central Administration page and worked my way through to creating a new web application and adding the Business Intelligence page, which has got Dashboard Designer in it.

After several clicks here and there, I then finally managed to find my way to get to the Dashboard Designer. I was getting excited to use the tool till…

When i tried to add a new data source from analysis services to the dashboard designer , an error message appears  – “The following data source cannot be used because PerformancePoint Services is not configured correctly”.  This error does not tell you much what not configured properly. After googling and trying a few solutions, finally I managed to resolve it.

My PerformancePoint Services has not been configured correctly because:

1) The Secure Store Service Application was not created and the Secure Store Service key was not generated.  This key is used to encrypt the credentials used to connect to the data source.

2) The Unattended Service Account(username and password) was blank. This is the account that will be used to connect to the data sources.

Solutions:

1)  Go to Central Administrations –> Manage Service Applications –> Secure Store Service Application. Click the New button to create a new secure store target application. Set the Target Application ID and Display Name. The Target Application ID is a unique key which once generated cannot be changed afterwards. Set the Target Application Type to Group and the Target Application Page URL to None. Next step is to generate the key. Click the “Generate New Key” button then put in the pass phrase (i.e. password which you need to remember).

2) Go to Central Administrations –> Manage Service Applications –> PerformancePoint Service Application –> PerformancePoint Service Application Settings. Set the Unattended Service Account username and password. Make sure taht the user has access to the data source.

Once done, go to the web page that you created in Step 1) . Select “PerformancePoint Content” then click on the “Add new item” link. This will pop up the dashboard designer.

In the dashboard designer, create a new data source to the cube. Select Unattended Service Account as the authentication method. Then click on the “Test Data Source” button to test the connection. The connection should be successful 😀 If it fails, refresh the secure store service application keys.

Read Full Post »

How to detect Reporting Services version information?

I came across the following error whilst connecting to the client report server:

The version of the report server database is either in a format that is not valid, or it cannot be read. The found version is ‘C.0.8.40’. The expected version is ‘147’. (rsInvalidReportServerDatabase)

This is the first time I saw this error. The error clearly says that the report server database installed is not the version of the SQL Server that is currently in use i.e. SQL Server 2008 R2. I was wondering what the C.0.8.40 version refers to. Googled a few times finally directed me to the Microsoft TechNet with a list of the versions of Reporting Services and the C.0.8.40 version is Report Server 2005. Uninstalling this version and installing the Reporting Services 2008R2 resolves the error.

http://technet.microsoft.com/en-us/library/bb630446.aspx

Version Information

The following table provides a list of the version numbers for the report server and report server database released to date:

Release	Report server	Report server database
SQL Server 2008 R2	10.50.nnnn.nn	n/a
SQL Server 2008 Service Pack 1 (SP1)	10.0.2531.00	C.0.9.45
SQL Server 2008	10.0.1600.00	C.0.9.45
SQL Server 2005 Reporting Services Service Pack 3 (SP3)	9.0.4035.00	C.0.8.54
SQL Server 2005 Reporting Services Service Pack 2 (SP2)	9.0.3042.00	C.0.8.54
SQL Server 2005 Express with Advanced Services	9.0.3042.00	C.0.8.45
SQL Server 2005 Reporting Services Service Pack 1 (SP1)	9.0.2049.00	C.0.8.43
SQL Server 2005 Reporting Services	9.0.1399.01	C.0.8.40
SQL Server 2000 Reporting Services Service Pack 2 (SP2)	8.00.1038	C.0.6.54
SQL Server 2000 Reporting Services Service Pack 1 (SP1)	8.00.0878	C.0.6.51
SQL Server 2000 Reporting Services	8.00.0743	C.0.6.43

Read Full Post »

Failed to start SSAS 2008R2 service …

I tried to start the service through run\services.msc and got the following error “The Sql Server Analysis Services (MSSQLSERVER) service on local computer started then stopped. Some services stop automatically if they have no work to do, for example the performance logs and alerts service.”

 I tried to start the service using SQL server configuration manager and got the following error “The request failed or the system did not respond in a timely fashion. consult the event log or the application error logs for details.”

The service is using Network Services built-in account; the same account used to start SQL Server database, Reporting Services and Integration Services.

After browsing through the Internet for solutions, finally found one that works for me, that is -clear the application event logs from the Event Viewer then  start the Analysis Services using “Local System” account. Once started, set it back to Network Services and restart the service.  🙂

Read Full Post »

How to assign a different color or pattern on each category group in the bar chart on SSRS 2008R2 if you don’t have any series?

Found this by trial and error 🙂

When there is no series groups in the bar chart, all of the bars will appear to have the same colour as they are all belong to one category group. In order to have a different colour on each bar, drag and drop the same field in the category groups into the series group.

To assign a different pattern (Note: the same method to assign a different colour)  for each series on SSRS 2008R2, right click on the chart then select “Series Properties”. In the Series Properties, select “Fill” option. Set the Fill type to Pattern and the Pattern Style to use custom expression such as below (using Switch function):

=Switch(Fields!Sequence.Value = 1, “Cross”, Fields!Sequence.Value = 2, “Divot”, Fields!Sequence.Value = 3, “DottedDiamond”, Fields!Sequence.Value = 4, “LightVertical”, Fields!Sequence.Value = 5, “Plaid”, Fields!Sequence.Value = 6, “Shingle”, Fields!Sequence.Value = 7, “Sphere”, Fields!Sequence.Value = 8, “Wave”, Fields!Sequence.Value = 9, “ZigZag”)

Read Full Post »

How to access cube via HTTP?

There are 2 ways in connecting to cube i.e. Windows Authentication and via HTTP.

I stumbled with setting this up by following the instructions in the blog below:

http://bloggingabout.net/blogs/mglaser/archive/2008/08/15/configuring-http-access-to-sql-server-2008-analysis-services-on-microsoft-windows-server-2008.aspx

My scenario is:

I have 2 servers i.e. DBServer and AppServer. Due to the client infrastructure security, both servers are not joined to the client domain so they both are workgroups.

The DBServer has SQL Server 2008R2, Integration Server 2008 R2 and Analysis Server 2008R2. The AppServer has IIS and Report Server 2008R2. My cube is hosted on the Analysis Server on DBServer. My mdx reports are all on the AppServer. Since these two servers have not joined domain, the only way for my reports to make connections to the cube is via HTTP through IIS.

Steps in setting this up (after several attempts):

1. Obtain the binary files (.dll). Go to C:\inetpub\wwwroot then create a new folder named OLAP.

Copy all the files from the DBServer C:\Program Files\Microsoft SQL Server\MSAS10.MSSQLSERVER\OLAP\bin\isapi folder. Then paste into the C:\inetpub\wwwroot\olap directory of the AppServer.

2. Open IIS on AppServer. Create a new application pool and name it as OLAP. Select the latest .NET Framework version. Set the Managed Pipeline Mode to Classic.

3.  On IIS, navigate to Sites, expand the Default Web Site, then select OLAP.  Right click on it then select Conver to Application. Browse to the OLAP application pool you just created in Step 2.  Set the physical path to C:\inetpub\wwwroot\OLAP  by browsing to locate the file. Click the Test Settings button and notice if there is any warning sign (in my case, pass-through authentication didn’t work). If there is, click on the Connect As button then select the Specific User option.  Set the username and password. Ensure that the user does exist in the DBServer and has the necessary permission.

4.  Click your virtual directory node in the IIS and select Handler Mappings from the menu. Make sure the directory has Read and Script permissions. This can be checked by clicking the “Edit Feature Permissions…” in the top right corner of the Handler Mappings screen.

5. In the top right corner of the Handler Mappings screen click the option ” Add Script Map…“.  Enter *.dll in the Request path text box. In the Executable option, enter the full path name to msmdpump.dll. In this example, it would be: C:\wwwroot\olap\msmdpump.dll.  Name the Script Map. In this example, we call it OLAP.

6. Click the OK button to accept the settings.

7. When you hit OK to add the script mapping you will be prompted if you want to enable the ISAPI extension.  Click the Yes button to accept the settings.

8. The configuration of this decision can be found under ISAPI and CGI Restrictions of the server.

9. Search for your extension and name it as OLAP.

10. Click the OK button to accept the settings.

11. Open the msmdpump.ini file located in your folder and take a look at the contents of this file. It should look like the following:

<ConfigurationSettings>

<ServerName>localhost</ServerName>

<SessionTimeout>3600</SessionTimeout>

<ConnectionPoolSize>100</ConnectionPoolSize>

<MinThreadPoolSize>0</MinThreadPoolSize>

<MaxThreadPoolSize>0</MaxThreadPoolSize>

<MaxThreadsPerClient>4</MaxThreadsPerClient>

</ConfigurationSettings>

Change the server name to the application /web server. In this case, the AppServer.

12. There is no need to restart the IIS.

13. Click your virtual directory node on the IIS and select Autentication from the menu. Select one of the authenticion methods. In my case, select Anonymous authentication.  Select Application pool identity.

14. To test this, open SQL Server Management Studio on DBServer. Connect to the Analysis server. Put the following as the server name to connect to. Change the MyMachine to the name of the application/web server.

http://MyMachine/olap/msmdpump.dll

This solution is using anonymous authentication where the credentials are sent as clear text which will impose security threats.

Read Full Post »

How to show the data labels in a Pie Chart as Percentage (SSRS 2008R2)?

I have an MDX report with a pie chart. The data labels need to be converted as a percentage. To do that,  right click on the data labels of the pie chart to bring  up the “Series Label Properties” window. Under General tab, set the label data to #PERCENT{P0}.  The {P0} means number without 0 decimal point.

Read Full Post »