Archive for July, 2011
Recently an American citizen was turned away from a Canadian border crossing for having a 20 year old conviction related to a high school prank. An immigration lawyer commented that he was not surprised as “the databases are getting deeper and deeper”. The Globe and Mail complained that this case and other similar ones fail to accomplish the goal of keeping Canada safe from real criminals and terrorists. While this decision appears frivolous or even silly, I think it highlights the differences between data, information and business intelligence.
The Information Continuum
Data – A Fact or a piece of information, or a series thereof
Information – Knowledge discerned from data
Business Intelligence – Information Management pertaining to an organization’s policy or decision-making, particularly when tied to strategic or operational objectives
So when hazy, ill-formed or poorly constructed objectives are tied to simple data access, bad business intelligence can be the result. Let’s consider two strategic objectives:
“Keep Canada safe from real criminals and terrorists”(as suggested by the Globe and Mail editorial)
- This leads to questions like
- What is a real criminal or terrorist?
- How can they be identified?
- This might lead to
- Information sharing with FBI and Interpol
- Data sets might include
- FBI’s most wanted list
- Interpol’s wanted list
In this case, the Information Continuum is approaching the Business Intelligence level.
“Keep convicted criminals out of Canada”(the apparent objective applied in this case)
- This is a blanket statement that will catch everyone ever convicted of any crime (assuming the data is available), regardless of circumstances
- It invites no further questions
- You will have false positives (prior convictions with no future criminal intent) and false negatives (active criminals with no prior convictions)
- In this case, the Information Continuum does not move beyond the data level
Simple data access will not make better business intelligence – if your people don’t have the right resources and direction, it could make it worse. Your analysts should be able to distinguish between real information and noise, and they should always be striving towards your organization’s clear and thoughtful objectives.
SQL Server Analysis Services is a popular OLAP product included with Microsoft SQL Server. Especially since SQL Server 2005 this product has been quite powerful and fairly easy to develop with. SQL Server provides the Business Intelligence Development Studio (BIDS), a Visual Studio –like product to aid the development of Analysis Services cubes.
For browsing and reporting on a cube, however, choices have been more limited. Excel provides a good choice, especially since Excel 2007, which contains enhancements that make creating cross-tab reports easier than previous versions.
If your users are committed to Cognos PowerPlay, you can use this tool as well. Setting up a MS Analysis Services cube for browsing with PowerPlay is a little more involved than a regular Cognos cube, but is still quite easy to do.
The executeable is ppconnct.exe.
This tool is used to create a binary “pointer file” with a .MDC extension. This file, once created, will behave like a PowerPlay OLAP Cube, but the underlying cube will actually be (in this case) a Microsoft Analysis Services cube.
Start PowerPlay Connect, select File… New to create a new MDC file. For the database type, select MS SSOS (ODBO):
You have a couple of choices next. If you know the server name for your instance SQL Server Analysis Services you can enter it in the next line, under Server:[Port]. In this case I can enter “localhost”, as I am serving the cube from my local machine.
Alternatively, I can select the … button beside Database, and I will be presented with the Chose a Remote Cube dialog box. In this case I then select Microsoft SQL Server OLAP Server at the bottom, and then select a connection I have already created previously using the tool. In this case the connection is called local. I’m then presented with a list of databases available on the connection “local”.
I can then open SSAS_Adventure_Works and the cube that exists in this particular database. A database might have many cubes available in it.
Alternatively I could create a new connection, by clicking on Connections… and then clicking Add. I enter the name I want to give the connection, and then the name of the server, and select Microsoft SQL Server OLAP Server and MSOLAP as the provider:
Since I selected the cube SSAS_Adventure_Works, we see this in the details of the connection string:
I can now click File… Save and save this as an .MDC file:
The file appears as a normal MDC cube, but is really just a pointer file to the SSAS database server:
Using PowerPlay, I can now open the MDC file as if it were an ordinary cube. I can navigate it generally in the same way I would navigate a Cognos cube, althought some things such as Measure Groups that are part of the Microsoft approach to OLAP do not behave exactly the same way. Meaures appear as a single list, much as they do in Cognos cubes.
PowerPlay Connect MDC files can be put on the network, or shared as any other file, and will work as long as the user has access to the underlying Microsoft database.