Topic: ‘SQL’

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.

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 Excel\WSS or Excel\MOSS, 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

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

It’s often helpful to have an idea of the volume of transactions in a department. For example, it’s nice to know how many AP Vouchers and Checks get processed each week to understand the load on the AP department. This also provides a pretty objective benchmark for AP employees. To make this happen, we start with a SQL View that agregates the Open and Historical AP transactions by week.

This code uses a Friday to Thursday week with the expectation that checks are cut on Friday so the typical AP entry cutoff is end of day Thursday. If your week doesn’t work like that, the code is easy to change. After that we can query the view using a Smartlist, Excel, SQL Reporting Services or Crystal Reports. My preference has always been Excel because this data really lends itself to a graph.

The code to build the view looks like this:

Create View Metrics_APVoucherCountWeek as

–Get Open AP
Select
–Get end of week
Case
when datename(weekday,docdate) = ‘Friday’ then Docdate
when datename(weekday,docdate) = ‘Saturday’ then dateadd(Day,6,docdate)
when datename(weekday,docdate) = ‘Sunday’ then dateadd(Day,5,docdate)
when datename(weekday,docdate) = ‘Monday’ then dateadd(Day,4,docdate)
when datename(weekday,docdate) = ‘Tuesday’ then dateadd(Day,3,docdate)
when datename(weekday,docdate) = ‘Wednesday’ then dateadd(Day,2,docdate)
when datename(weekday,docdate) = ‘Thursday’ then dateadd(Day,1,docdate)
else ’01/01/1900′ end as WeekEndDate, DocDate, VchrNmbr, DocType, Bchsourc, Mdfusrid
from pm20000

–Get History AP
Union

Select
Case
when datename(weekday,docdate) = ‘Friday’ then Docdate
when datename(weekday,docdate) = ‘Saturday’ then dateadd(Day,6,docdate)
when datename(weekday,docdate) = ‘Sunday’ then dateadd(Day,5,docdate)
when datename(weekday,docdate) = ‘Monday’ then dateadd(Day,4,docdate)
when datename(weekday,docdate) = ‘Tuesday’ then dateadd(Day,3,docdate)
when datename(weekday,docdate) = ‘Wednesday’ then dateadd(Day,2,docdate)
when datename(weekday,docdate) = ‘Thursday’ then dateadd(Day,1,docdate)
else ’01/01/1900′ end as WeekEndDate, DocDate, VchrNmbr, DocType, Bchsourc, Mdfusrid
from pm30200

You only have to run the view code once to build the view named Metrics_APVoucherCountWeek.

From there, a query on the count of AP Vouchers by user looks like this:

–SQL Query for APVoucherCount
–Use Weekenddate in where clause to limit range
–This is ALL AP Voucher transactions.
–Uses Metrics_APVoucherCountWeek View

Select WeekendDate, Mdfusrid as APUser, Count(VCHRNMBR) as VoucherCount
from Metrics_APVoucherCountWeek
–Exclude Payments
Where Doctype<>6
group by WeekendDate, mdfusrid

And the output looks like this:

AP Metrics Ouput Example

You can see that it would be pretty easy to graph the voucher count per user per week. I have prebuilt AP queries available for AP transactions, AP payments and AP Transactions excluding PO related items. We’ve also got PO and Fixed Asset metrics available, all you have to do is ask.

This is my shameless inducement to get you to leave a comment. If you want more queries for the metrics, leave a comment on the blog with an email and I’ll get them to you. No, we’re not going to spam you to death if you ask for the metrics. We just want to know that people are paying attention.