Topic: ‘Business Intelligence’

Okay, once you get past the fact that the opening keynote by Stephen Elop was preceded by 80′s cover tunes played (loudly and early and before I had enough caffeine) by "The Dudley Manlove Quartet" (who had 5 members), the keynotes were definitely worth the wait.

The key (no pun intended) points:

1. MS wants to "democratize" BI. 

In essence, put easy to use tools, built in Excel, into the hands of the end user.  Then allow them to use those tools to build stuff that the rest of the organization can share through MOSS.  This "self service" BI is nicknamed Project Gemini and will start appearing in staged feature pack releases (I think that’s what they said). 

The most astonishing thing I saw was one of the program managers, working from a $1,000 workstation using Excel 2007 with the Gemini plug-in, pull in a data set of 20,000,000 rows (that’s right, 20 million rows), and dynamically build a pivot table whose dimensional relationships were created on the fly, upload the table to MOSS and, while doing so, dynamically create an Analysis Cube that everyone else could share.  And he did in in 20 seconds.  Almost any Excel knowledgeable accountant I know could do what he did if they had this loaded.

2.  SQL 2008 is totally, totally cool. 

You will now hear it here first:  I am leaving my wife for SQL 2008.  MS just set a data loading record of 30 minutes for 1TB of data. This is a world record for a wintel platform and beats Oracle by 15 minutes.  Combine that with its ability to scale massively up (they were doing demos with 150TB of data where reports were kicking out in 4 or 5 seconds) and still meet the needs of the SMB space, and SQL2008 is definitely a hotty.

The rest of the day was a plethora of preso’s on KPI’s and Dashboards in MOSS and Performance Point, combined with great session on using Excel and Excel Services to deploy good, solid BI solutions inexpensively (both on initial investment and TCO).

It appears IBIS is leading the pack in BI for the SMB space with our fixed fee offerings around Analysis Services and Excel, and especially in our work with deploying dashboards deployed in MOSS via Excel Services.

One last note:  MS has to learn not to let the marketing department try to make humorous Powerpoint preso’s for technical staff.  MS is a lot of things, but as a humorist, they really, really suck. 

More tomorrow.  If you want intra-day updates, check out my twitter feed at http://twitter.com/DwightSpecht.

Dwight

I’ve been a reasonably frequent publisher of blog entries at Mark Polino’s site related to Analysis Cubes, but will also start posting here as well.

We just finished another one of our Fixed Fee Packaged Offerings for Analysis Cubes for Excel.  In these very competitively priced (and quite valuable) offerings, we do a complete deployment of Analysis Cubes including installation, training, custom modifications to the cubes and custom report writing.  In this last one, we uncovered (and then fixed) a slew of issues.  I’ll list some of the bigger ones here:

The Data Source View is Improperly Built – Part 1

We noticed the processing time on the cubes was WAAAAY too long.  In fact, it almost crashed the server.  We drilled down on the problem by looking at the query that was being built to populate the Total Expenses dimension and noticed it was returning about 88 million rows.  Further examination showed that the offending item was the join between the Total Expense table and the Fiscal Time table.  The join was based solely on the company ID field in both tables.   The problem with this is that the Total Expense table contains one record per AP voucher per company.  The Fiscal Time table contains one record per company per day since the cut off date selected in the install.  So, if you’ve been operating for 4,380 days and you have 12,000 AP vouchers in that company, you get 144,000,000 records returned (4,380 * 12,000) just for that one company.  Multiply by the seven companies with which we were working and you can see where the problem really comes in.  The correct join should be included the Document Date from the Total Expenses table and the Full Date from the Fiscal Time table.

The Data Source View is Improperly Built – Part 2

In resolving the above, we noticed that many of the tables had similar, incomplete joins.  As we started looking further, we then found then often the correct join (like Company ID and Document Date to Company Id and Full Date) was spread across two different connections.  The problem with this is Analysis Services only uses on (the first) connection in the DSV to build its query against the warehouse.  So, defining compound keys in separate connections is about the same as defining them incorrectly in the first place.

Fixing the above two issues by fixing the DSV reduced the processing time from 3+hours and crashing the server to 39 minutes.

Packages and Encryption

We installed ACE under a domain account with Windows Admin  and a bunch of other privileges (per the manual).  We could run the SSIS packages.  We would create and run the job.  We could schedule the job and have it run.  However, the second we logged out of the server console, the job would fail.

That’s right, the job ran only if the install user was logged in to the SQL Server console.  So, I could login to the console and have nothing open.  Then, I could start SQLMgtStudio from another workstation logged in as another user and start the job.  Everything went fine.  But if I tried to run the job WITHOUT the install user being logged into the console, it failed.  Turns out the encryption level (Encrypt Sensitive Data with User Key) for the SSIS packages is seriously buggy and pretty much prevents you from doing anything you really need to do.  Even setting up proxy credentials didn’t work, so we had to have Tech Support decrypt the packages for us.

Bad Data 

Other blog entries I’ve done addressed the Retained Earnings issues with ACE, but we found a new one.  The Customers Over Credit Limit Dimension was not returning the right data.  When we looked at the decrypted package, we found out the the sign was wrong:  Customer Balance > Credit Limit was being set to No rather than Yes.  That was a pretty quick change to the package (although you would easily do the same by using an update script on the CustomerMaster table in the warehouse).

The above applies to GP 9.0, but we found many of these same issues in GP 10.

We now do all these fixes as part of our standard deployment, but though the above might save you some time.

Dwight