What happened to all of my GP OLE Notes?

During the migration of OLE Notes to the GP 2013 and above to the document attach functionality not all OLE Notes will get migrated through the Migration tool. After the GP OLE Notes migration tool has completed you will see a screen detailing the process and the number of unsuccessfully extracted notes. Through research from other GP OLE Notes blog, I was able to piece together a manual process to identify, extract and then attach the document within GP to the appropriate record.

GP OLE Notes 002

The Manual Process

Part One – Identifying the Record the OLE Note is associated with

All OLE Notes that weren’t successfully extracted by Microsoft’s migration utility will remain in the original extraction path.

1. To create a list of the files within the folder follow the directions in this Microsoft KB https://support.microsoft.com/en-us/kb/196158. After that I created an Admin database in SSMS on the SQL server and then imported the results within the txt file to a table called OLENotes_Missing.

2. I ran the following SQL script in SSMS to find all missing OLE Note names and associated Note Indexes and convert the HEX Note name.

SELECT NoteINDEX, DATE1, HEX_REMOVE as [OLENote Name]

FROM [ADMIN].[dbo].[OLENotes_Missing]

Order By DATE1 DESC

3. Take the Note Index from first SQL Script and run below SQL script in SSMS to find associated tables. Replace Note Index searching for into yellow highlighted area.

USE [<Production Database>]

DECLARE @noteindx nvarchar(20)

DECLARE @tablename nvarchar(20)

DECLARE @sqlstring nvarchar(MAX)

DECLARE @param nvarchar(100)

DECLARE @print nvarchar(100)

SET @noteindx = ‘9708539’ –Change to note index that you would like to find

DECLARE notecursor CURSOR FOR

SELECT c.TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS c

LEFT OUTER JOIN INFORMATION_SCHEMA.TABLES t ON t.TABLE_NAME = c.TABLE_NAME

WHERE c.COLUMN_NAME = ‘NOTEINDX’ AND t.TABLE_TYPE = ‘BASE TABLE’

OPEN notecursor

FETCH NEXT FROM notecursor INTO @tablename

SET @sqlstring = N’SELECT ”’ + @tablename + ”’ AS TableName, NOTEINDX, DEX_ROW_ID FROM ‘ + @tablename + ‘ WHERE NOTEINDX = ‘ + @noteindx + CHAR(13);

WHILE @@FETCH_STATUS = 0

BEGIN

SET @sqlstring += N’ UNION SELECT ”’ + @tablename + ”’ AS TableName, NOTEINDX, DEX_ROW_ID FROM ‘ + @tablename + ‘ WHERE NOTEINDX = ‘ + @noteindx + CHAR(13);

FETCH NEXT FROM notecursor INTO @tablename

END

CLOSE notecursor

DEALLOCATE notecursor

EXEC(@sqlstring)

4. Output from above script will look like below.

GP OLE Notes 004

5. Disregard TableName SY03900 and substitute the other TableName and DEX_ROW_ID into SQL script below and execute in SSMS. This will give you the record that the OLE Notes document will need to be attached to.

SELECT * from SVC00600 WHERE DEX_ROW_ID = 51259

GP OLE Notes 006

PART 2 – Document Extraction and Attachment

Using 7Zip you can manually extract the contains of the file.

1. Open 7Zip and navigate to original OLE Note extraction location.

2. Find Note Name you wish to open and then drill down into Embedding folder(s)

GP OLE Notes 008

3. Open CONTENTS file with Internet Explorer

GP OLE Notes 010

4. Print output to PDF

GP OLE Notes 012

5. Log into GP and find the record from SQL script from step 5 of Part 1 and then attached record using document attach functionality. Follow Steps 1 through 3.

GP OLE Notes

A very manual process, but worth the time if you need the documents within your GP system.

Advertisements

Using Market Basket Analysis to increase sales using my Microsoft Dynamics GP data.

 

Part 3 of a 5 part series

Market Basket Analysis, what is it?

Market basket analysis is a data-mining algorithm common to marketing. It identifies customer purchasing habits by analyzing previous purchases to determine items they buy together, the frequency of purchase and the order of purchase.

Why would I want use it?

Consumer behavior is complex and unpredictable — what appeals to one buyer may not appeal to another. This buying information will enable the retailer to understand the buyer’s needs and rewrite the store’s layout accordingly, develop cross-promotional programs, or even capture new buyers (much like the cross-selling concept). A widely used example of cross selling on the web with market basket analysis is Amazon.com’s use of “customers who bought book A also bought book B”. Here are some examples of what Market Basket Analysis can be used to do:

  • Increase sales with marketing and sales promotional campaigns
  • Segmentation of customers to create targeted advertising and marketing campaigns
  • Placement of goods in retail stores, catalog and web sites to increase sales via cross selling or up selling
  • Education of Salespeople
  • Inventory Management

How do I perform Market Basket Analysis on my data?

In Excel 2013, we have two options. Microsoft provides a Data Mining add-in for SQL server 2008 and 2012. The 2012 version can be downloaded from here: http://www.microsoft.com/en-us/download/details.aspx?id=29061. For purposes of this blog post, I will be using Predixion Software’s Data Mining add-in for Excel 2013 which is located here: www.predixionsoftware.com/predixion/download.aspx.

After downloading and installing the add-in, you will need to run the following SQL script in SQL Server Management Studio:

  SELECT  [SOP_Number],
          [Item_Number],
          [Item_Description]
  FROM [view_SOP_Line_Items]
  WHERE SOP_Type in (2,3)
  ORDER by 1

After you run the SQL script you can copy the output to Excel by selecting all of the cells and then right clicking in the top corner of the output section and selecting to “Copy with Header” data and paste into Excel.

clip_image002

Next we want to select the Predixion’s “INSIGHT NOW” tab and select the “Shopping Basket Analysis” button.

clip_image004

Predixion provides a wizard to step you through the process. The first window asks you for the data range you wish to analysis and if your data contains headers.

clip_image006

Step two of the wizard asks for Transaction ID, Item and Item Value. Below is a screen shot of how I filled out the required and optional fields in this window.

clip_image008

This kicks of a process that analyzes the data and creates two additional tabs within your Excel spreadsheet. Here we see the Shopping Basket Bundled Items recommendation tab. clip_image010

This provide a clear association on how are current customers are buying our products and how we could create promotional campaigns, educate our salespeople on cross-selling opportunities and make changes to our retail layout or web store to increase sales.

Until next time, where in part 4 we will be building an Excel 2013 Dashboards with our Microsoft Dynamics GP data.