Business Intelligence And Just Plain Intelligence

Sequence or Identity ?

2010-11-11T09:52:00.000-08:00

Traditionally, when you wanted to create a key field that has Auto Numbering, you would have used an "Identity" in SQL Server and "Sequence" in Oracle world of RDBMS.
Well, it looks like that's going to change. In my opinion, Identity was simpler to use, it wasn't a separate database object, it was simply a column property. Sequence on the other hand is a database object in Oracle RDBMS. it allows much more complex manipulation in order to manufacture a key. SQL Server database developers have been asking for sequences for a long time.
If you've downloaded SQL Server 11 (Code Name "Denali") CTP 1 who was released on November 9th, and checked out the new Management Studio, you might have noticed a new "folder" in the Database tree, named "Sequences". There's also a new DDL command called "CREATE SEQUENCE" and other mechanics associated with this function. This is quite similar to the Oracle implementation. Check it out !

ESRI Announce MapIt

2009-07-15T22:49:00.000-07:00

A Couple of weeks ago I had the pleasure of participating in a training of a new product by ESRI : MapIt. MapIt is a tool that turn your business geospatial data into a wide range of graphical representations. Whether it's simply "dots on a map" (as the GIS folks like to call it) or shapes of all kinds (cities, regions and other slices).
MapIt is based on Silverlight 2.0 or WPF and is using the geospatial data types introduced in SQL Server 2008. It has .NET controls and more importantly - a MOSS plug-in. The Plug-in allows integrations of maps generated by MapIt with a sharepoint page. I recommend visiting MapIt product page. Take a look at some of the dashboard demos.

BI Stack Install Order

2009-07-14T08:56:00.000-07:00

After Testing multiple scenarios, this is what I believe is the best, smoothest way to install the entire (latest release to date) BI Stack including :

Windows Server 2008 (Without Hyper-V)
SQL Server 2008
MOSS 2007
PPS 2007
PAS 6.3

No SQL Server 2005 is needed. both MOSS and PerformancePoint Server will be using SQL Server 2008 for repository purposes.
For testing purposes – 32 bit enterprise editions have been used across the board. I know we all sometime use X64 but really – Enterprise Edition of Windows Server 2008 supports up to 64GB of ram which is usually enough.

So here it is:
---------------

Install windows Server 2008 Enterprise without Hyper-V x86
Change Server Name
Install Virtual Machine Additions
Turn on Windows update
Run windows updates
Add Web Server Role and IIS 6.00 compatibility mode.
Turn IE Security Off.
(No need to install .NET 2.0 since .NET 3.5 includes it)
Turn windows Firewall off.
Activate Windows 2008
Install SQL Server 2008 Enterprise x86
Install SQL Server 2008 SP1
Install ASP.NET 2.0 Ajax Extentions 1.0
Create a sql admin user (local admin, sa)
Configure Reporting Services. Backup Encryption Keys.
Install MOSS 2007 Enterprise. Advanced. Create New Farm, use sqladmin to connect.
Configure MOSS (SSP, MOSS Services, Web App)
Make sure http://localhost works (moss)
Install Excel 2007
Update office components (windows update).
Install Silverlight (windows update).
Install PPS Monitoring Server
Install PPS Monitoring Server SP2
Install 4 distributables of SQL Server 2005 SP2 CU9:
SQLNCLI
SQLServer2005_ADOMD
SQLServer2005_ASOLEDB9
SQLServer2005_XMO

If you need excel support - also install:
SQLServer2005_ASOLEDB9.msi
ADOMD.msi

Configure Monitoring Server
Install and configure Planning Server - If needed
Install PAS (You will have to read this and you'll need to take care of the Port issue if you are using Port 80 for MOSS.)

If you need to install any add-ons or other products, you can do it at this point.
Good luck ! I will write up a more detailed document, including screen shots and maybe some videos, in the next few weeks.

Report Builder 2.0

2009-07-02T11:26:00.000-07:00

"So do I need to use Visual Studio if I want to create a new report ?" is a question I get from users quite often when I show them how to develop new SSRS reports. The question is often accompanied by a worried look or even a slight disappointment.
While SSRS is a tool that is suitable for the power user to design and publish reports, Visual Studio, in most cases, is not.
Report Builder 2.0 allows RDL development in a well known environment - Microsoft Office. It is much friendlier, has significantly simpler and less cluttered user interface and most of all - any user who ever used Word or Excel will be immediately familiar with it.
In addition - it has the newer Microsoft Office Graph component and best of all - it's free !

BI Failures

2009-06-24T03:54:00.000-07:00

We often hear about the success of business intelligence in various organizations. If you are a consultant, I am sure you have heard or might even been involved in failed BI projects. I recently read an excellent blog post by Peter Thomas that tries to explore the main reasons for failed BI projects. I believe it is equally important to read about failures and not just success stories. Knowing which paths to avoid is just as important as knowing which paths to follow.

Where's AdventureWorks ?

2009-05-12T12:16:00.000-07:00

If you were looking for the AdventureWorks database when you were installing SQL Server 2008, You've found out that Microsoft's sample database from SQL Server 2005 is no longer a part of SQL Server 2008 Install bits. Instead, it's available on the open source site - Codeplex.

Adventureworks is being used throughout microsoft documentation, 99% of microsoft's sample code (SQL, MDX, DMX, XMLA) is using the Adventureworks or AdventureworksDW database.

Both Adventureworks and AdventureworksDW databases are available Here. Take a look at the Releases box on the top right side in order to make sure you are downloading the latest (stable or alpha/beta, choose your poison) release.

PerformancePoint Prerequisits and how to bypass them

2009-05-11T15:21:00.000-07:00

Last week I tried to install PPS 2007 on a VM that for some reason had the Domain Controller role installed. The installer's pre-requisites check generated an error and I couldn't go on with the install.

While Microsoft does not recommend installing PPS on a domain controller, it is still possible to install it for demo purposes. there's a parameter called 'SKIPREQCHECK' and you just need to set it to 1 in order to skip the requisites check. I would never do this on a production or even a development machine obviously, but if you have a VM that is a DC, you can use the following command to install PPS :

PerformancePoint Planning Server 2007
MSIEXEC /i PPLSrv.msi SKIPREQCHECK=1

PerformancePoint Monitoring Server 2007
MSIEXEC /i PSCSrv.msi SKIPREQCHECK=1

These commands will still show the error but they will allow you to continue on with the installation and ignore it.

4.2 Billion Tuples - That's all you get !

2009-04-22T23:38:00.001-07:00

"The expression contains a function that cannot operate on a set with more than 4,294,967,296 tuples."

If you are running a complex MDX query in Analysis Services 2005, You will see this message sooner or later. I've heard that this is no longer an issue in Analysis Services 2008 but I haven't got the chance to check it yet.

Why does it happen ?

When your MDX is filtering against dimension hierarchies with a large amount of members, it will scan each and every combination of members possible.
Let's assume that :
DimGeo has 1000 members
DimTime has 1000 members
DimStore has 1000 members

and your mdx is filtering on all of these members (whether it's an actual FILTER or you are using them in the WHERE clause). it will scan through 1000 x 1000 x 1000 members or actually 1,000,000,000 members. Add another dimension or two and you can see how easy it is to get to 4.2 Billion members.

How do we resolve this ?

The good news are that there are a few solutions to this problem. Let's start from the easiest solution and if that doesn't work for you, try the next one.

1. Use NONEMPTY - Using NONEMPTY will reduce the number of tuples that are being scanned by eliminating all the empty ones.

2. Use SCOPE - this is not always possible but if you SCOPE out your set, you can reduce the amount of the tuples that need to be scanned.

3. This is the official solution from Microsoft. By splitting your sets and using + to query them, it solves the issue in a clever way.

4. If you tried all these solutions and you still get the error, I'd recommend that you'll look into your dimensions hierarchies and try and implement some additional hierarchy levels. for example : If my DimGeo dimension hierarchy looks like this :

Country > State > City

try and add a level like 'County' and use that in your query.

If your dimension has quantifiable ranges - try and create levels with larger ranges.

The idea is of course to reduce the number of members in the dimension hierarchies that the query needs to scan.

Good luck !

MDX Performance Tuning Tools

2009-04-22T23:22:00.000-07:00

Many people, both experienced BI developers and people who just started with MDX were asking about MDX performance issues. I'd like to mention a couple of tools who can help with that. First and foremost is MDX Studio by Mosha Pasumansky, one of the original Analysis Services developers. MDX Studio comes in two flavors - a desktop version and an online version.
Both versions will allow you to analyze MDX statements and even format it so it looks really nice and organized (I've been complemented before on how tidy my MDX is after I used MDX Studio's formatting :) ).

The second tool I'd like to mention is MDX Script Performance Analyser. It's hosted on CodePlex and it provides a graphical view on how your MDX is performing every step of the way.

Both tools are free.

Latest BI Stack VPC

2009-04-22T11:21:00.000-07:00

For anyone who's looking for the latest Microsoft BI Stack VPC (as of today), you can download it here, it includes a bunch of demos so if you are looking to build one of your own, this will be a nice resource.

Part 1
Part 2
Part 3
Part 4
Part 5
Part 6
Part 7