Tuesday, July 31, 2012

Creating an Enterprise Data Warehouse (EDW) for Consolidated Business Intelligence (CBI) Series

After my DW/BI post yesterday, a reader mailed me pointing me to the Creating an Enterprise Data Warehouse (EDW) for Consolidated Business Intelligence (CBI) Series by John Lowther.  John has some great ideas – and recommendations, so check it out.

Here’s the summary from John’s blog -


As the last post in this series, I wanted to recap and try to give a high level of what we wish to accomplish.

If you remember on the first post we wanted to achieve two things, and that was not to hurt the performance of the Applications or of the BI Solution.

In the second post, we defined some generic ways of thinking about data in general. Things like concentric data, superfluous data, solitary data, relational data, etc.

Then we look at a way to keep the number of SQL Jobs down so that we have a maintainable system in the end.

Then we looked at a way to monitor the system to make sure that performance does not degrade over time.

We even touched on a way of getting data from on system to another, and started up another set of related posts on that subject, specifically named "Getting data from Point A to Point B".

Monday, July 30, 2012

Data Warehouse / Business Intelligence Solution for Dynamics GP–Part 1 Overview

Over the last few years we have seen a wide variety of BI solutions for Dynamics GP. As we see more and more mid to large sized Dynamics GP Implementations (40 to 100+ users) – having robust DW/BI (Data Warehousing/Business Intelligence) solutions is becoming a necessity. You can have excellent BI tools ( SSRS / BI 360 /Smartlist Builder / Crystal / Excel Refreshable reports) – but you need a scalable solution that works for a large user base.

As it is with most processes, there are best practices to achieve your goals. When I was programming, I really enjoyed reading about and implementing Design Patterns and Refactorings – that changed programming for me. Implementing these common sense techniques saved me time, and helped deliver robust solutions.


There are similar best practises for Data Warehousing and Business Intelligence. Kimball’s approach is very popular – it’s always been highly recommended in the DW/BI world. My aim with this post and the next few posts is to introduce you to the Kimball approach, and associate how it ties up with Dynamics GP Implementations. However, first lets take a look at OLAP – and where that fits into the GP world.

If you look at the system requirements for Dynamics GP – you will see that

  • For 20-60 users – Microsoft recommends - Perform some online analytical processing (OLAP) cube generation
  • For 60 – 100 users – Microsoft recommends – Perform online analytical processing (OLAP) cube generation (to different machine)

Microsoft provides a module – Analysis Cubes – which implements most best practices and can help significantly with performance. At the same time, its important to understand what goes behind the scenes, as you would often be integrating with multiple systems – and have custom requirements.

So what are the advantages of reporting from a dimensional OLAP database like SSAS vs. directly reporting from Dynamics GP SQL Database ?

1. OLAP database technology is optimized for querying and reporting, instead of processing transactions.

The Dynamics GP Database is designed as a highly normalized OLTP database. There are lots of small tables with relationships defined between them. This is good for additions, deletions, and modifications of a field, which can be made in just one table and then propagated through the rest of the database via the defined relationships. However, if you have written reports for GP – you would know it often involves creating multiple joins. This is fine for smaller implementations, but as the number of users and transactions increase – can lead to performance bottlenecks.

Here is a simple SQL view (Sales by Item by Month – by Victoria Yudin)

   3:  SELECT
   4:  D.ITEMNMBR Item_Number,
   5:  D.Item_Description,
   6:  D.Generic_Description,
   7:  D.Item_Class,
   8:  D.User_Category_1,
   9:  sum(case when month(D.DOCDATE) = 1 then D.SALES else 0 end) as [Jan_Sales],
  10:  sum(case when month(D.DOCDATE) = 2 then D.SALES else 0 end) as [Feb_Sales],
  11:  sum(case when month(D.DOCDATE) = 3 then D.SALES else 0 end) as [Mar_Sales],
  12:  sum(case when month(D.DOCDATE) = 4 then D.SALES else 0 end) as [Apr_Sales],
  13:  sum(case when month(D.DOCDATE) = 5 then D.SALES else 0 end) as [May_Sales],
  14:  sum(case when month(D.DOCDATE) = 6 then D.SALES else 0 end) as [Jun_Sales],
  15:  sum(case when month(D.DOCDATE) = 7 then D.SALES else 0 end) as [Jul_Sales],
  16:  sum(case when month(D.DOCDATE) = 8 then D.SALES else 0 end) as [Aug_Sales],
  17:  sum(case when month(D.DOCDATE) = 9 then D.SALES else 0 end) as [Sep_Sales],
  18:  sum(case when month(D.DOCDATE) = 10 then D.SALES else 0 end) as [Oct_Sales],
  19:  sum(case when month(D.DOCDATE) = 11 then D.SALES else 0 end) as [Nov_Sales],
  20:  sum(case when month(D.DOCDATE) = 12 then D.SALES else 0 end) as [Dec_Sales]
  22:  FROM
  24:   coalesce(I.ITEMDESC, SD.ITEMDESC) Item_Description,
  25:   coalesce(I.ITMGEDSC, '') Generic_Description,
  26:   coalesce(I.ITMCLSCD,'') Item_Class,
  27:   coalesce(I.USCATVLS_1,'') User_Category_1,
  30:       WHEN 4 THEN SD.XTNDPRCE*-1
  31:       END SALES
  32:   FROM SOP30200 SH
  33:   INNER JOIN
  34:       SOP30300 SD
  38:       IV00101 I
  41:       AND SH.SOPTYPE IN (3,4)
  42:       AND SD.XTNDPRCE <> 0
  44:       AND year(SH.DOCDATE) = 2017 --change year as needed 
  45:       ) D
  47:  GROUP BY D.ITEMNMBR, D.Item_Description, D.Generic_Description, D.Item_Class, 
  48:  D.User_Category_1

Here are the results from Fabrikam


Its getting data from multiple tables, and aggregating data on the fly. If we look at this query in the profiler – it takes about 57 ms to execute.


A OLAP system like SQL Server Analysis services, is designed for reporting and analysis. There are pre-processed aggregations, and proactive caching which helps in generating report in a fraction of a time that it would normally take.

Here is the corresponding MDX (query language for SSAS – you can often get away with the generated MDX from the designer and Excel power pivot)

   1:  SELECT NON EMPTY { [Measures].[Extended Price - Detail] } ON COLUMNS,
   2:  NON EMPTY
   3:  {
   4:    ( [Items].[Item No].[Item No].AllMembers * [Master Date].[Month].[Month].AllMembers )
   5:  } Dimension Properties MEMBER_CAPTION,
   7:  FROM (
   8:    SELECT ( { [Master Date].[Year].[All Date].[2016] } ) ON COLUMNS
   9:    FROM [Sales]
  10:  )
  11:  WHERE ( [Master Date].[Year].[All Date].[2016] ) CELL Properties Value,
  12:  BACK_COLOR,
  13:  FORE_COLOR,
  16:  FONT_NAME,
  17:  FONT_SIZE,

Here is the result output


SSAS has pre-calculated measures


If you look at this MDX query being executed in profiler, it takes about 10 ms to execute.


As you can see for the same result output SSAS delivers a much better performance, as the SSAS database is built for Analysis and reporting.

2. Analysis Cubes for Dynamics GP can serve as an easier reporting platform for end users.

Dynamics GP with its complex Database structure can be a nighmare for end users to report on. With user friendly dimensions and measures, end users can use tools like SSRS and Excel to report on data.

Consider end users digging data from the Dynamics GP database versus


Users digging data from well defined dimensional model in SSAS (accessed from within Excel easily)


3. Users can view data in Excel, without requiring access to Dynamics GP.

For example, Analysis Cubes can be used to view all the checks that have cleared the bank, or the total purchases made by a customer, and these Excel users do not need access to Microsoft Dynamics GP. Information from Dynamics GP Analysis Cubes can be viewed in pivot tables in Microsoft Excel. Analysis Cubes can be used to spot trends by producing analytical views of sales information, buying patterns, and other values.

Hopefully, if you have read this far, I have you interested in why this is something you need to be considering for your growing BI requirements.

UPDATE : The new licensing for Dynamics GP seems to have Analysis Cubes in starter pack, which would mean it would be even more lucrative to implement this for your Dynamics GP implementation.

Over the next few posts I am aiming to look at

  1. Overview of Kimball Approach for a scalable BI solution for Dynamics GP
  2. Kimball Approach for gathering BI requirements
  3. Master Data Management with Dynamics GP
  4. Building a robust ETL (Extract Transform Load) system for initial and ongoing load into Dynamics GP
  5. Building a Data warehouse for Dynamics GP (following best practises)
  6. Building a BI solution for Dynamics GP (following best practises)
  7. Reviewing the existing DW/BI toolset for Dynamics GP
  8. Analysis Cubes Module for Microsoft Dynamics GP (how it implements parts of Kimball approach)
  9. Latency for Dynamics GP BI (real time vs next day)
  10. Data Mining for Dynamics GP
  11. Security and Metadata for a DW/BI solution

Monday, July 23, 2012

GP 10 Support End Date

My friend Sunbeam brought to my attention today that the mainstream support for Dynamics GP 10 is ending on 10 Sept 2012.



Friday, July 20, 2012

Smartlist Left Pane Resizing Awesomeness

David Musgrave is back and rocking with Resizing SmartList TreeView and ListView panes using the Support Debugging Tool. This is one of the most requested features for Dynamics GP – (See Mark Polino’s: Dynamic Future - Vote - Resize the Left Pane on Smartlists)

While you are at it also check out - Patrick Roth’s modifier solution - Smartlist: Only the data area should grow and Frank Hamelly who links to a dexterity solution - Resize the left pane in SmartList

Before Resize

before resize 4-07-2012 7-13-50 AM

After resize

After Resize 4-07-2012 7-21-29 AM

Wednesday, July 18, 2012

Making Dynamics GP remember the position of a window

David Musgrave shows a very cool demo of Using Support Debugging Tool Non-Logging Triggers by Adding Window Size and Position Memory using the Support Debugging Tool

Say you want your PO window to always open up on the right side of your very large monitor, and you always drag the window to be longer than normal. By using SDT with David’s sample – you can have GP remember the position of the PO window every time you open the window.



Tuesday, July 17, 2012

GP 2013 features

The GP Product management team has started their popular Feature of the Day series for the upcoming GP 2013. They have been working hard as always, and this time there is a slideshow complete with a Video, learning script and screenshots !

Go check it out at http://blogs.msdn.com/b/gp/archive/2012/07/17/feature-of-the-day-reason-codes.aspx

The series will be available at http://blogs.msdn.com/b/gp/archive/tags/feature+of+the+day/



Thursday, July 12, 2012

New Dynamics GP Pricing Announcements

Go check them out at -



A few highlights:

  • This pricing announcement is for the new price model called Perpetual.  It's buying it outright like we do today.
  • Subscription pricing will be a limited pilot and will have a similar structure to this new pricing.
  • This simple structure gives you a unique opportunity to sell differently.
  • There are two actual logins for Microsoft Dynamics GP when we launch.
  • For customers to get GP 2013, they have to ask for it.  They will not automatically get it.

Monday, July 9, 2012

Dynamics AX to be available on Volume Licensing

Announced at WPC-  Microsoft Dynamics AX will be available on Sept. 1 on Enterprise Agreement (EA) in Volume Licensing.

Microsoft will also roll out simplified pricing for SMB ERP products as part of the release of Dynamics NAV 2013 and Microsoft Dynamics GP 2013 later this year.

Other Notables -

· Windows 8 RTM first week of August

· Windows 8 to be released end of Oct

· Windows 8 Pro upgrade from a new Windows 7 PC is $14.99

· Windows To Go - a USB stick that can install and run Windows 8, company apps, BitLocker, and any personal data you want to store


POP Tax engine for eConnect

David Musgrave has a great post up about how often this is requested - Product Suggestion: eConnect POP Tax Engine

Read it, and then go vote. At the time of my voting there are 7 votes. I am sure the number will increase significantly as the Americans wake up.

I am really loving the enthusiasm from the community with regards to product suggestions. Watching how this has, and will shape the upcoming releases of Dynamics GP is fascinating.

Sunday, July 8, 2012

Re-awarded Microsoft MVP

Last week I was re-awarded with the Microsoft MVP award for 2012. It’s a great honour, and once again thank you to David Musgrave, Belinda Allen, Mark, Mariano for leading the way. Also thank you to Jay Manley, Pam, Chad and Errol in the GP Product management team– for all their help and support.

There are a lot of things to look back at in the last year -

Many things to look forward to in the coming months -

  • A Dynamics GP Book, and a Video course I recently reviewed (Quality stuff, soon to be released)
  • A community initiative Belinda Allen is planning (And getting to meet her on the MVP Meets)
  • A small GP Product  (think Microsoft Surface/iPad and GP)
  • Playing around with the awesome GP 2013 Web Client
  • DW/BI with Microsoft Dynamics GP – a series of posts
  • An awesome project I am working on with Mark Polino

I am also hoping to attend either the Tech Airlift or the MVP summit in the coming year.

Lets see how all that pans out!

My dad remembered I got awarded on July 1 last year, and called me the same day this year to ask if I got the award. He had been checking the Microsoft MVP award website. So it was very satisfying telling him the next day that I did, and how happy it made him!

Tuesday, July 3, 2012

New Dynamics GP MVP–Belinda Allen

Please join me in congratulating long time Dynamics GP expert – Belinda Allen on being awarded as a Dynamics GP MVP.

Belinda has over 109 free Dynamics GP training videos, a very popular GP Blog with 100s of posts and is a regular contributor to Dynamics Community online and community events. As I mentioned last year, when I was awarded as an MVP – I have learnt most of what I know about Dynamics GP from Belinda, and Richard Allen. 

Her Blog : http://community.dynamics.com/product/gp/gpnontechnical/b/belindaallen/default.aspx

Youtube Channel : http://www.youtube.com/user/HowToDynamicsGP

Dynamics Sessions : http://decisions.msdynamicsworld.com/speaker/belinda-allen

Other free Webinar and Sessions : http://saci.com/blogs/belinda-the-gp-csi/