Topic: ‘SQL’

Mark Polino came up with a handy Excel spreadsheet for referencing SQL tables in Dynamics GP 10.  To download your own copy click tables in Dynamics GP 10. 

Thank You!  Mark for providing this spreadsheet.  I have already used it several times.

This post was submitted by Christina Belding who is a Managing Consultant here at I.B.I.S., Inc.   She provides a real-life scenario of what many of us come across out in the field and a very useful script to locate specific columns in a database. 

Christina’s story…I had to find every table where a specific field was being stored quickly.  My client needed to make a decision on whether or not they should change some core behavior or locate another field to be used, however, we weren’t sure how widespread our field truly was.  Did it have a history table associated with it?  Were there a lot of transactional tables?  Hmmmm….

We ran the following script (change the CALLNBR field to whatever the physical name of the field is you’re looking for) and voila!  We determined in GP10, the CALLNBR (call number field in field service) was being referenced in 54 tables.

select  

       on sysobjects.id = syscolumns.id

where syscolumns.name like ‘CALLNBR’ 

order by 1

from syscolumns

You can then go into the resource descriptions within GP and get more information on the specific tables listed.

On Friday, January 23, Microsoft announced significant changes to its business intelligence strategy.  You can find the detail behind this at http://www.microsoft.com/presspass/features/2009/jan09/01-27KurtDelbeneQA.mspx as well as a number of great blog entries.

To help our customers and partners, I want to summarize those changes then talk about how they will impact you.

Here are the announced changes:

1. Performance Point Monitoring and Analytics will be downgraded into MOSS Enterprise CALS:  This is the part of PPS that handled all dashboarding and KPI’s.  It is the very visual, very sexy part of the product that really helped it sell and also the part that worked the best.  If you own MOSS Enterprise, you now get M&A for free.
2. Performance Point SP3 Release in July/Planning Discontinued:  Planning is the portion of PPS that is the hardest to implement and was intended to replace Forecaster and Enterprise Reporting.  MS will release a performance update service pack (SP3) and then will stop development of new features.  As of July, when SP3 releases, PPS will cease to exist as a stand alone product.
3. Management Reporter To Be Managed by Dynamics:  Management reporter, the FRx replacement reporting tool in PPS, will be given to the Dynamics team to own and enhance.
4. Enterprise Reporting is Back on Support: All previous announcement of Enterprise Reporting’s support discontinuation (planned for Jan 2010) are revoked and its back on the price list.
5. Proclarity Desktop:  Gone.  Not much more comment other than that.

Why did they decide to do this?  Answering solely for myself:

1. The planning module was definitely a v1 release and required significant technical and programming expertise to implement.  However, the Dynamics channel already had good forecasting tools (Forecaster, ER) and wasn’t willing/able to adopt PPS quickly solely for that.  The channel that was adopting it (infrastructure and MOSS partners) weren’t very adept at financial planning and forecasting, so an automatic channel disconnect was setting in.
2. PPS was expensive.  In this environment, it wasn’t going to get good play.  In addition, customers with Enterprise Agreements were feeling "nickled and dimed" over the additional cost in the EA for PPS.
3. MS truly believes in "Democratizing BI", especially in the role Excel and SQL play in this space.  By pushing core BI functionality back into MOSS, Excel and SQL, they allow the widest possible audience to buy and use these features.
4. Lastly, I truly believe someone finally said "Hey, don’t you think the Dynamics team should be working on the finance stuff?".

So, what does this mean to the MS customer?

1. If you own PPS Planning, stick with it, but start looking for a transition in 2012 or thereabouts.
2. If you own PPS  and only use it for M&A, renegotiate your EA when it comes up to lower the cost of it by the PPS piece that got added.  Other than that, you are in the catbird seat.  Good job!
3. If you were looking at PPS for planning, kill the search and talk to your reseller about alternatives.
4. If you were looking at PPS for M&A, continue.  Having it in MOSS Enterprise is great news!
5. If you were looking at Forecaster, FRx, Enterprise Reporting, stop being fearful of the decision.  The products are great and will be supported into the foreseeable future.

As to Management Reporter, we don’t know what’s going to happen to that.  It was billed as the next-gen FRx, but its feature set is, IMHO, pretty crappy still.  I think it will be enhanced significantly by the Dynamics team and become a truly awesome product, but don’t look for short term results.

MS promises it will deliver more roadmap information at 2009 Convergence in New Orleans.  Will have our usual large team there, so we’ll update you after the conference.

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.