Connecting PerformancePoint 2010 site with Dashboard Designer throws error

Recently I had a problem with connecting to a PerformancePoint 2010 site using Dashboard Designer.

I created a new site in SharePoint 2010 using the "Business Intelligence Center" template and started the Dashboard Designer. When I tried to connect to the site, the Dashboard Designer failed to connect and throws the following error : "The URL is not available, does not reference a SharePoint site, or you do not have permission to connect".

When a System Administrator (working for our customer) checked the Event Logs (Administrative Tools | Event Viewer | Windows Logs | Application), he saw the following message : "Login failed for user : domain\username". After some research, it appears that the Service Account under which the PerformancePoint Service runs, did not have sufficient permissions.

The System Administrator executed the following commands in the SharePoint 2010 Management Shell to fix this problem:

$w = Get-SPWebApplication("Web application name")

$w.GrantAccessToProcessIdentity("domain\username") 

For more information about how to grant a managed account access to the content databases where PerformancePoint data will be stored, read this article on Microsoft Technet.

Sharepoint 2010 Business Intelligence Center Template Missing

One of our customers is using SharePoint 2010 with SQL Server 2008 R2. Everything in their SharePoint environment seemed to be working fine, but when we wanted to create a new "Business Intelligence Center" subsite, we didn't see the template.

 

After the customer activated the following settings in the root of their Sharepoint site, the "Business Intelligence Center" template was available for us:

• Site Settings | Site Actions | Site Collection Administration | Site Collection Features | Activate SharePoint Server Publishing Infrastructure
• Site Settings | Site Actions | Site Collection Administration | Site Collection Features | Activate PerformancePoint Services Site collection features
• Site Settings | Site Actions | Site Collection Administration | Site Collection Features | Activate PerformancePoint Services Site Features
  
  

SSIS datatypes VS SQL Server datatypes

Often it can be extremely confusing when you encounter SSIS data types.  At first glance they seem to be nothing like SQL Server data types.  That's why I wanted to write this blogpost,  below a conversion chart of SSIS data types to SQL Server data types.  This information is readily available on MSDN but it always seems difficult to find.  

SSIS Data Type	SSIS Expression	SQL Server
single-byte signed integer	(DT_I1)
two-byte signed integer	(DT_I2)	smallint
four-byte signed integer	(DT_I4)	int
eight-byte signed integer	(DT_I8)	bigint
single-byte unsigned integer	(DT_UI1)	tinyint
two-byte unsigned integer	(DT_UI2)
four-byte unsigned integer	(DT_UI4)
eight-byte unsigned integer	(DT_UI8)
float	(DT_R4)	real
double-precision float	(DT_R8)	float
string	(DT_STR, «length», «code_page»)	char, varchar
Unicode text stream	(DT_WSTR, «length»)	nchar, nvarchar, sql_variant, xml
date	(DT_DATE)	date
Boolean	(DT_BOOL)	bit
numeric	(DT_NUMERIC, «precision», «scale»)	decimal, numeric
decimal	(DT_DECIMAL, «scale»)	decimal
currency	(DT_CY)	smallmoney, money
unique identifier	(DT_GUID)	uniqueidentifier
byte stream	(DT_BYTES, «length»)	binary, varbinary, timestamp
database date	(DT_DBDATE)	date
database time	(DT_DBTIME)
database time with precision	(DT_DBTIME2, «scale»)	time(p)
database timestamp	(DT_DBTIMESTAMP)	datetime, smalldatetime
database timestamp with precision	(DT_DBTIMESTAMP2, «scale»)	datetime2
database timestamp with timezone	(DT_DBTIMESTAMPOFFSET, «scale»)	datetimeoffset(p)
file timestamp	(DT_FILETIME)
image	(DT_IMAGE)	image
text stream	(DT_TEXT, «code_page»)	text
Unicode string	(DT_NTEXT)	ntext

Hope this helps!

Addition to BIDS: BIDS Helper

The Business Intelligence Development Studio (or BIDS) is typically used for developing Microsoft BI software. Now there is room for improvement everywhere and the BIDS Helper offers this improvements.

BIDS Helper is a Visual Studio add-in which will add a number of additional features and options to Visual Studio. Most additions are in the Analysis Services and Integration Services projects and range from comfortable to extremely useful. A lot of these additions are really things that make you wonder why Microsoft didn't include it themselves...

A few examples:
- Integration Services packages can sort by name within a project
- Variables in packages for Scope another move / copy
- Within Analysis Services option to create your attribute relations graphically
- A Smart Diff option packages or differences between two cubes to detect

And there are many more.

The add-in comes from an open source project on Codeplex (from Microsoft) and is free to use. It is available for Visual Studio 2005 as well as 2008, so be sure to choose the right one.

Download BIDS Helper, install and try it yourself!

ShellRunas – Launch Programs with Different Account Credentials

ShellRunas is free utility that integrates into the Windows context menu to launch programs as a different user. It provides similar functionality to that of the command line based RunAs utility. The command-line Runas utility is handy for launching programs under different accounts, but it’s not convenient if you’re a heavy Explorer user. ShellRunas provides functionality similar to that of Runas to launch programs as a different user via a convenient shell context-menu entry.

Screenshot

At BI Architects we are using ShellRunas a lot for logging into SSMS (SQL Server Management Studio) with different credentials. If you want more information about ShellRunas or if you want to download the utility, click here

SSIS Derived Column IF(IIF)

I use this post as a short reminder for myself, because the Derived Column Transformation Editor in SSIS simply does not spell out how to accomplish this.

The following syntax is used in the Derived Column control to place an IF(IIF) statement :
(Boolean Expression ? True Part : False Part)

So, if I were checking against a Trade Type code column (trade_type_cd) to determine whether this column IS NULL or IS NOT NULL, I could simply write up something like this:
ISNULL(trade_type_cd) ? "<Unknown>" : trade_type_cd

Addition to BIDS: Table Difference component

One question arising very often in the Data Warehouse programming is "What are the differences between two tables?" You receive several million customers and should decide what changes need to be done. The standard SSIS toolkit provides SCD component for handling this process, but it is of no practical use when you have to handle more than a couple of thousand records. The Table Difference component doesn't have this limitation.

The component has two inputs: one for "old" and one for "new data". The old and new rows are compared and the result is sent to different outputs:

• Unchanged rows (same in both old and new)
• Deleted rows (appear in old but not in new)
• New rows (appear in new but not in old)
• Updated rows (appear in both old and new, but there is a change)

The diagram below illustrates the component functionality better than thousand words:

Occasionally you may have a requirement to perform different updates, for different columns. The component includes option to tag each input column with identifier. Then for each unique identifier, a separate update output is created and maintained. In this way you can tag with "10" the historical attributes and with "20" the other columns and decide what kind of operation to carry on with the different updates.

The component inputs MUST be sorted and have a collection of fields (keys) that let the component decide when two rows from the inputs represent the same row. This is easily accomplished in SQL with a simple “order by” and a convenient index. Usually slowly changing dimensions maintain an index by the business key, so the sorting requirement is easily accomplished and do not represent a problem.

Click here for more information about the Table Difference component.

Business Intelligence extension for Clarizen - Presentation

Two weeks ago i posted about the Clarizen's online project management software. In the presentation below our Business Intelligence extension for Clarizen is described.

For more information about the extension for Clarizen please contact our sales manager Fred Booms (f.booms@bi-architects.nl)


Hermann-Otto Israël
BI Architects project manager

BI solution additional to use of Clarizen's online project mgt software

BI Architects develops Business Intelligence solutions for its customers. Furthermore it has developed BI solutions for managing its own business processes, such as the solution that helps the project managers monitor, track and report project progress (including KPI's and earned values), the finance department enable swift and correct detailed invoicing and the management team gaining direct insight in the performance/profitability of projects.


BI Architects has chosen the Clarizen's online project management software for the registration of customers, projects, milestones, tasks and writing time. The Clarizen tool takes care of the complete project administration/resource management/and much plus it gives basic insight in performance via reporting. What it lacks is giving a more detailed insight in the performance of project phases over projects (for comparing project performances and quality improvement), earned values (current status and expected outcome) of projects and more figures needed for project management that are available without starting reports, setting parameters, running the report and opening the result in MS Excel.


What we have done is developing a standard Microsoft SQL Server Integrated Services/SQL Server Analysis Services (SSIS/SSAS) solution that adds what we miss in the Project Management tooling. SSIS is used for the extraction, transformation and loading of data from the Clarizen database into a staging environment (dimension tables), while SSAS delivers actionable insights (cubes and its dimensions) and made available via a pivot table in Microsoft Excel.


All information is now available via MS Excel and on the fly customizable serving several purposes within the BI Architects (project) organization.


For more information on our project management BI solution please contact me via H.O.Israel@bi-architects.nl . For more information on Clarizen's online project management software go to Clarizen.com .


Hermann-Otto Israel
BI Architects project manager

Bug regarding datasources in BIDS

Everytime I use shared datasources in a SSIS package and configure the connectionstring with a package configuration I've noticed that my package configuration connectionstring is being overwritten by the connectionstring that is present in the shared datasource (the synchronize connection strings popup). This can be very confusing.

I've done some research on the internet and discovered that this behaviour is a bug in BIDS, and only in BIDS. When you deploy your SSIS packages to a server the package configuration connectionstring will overrule the connectionstring in the shared datasource.

1 Shared datasources

2 Datasources within SSIS package

Kimball Design Tip #139 Much Ado About Nothing

Yesterday I've read Design Tip #139 from the Kimball Group. In this tip Margy Ross is talking about renewed rumblings in the DW/BI industry about the Kimball versus Inmon approaches. She refers to a white paper, written in January 2008 by the Kimball Group, entitled “Facts and Fables about Dimensional Modeling”. In this white paper, the Kimball Group tries to tackle misunderstandings about dimensional modeling that appear in DW/BI industry publications, training, and marketing materials.

Margy Ross also refers to an article she wrote almost eight years ago for Intelligent Enterprise magazine (since absorbed into InformationWeek) entitled “Differences of Opinion”. At the time, she tried to fairly contrast the Kimball bus architecture approach versus the Corporate Information Factory. She had several clients who were struggling to make a decision between the two dominant schools of thought, so she attempted to summarize the similarities and differences.

If you're are interested in this and other Kimball Design Tips you can read them over here. They are worth reading ;-)

"No cubes can be found" message from SSRS 2008 R2

Today I had a problem that I have already came across some time ago. In Visual Studio 2008 (BIDS) I created a SQL Server Reporting Services (SSRS 2008 R2) report and connected this report to an Analysis Services Olap Cube (SSAS 2008 R2). Then I created a new dataset and when I started the Query Designer, the following error message appears: "No cubes can be found".









After looking at the cube properties, I noticed that the "Visible" property of the cube was set to "False". This property should be set to "True" to enable connectivity in client applications.



Hopefully I can help someone with this!

Projects and customer involvement

How to make a success of your project? When looking for the answer on this question and using the keywords “project management success factors” in Google’s search engine I have found a lot solutions. Most of the found blogs, articles and other descriptions were written by solution providers (IT infrastructure and software) and aimed on using a specific project management method, for instance Prince2, to ensure project success.

But does this project success also result in a happy customer?

INTRODUCTION

A few weeks ago I have read a story on managing projects from the customer side. It was a real eye-opener. Why do we do projects for our customers? Just to make a lot of money by serving our customers solutions without considering if it is value for the business or is it because we do want to implement a solution that delivers added value to business. Is it quantity (lots of worked hours) or quality (a happy customer).

PUT THE CUSTOMER FIRST

It is clear that we have to deliver a solution to fulfill the customer’s need, but we have to ensure and keep ensuring that we have customer involvement in our projects. Research by the Standish group has shown that the customer is most influential on project success. The customer (executive, sponsor and user) is for 34,5% the success factor for successful projects, while the project team of project management are less influential. 

HOW TO DO THAT?

The greatest challenge in making projects successful is answering the question how to put the customer first and involved in the realization of the product or solution.

HOW DO WE DO THAT?

To enable/ensure customer involvement our specialists work a few days on the Floor. While working among the end users or business representatives during design, development or testing/acceptance communication lines are kept short.

Our BI projects cannot be successful if we do not have an involved customer. BI projects are something you have to do with your customers, because this something you do for them. For us this is the key success factor in our projects and that is something we would like to share.

In the near future we will subject more of these Project Management and Customer Quality topics in this blog.

Hermann-Otto Israël

BI Architects specialist

Welcome to our blog!

Welcome! Before we describe what you can get from reading our new blog, we want to introduce ourselves to anyone new to this space. BI Architects is a specialist in Business Intelligence Solutions and is located in the Netherlands. Our mission is to offer customized Business Intelligence and System Integration, through honest advice and implementation projects where quality, commitment, up to date knowledge and understanding in and optimizing of the processes of our customers stands central.

What can you find at the BI Architects blog? Our blogging team will cover a wide-range of Business Intelligence topics, including Dimensional Modeling, System Integration, Project Management, News, Information on trends, and more. Check back on a regular basis to read about:

• Project Management approaches in Business Intelligence projects
• Business Intelligence with online Project Management software from Clarizen
• Business Intelligence in Trading & Risk Management with Commodity XL (CXL) from Triple Point
• Sharepoint 2010 dashboard solutions
• Innovations in Business Intelligence software

Our blogging team will also describe practical solutions for challenges we have with tools like:

• Microsoft Visual Studio
• SQL Server
• SQL Server Integration Services (SSIS)
• SQL Server Analysis Services (SSAS)
• SQL Server Reporting Services (SSRS)
• Sharepoint 2010
• etc.

So, enjoy reading, and don’t forget to comment. We want to hear from you.

The BI Architects Blogging Team