Topic: ‘Reporting’

At iSight last week, I presented a high speed session titled 50 Dynamics GP Tips in 50 Minutes. Attendees got the presentation and notes on a USB drive but in case you’ve lost your drive or just need to point a colleague to the presentation, you can get the presentation with related notes here.

As promised at iSight, our customer conference from last week, here is the document comparing Microsoft Office Sharepoint Server with Performance Point for Business Intelligence needs. There is some overlap between the products depending on what you want to do and this document highlights where they overlap and where they differ.

Phew…3 days of hardcore BI geekdom come to a close.  The action for today was very representative of the first two days:

Keynotes

Kurt DelBene did a nice job, along with Kristina Kerr, Senor Product Mgr.  Kurt gave a pretty good, but very stock analystmarketingcan’t-tell-you-anything preso on the future of the BI stack and integration of the office solutions to same.  If you think self-service via Excel and greater integration with MOSS, you think right.  However, Kurt also commented that we are only "halfway through Wave 14", so you won’t see anything for at least another year.

Kristina did a very catchy demo of Microsoft Surface as a BI UI by showing water usage data for the downtown Seattle core, mapped against Visual Earth, against temperature and time.  Very cool, very 3-D.  Not a single customer I work with will ever buy it.  K:  Come up with a self service app that lets me order and pay in a restaurant without talking to a waiter, and I think you may have a winner.

From an overall perspective, the keynotes (with the exception of Ben Stein) were the definite weak point of the conference.  Little solid info, lots of marketing stuff. 

Sessions

The only one I did today was Avoiding Common Mistakes in Analysis Services:  Very nicely done by Craig Utley.  Between his discussion around Attribute Hierarchies and Attribute Relationships, he solved 4 separate problems I have currently with customer deployments.  Also, he has a great book called Business Intelligence with Microsoft Office Performance Point Server 2007. 

Vendor Pavilion

Today was the first day I spent time in the Vendor Pavilion and spent the entire time with Robert Sterling, VP of Partner Alliances, at Strategy Companion.  SC is a great vendor of BI plug in solutions for Dynamics CRM and is one of the few that produce a truly powerful and highly integrated solution. 

Hands on Lab

The balance of the day was spent in the HOL working on Performance Point Dashboards, Excel ServicesMOSS, and Dynamics AX integration with SSAS.  I think I set a conference record of almost 4 hours in front of the training machines, but was very impressed by the content.

Overall Review

In general, this is a good conference and worth the money, provided you look closely at the conference agenda BEFORE you sign up.  If you’re a client of IBIS and customer of Microsoft, send your CIO or high level tech team combined with at least one SQL Server DBA.  If you are a partner, send an architect and at least on business application (ERP or CRM) consultant.  This year, only I went.  Next year, we’ll probably want to take at least one more person.

Avoid the keynotes.  Split the sessions up to cover maximum ground.  Spend lots of time in the HOL. 

Thanks for reading, folks.

Today’s keynotes varied between thought-provoking and mind numbing.  Ben Stein, noted comic economist, opened the day with a cogent overview of the current financial crisis and how it came about.  He followed this with a listing of the country’s problems, a touching personal story and then wrapped it all into a simplistic solution:  help make good families.  No ideas on how, but certainly it was entertaining.

The second keynote was a panel of BI guys from Accenture, Dell, HP, Profit Base and Hitachi being asked "What will BI look like in 2020?".  Kill me.  The answers were about what you’d expect – "blah, blah, blah…and that’s why Dell/HP/Accenture/whatever is a particularly good solution for blah, blah, blah".  After the Accenture guy coined the term Information Resource Planning ("IRP"), I bailed to prevent any inadvertent vomiting from hitting the guy seated in front of me.  Really, does this industry need one more fill-in-the-blank-resource-planning acronym?

Content today was, in a word, rock solid.  Here’s what I did:

Data Mining with Excel:  Okay, this sounds a dry as can be…but WOW!  The presenter covered the use of Excel’s Data Mining add in to do predictive analysis on example data sets.  Where this should be of interest to all of IBIS’s clients is the power to mine your existing ERP and CRM data (even better with CRM, but if you have Analysis Cubes for Excel installed with GP, it would work great) with an easy to use predictive analysis tool the better to identify hidden relationships and trends in customer buying behavior. When coupled with basket analysis ("when x bought y, what else did they buy") and category analysis ("what do people that upgrade to X have in common"), you really, really get fast information you can leverage into tighter, more focused marketing behavior.  Best of all, you own this stuff already with MS Office. 

Strategic Planning and Scorecards for PPS:  This entire conference is really about three things:  SQL 2008, Excel and PPS.  PPS really is showing well in two areas:  consolidating and controlling Excel based planning and budgeting sheets, and fast dashboard construction of analysis data in Analysis Services, Excel, SQL Tables, etc.  This session was a little dry, but great to see the entire life cycle from plan to report.

Leveraging Your Business Intelligence Applications in Sharepoint:  In case you think BI is only a huge ticket item, think again.  Using basic ExcelWSS or ExcelMOSS, you can easily create awesome team template sites that have built in KPI and Metric functionality from a data source as easily managed as a Sharepoint-mounted Excel workbook.

Hands on Labs:  MS did an amazing job on these.  I spent about 3 hours in the lab working on Excel dashboards, Excel Services and Performance Point Dashboard construction.  From them, I take away the following points:

1. Everyone running GP and CRM needs to be using Analysis Services and Excel.  Period.  Its the best data mining and dashboarding tool every put into the hands of the end user.  Ever.  Period.  Really.
2. Excel Services and MOSS:  Done right, this solves all the problem related to mailing around Excel worksheets or storing them on a network.  You can centrally store them, see them in a web page, pull out only individual elements (like a single chart) for display, bury segments into a Sharepoint KPI, and the list goes on.    Small clients can buy this off the Dynamics price list fairly inexpensively and if you are BRL AM you already own a lot of it. 
3. PPS Dashboards:  This is where you should start with PPS, if you laid in the foundation of Analysis Services.  However, you can also use Excel, SQL Tables, Sharepoint Lists and any ODBC datasource as a dashboard datasource so its not a hard requirement.

Tomorrow is going to be more HOL and sessions and I’ll do a wrap post.  For those of you coming to iSight, I look forward to talking to you about all this stuff in person.

Dwight

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