Wednesday, June 04, 2014

Microsoft Dynamics to SAP ECC - Part 2

As promised, I'm posting Part 2 of the Dynamics GP to SAP migration.

Since my last post we've done a lot of cleansing to the GP data. Some of the ETL processes we ran include:

- Master data including GL accounts, Profit Center, Cost Center, Employees, Vendors and others
- Vendor and Customer addresses including multiple addresses for Vendor Partner functions
- Any Tax-related fields including 1099, GST, etc.
- Hierarchy if any (can be messy)
- Open Balances for General Ledger, Accounts Receivable, Accounts Payable and Fixed Assets

Lastly, I consider my SQL scripting decent but get a really good one as you'll need to create lines of code and SP for your ETL.

Goodluck!

Note: I'll see if there's a need for another Part in FI, otherwise Part 3 will be more on SAP SD.

Wednesday, February 12, 2014

Microsoft Dynamics to SAP ECC - Part 1

Actively participating in the data modeling & cleansing, reporting (including BPC, BW and BI) and testing of a Dynamics GP client to SAP AFS.

Exciting, as the last time I was part of a SAP implementation was almost 10 years ago.

Piece of advice if you know you're going to migrate to SAP:
1 - When creating your Chart of Accounts start integrating Cost Center and Profit Center
2 - Create a logic in your Item, Vendor, Customer and Asset masters
3 - Addresses can create a lot of work if not properly maintained
4 - Use logic when using the classes

Monday, September 24, 2012

Dynamics GP 2013

This month Microsoft announced the availability of Dynamics GP 2013 demo which you can download here.

Most of the improvements were geared to improving security, integration with other Microsoft products and the Web client.

I was reading the installation guide of the web client and the requirements were pretty basic including the Web server OS which can either be Windows Server 2008 R2 or 2012. You can secure the web client by implementing SSL which you can purchase from a vendor or use one provided by IIS.

If you've installed Microsoft Sharepoint Server, most of the required steps were very similar including the required authentication.

Goodluck!

Monday, July 30, 2012

ETL tools

Following are top ETL tools used for extracting, cleaning, transforming and loading data to a data warehouse:
  1. Oracle Warehouse Builder (OWB)
  2. SAP Business Objects Data Integrator (BODI)
  3. IBM Data Stage
  4. Microsoft SQL Server Integration Services (SSIS)
  5. SAS Data Integration Studio
  6. Talend Open Studio
  7. Clover ETL
  8. Adeptia Integration Suite

Friday, July 06, 2012

Former Citi VP of Treasury Sentenced

In this age of compliance and tight security, how can one still manage to embezzlement more than $22 million?

Well Mr. Gary Foster did, the former vice president at Citigroup Inc.’s treasury finance department was sentenced to 97 months in prison for fraud related to his embezzlement. Foster embezzled funds from various Citigroup accounts by transferring money to Citigroup's cash account and then wiring it to his personal bank account.

Foster concealed his thefts with various false accounting entries, making the Citi cash account appear in balance and placing a fraudulent contract or deal number in the reference line of the wire transfer instructions.

Made me wonder, which accounting system were they using that nobody can even catch this kind of false transactions especially during month-end reconciliation. I guess if you work for a large multinational bank, it'll take a few cycle before you can confirm that there's fraud or breakage in the process. But mostly this can be remediated by constant communication between people who audit and reconciles cash.

Tuesday, April 10, 2012

LinkedIn error

LinkedIn is acting up today as I'm getting this error a lot - Sorry, there was a problem processing your request. Please try again.

Even on simple links, it doesn't work.

Wednesday, February 22, 2012

10 things you should know about NoSQL databases

Did you hear about the latest trend in databases? If not then you should read this article about NoSQL or  "not only SQL".

NoSQL vary from your classic relational database management system (RDBMS) as they do not use SQL as their primary query language. These data stores may not require fixed table schemas, usually do not support join operations, may not give full ACID (atomicity, consistency, isolation, durability) guarantees, and typically scale horizontally. NoSQL is now used by major internet companies like Google, Facebook, Twitter and others.

Here's an article about NoSQL which will help you understand the pros & cons of using the technology. Click here.

Friday, December 02, 2011

Cloud Computing Practice Overview

Bessemer Venture has been an advocate of Software-as-a-Service (SaaS) for years now. They've compiled a one-pager of all potential providers of cloud services in different areas of the business. Get the one-pager here.

Saturday, July 30, 2011

Apple's iPhone forcing smartphone industry change by Computerworld

The intensely competitive smartphone industry is becoming an increasingly challenging space. Analysts say Apple [AAPL] has achieved the largest share of hardware sales and revenue, putting competitors on any current platform (Android, Microsoft, RIM, etc.) under lots of pressure. In this environment, surely it won't be long until another round of mergers and acquisitions begins?

[ABOVE: Thanks to Asymco for this image, which shows how much things have changed since Apple invaded the mobile industry with the iPhone.]
Apple's game to lose
Surely it's obvious? Think about this: Asymco's Horace Dediu, believes four of the biggest smartphone makers lost money in the last quarter: Nokia, Motorola, Sony-Ericsson and LG.

For more on this click here

Monday, June 27, 2011

Cloud war already started?

For months, I've been waiting for somebody to write an article about cloud-based services which features bigwigs like Micrsoft, EMC, Apple and some unknown players in the market. The day finally came but I was more disappointed than excited about the article as it failed to recognized other David's in the market.

Also, what do you consider players in this market and how do you classify them in such groups? Is it the services they provide or the target markets they're trying to penetrate?

I wonder if Sharepoint will have new competitors once Office 365 goes to market.

Read more about the article here.

Wednesday, June 01, 2011

The Dynamics Decade

I liked this article because it shows you the history of the company I've grown to follow and the individuals who have contributed to its success over the years. Here's an excerpt from the article:

Microsoft originally created a Microsoft Great Plains Business Solutions brand headed by Great Plains Chairman and CEO Doug Burgum, who became a Microsoft senior vice president and president of the Great Plains Division. Burgum reported to Jeff Raikes, then group vice president of Microsoft's Productivity and Business Services Group.


Read the full article here.

Friday, March 25, 2011

The New York City SharePoint Developers User Group Monthly Meetup



Wednesday, April 20, 2011
5:30 PM
Microsoft Office NYC - 6th Floor
1290 Avenue of the Americas New York, NY

Required apps for Cloud computing

You've done your research and know the differences in business model between the 3 areas of cloud computing:

  • SaaS (software-as-a-service). WAN-enabled application services
  • PaaS (platform-as-a-service). Foundational elements to develop new applications
  • IaaS (infrastructure-as-a-service). Providing computational and storage infrastructure in a centralized, location-transparent service

So you decided to jumped on the bandwagon. Now you're planning the operations of the company and looking at the operational processes from the point of contact to revenue recognition. You start creating the flow and do an inventory of the applications required for each process. At a high-level this is what you had at the end:

CRM - This system will initialize the process. A simple CRM sales cycle include prospecting, proposal then Win. You can go for On-demand or On-premise solutions.

Contract Management -  The contract creation should be simple yet flexible. These days CMS softwares are flexible enough to create contracts in different languages which caters to different regions - EMEA, AsiaPac, etc. Flexibility is very important because contracts can have different data elements and the application should have this feature.
Billing - This is the heart of the whole system. When looking for this system, you should understand that this goes to details of your process so involve hands-on users as they know what's important to this process. Accurate billing is your main objective here. Invoice formats is likewise an important piece.
Collections - With companies going global, expect that customers will require you to cater to their needs. One common pitfall of this system is the ability to receive payments other than the customer's region currency. Your system should be flexible enough to accept payments in different currencies. You don't want to return a payment because it's on the wrong currency...Money is king!
Usage Metrics -  Every customer using your service expect to know their monthly usage. This system should be able to track where the customer is in terms of usage. If you're sending a monthly invoice to your customers then this is based on monthly usage. Some companies support real-time usage on their websites for customers to see.
Revenue Recognition - The accounting for Cloud computing is still on its infancy. New accounting rules will be define in the coming years and this will change how applications determine Revenue recognition. The juice of the discussion will focus on how to leverage this new way of doing business. Revenue calculations will neatly integrate with customers subscription contracts but there are other ways of doing business in the cloud which can define how the new rules will be laid out.
General Ledger -The last piece of the puzzle but not the least important as this can determine if your process is working or not. Other applications will feed this system, application or module. You have to know your integration points and GAAP rules that govern those systems. Don't take your reconciliation process for granted.
Others - Tax, reporting - financial and operational, templates,etc.

Integrating with Dynamics GP - Part II

One of the more complex integration in this project were the customer payments integration and Revenue. The entry point of the integrated data in GP will be in two (2) windows: General Ledger and Bank Deposits.

The General Ledger integration were the entries originally created from the Revenue & Expense Deferral (RED) module. Now that the subledger is non-resident to GP, we have to create the middleware to do this. It was a mix of SSIS & Dexterity and since the host system was on Oracle we had to create Linked Servers.

The Bank Deposits is pretty straight forward as customer payments were received from the billing system, the entries are booked to GP as cash and a separate GL entries are created.

Another complexity in this project is the data migration of the subscription contracts in GP. We were using a 3rd-party product and we made customizations which slices the contracts on an annualized basis. All active contracts were migrated so billing can continue on the new system.

Lastly, reporting and Smartlist. Given that subledger reports will be coming out of the new system, the reconciliation process between GL & Subledger will change. Smartlist reports for subledgers goes away as well.

Friday, October 01, 2010

NYC Sharepoint Developers User Group Meetup

The NYC Sharepoint Developers User Group will meet again on Oct. 20 at the Microsoft office in the Avenue of Americas, NY.

The topic for October is "Word Automation Services in Sharepoint 2010" by Kenneth Cooper.

Join us by visiting the website here.

Wednesday, September 29, 2010

IntraLinks Holdings Inc. is now public

IntraLinks is now a public company (NYSE: IL). Our company is a cloud services provider of online secure exchanges for different industries including Financial services, Life Sciences, Alternative Investments, Real Estate, Legal, Manufacturing and others.

To check our stock in NYSE, here's the link: http://www.nyse.com/about/listed/il_news.html

Here's a clip of our CEO in Bloomberg: http://www.bloomberg.com/video/63320076/

Thursday, July 15, 2010

Integrating with Dynamics GP

I've been through a lot of GP integrations and most of them were very straight forward. In my current environment we have integrations from different sources including Salesforce to SOP module, IT Asset management to Fixed Assets module(including bar-code implementation), ADP to GL, and others.

The current work will be considered the most challenging as we're moving the AR subledger to a new Oracle-based billing software and GP will be processing General Ledger entries from this system. So what's involved? The following transactions will be created in the new system and all the journal entries will be push to GP - customer payments (Lockbox, Wired,etc.), invoices, credits, write-offs, refunds(AP entry) and others.

I guess the experts are telling themselves this is NOT much of a challenge, well not really and why? That's just the first part of this integration and since we're a subscription-based environment like Salesforce, we need to create deferred schedules for each document integrated. I'll stop there for now and let you create your own architecture for this integration.

Monday, July 12, 2010

Jack Dorsey on Twitter and Square

I was watching Beyond the Boardroom this weekend with their guest Twitter Founder Jack Dorsey. Jack introduces his new product - Square, a revolutionary mobile credit card reader that plugs into the audio port of compatible smart phones and laptops.

Also in this episode, Jack discussed the importance of Twitter in business including applications built around Twitter.

Here's an excerpt from the episode:
http://www.youtube.com/watch?v=tVc596DJYR8

Wednesday, May 12, 2010

Watch the World Cup

Here's a good website to find a popular venue based on your team for the World Cup. The website also shows the schedule of the preliminary games. Click here to go to the website.

Tuesday, May 04, 2010

Convergence 2010 - Exciting Atlanta

I always give my feedback a week after the conference but I take notes during all the action is happening so I don't forget the important stuff later. Here's some notes that I've kept and wanted to share with everyone: 

- I was surprise on the number of Dynamics GP attendees this year. After the keynote session more than half stayed for the next session which was on the next version - Dynamics GP 2010.

- There's a new group of GP attendees and they're eager to contribute in the future of GP. This is critical as in the past attendees were just there to learn but now they want to contribute and put their skin in the game.

- In the past a lot of attendees spent their time going to basic sessions but now more went to Advanced sessions and also to Project Management sessions. Dale Carnegie came this year with different sorts of sessions on project management and it was well attended.

- New sessions from experts like the Support Debugging Tool which David Musgrave and Mariano Gomez hosted. This was well received and hopefully they come back next year with more.

- I saw old friends which I haven't seen in years including David Musgrave(Match Data days), Ayodale Cole (Cole Solutions), Frank Buckley and guys from Tectura who helped me implement GP in a NJ manufacturing.

- My favorite session this year was the one by New Orlean Saints coach Sean Payton. So much emotion during the session and you see his excitement in the coming season. He's very passionate about what he does, he cares for his players and he loves New Orleans.

- Other feedback:
    - The walk was too long from the food court to the sessions. So far this is the biggest in terms of area.
    - Food in the conference was ok, nothing to get excited. We tried Mary Mac Tea Room during one of our dinner and the ribs were incredible. Here's a pic of my plate:



    - Bus service was always available and when it's full you can get assurance from somebody that the next one is available....and it is.
   
- Lastly, Atlanta was a great host. I want to go back and visit the World of Coca-cola, CNN, Georgia Aquarium and others.

Saturday, May 01, 2010

Sharepoint 2010 and SQL database mirroring

http://blogs.technet.com/b/wbaer/archive/2010/05/03/database-mirroring-in-sharepoint-2010.aspxHere's my first article on Sharepoint 2010, it's about creating a SQL database mirroring for the environment. Click here to read the article.

Wednesday, February 24, 2010

InfoView for Crystal Reports

Finally started working on publishing all my Crystal Reports to InfoView, a web application from Business Objects Enterprise (BOE).

Infoview runs on the web browser and is utilized to run and schedule Crystal Reports or any other report.

Thursday, January 28, 2010

Business Intelligence experiences

During my 17-plus years of being a business owner or employee, I've had the opportunity to implement, feed, test or used a Business Intelligence (BI) product including Cognos, Microsoft's PerformancePoint Server Planning(discontinued) and now Business Objects. I like the look and feel of this product but wonder what will change in 2010 since SAP took over. Nowadays, database vendors provide Business Intelligence solutions in their product line and you can check vendors like SAS (Voted by Fortune as the top company to work for in 2010), Oracle, Microsoft, IBM and now SAP.

You can add value to your customers if you can implement a BI solution right on top of their ERP/SCM/CRM system.

Thursday, January 07, 2010

Windows 7 is now the fastest selling OS

Last night at the CES 2010 keynote, Microsoft CEO Steve Ballmer announced that Windows 7 has become the fastest selling operating system in history....read it here

Thursday, November 12, 2009

Information Rights Management (IRM)

Here's an interesting article from Fahim Siddiqui, EVP of Intralinks, Inc. His article can help organizations in understanding the complexity of applying encryption and permissioning to corporate information. Read article here


Tuesday, November 10, 2009

Revenue and Expense Deferral

What is Revenue and Expense Deferral (RED)?

Revenue Deferral is considered a liability until it becomes relevant to the business at hand, such as a payment received for work that has not yet been performed. opposite of deferred charge. Expense Deferral refers to an item that will initially be recorded as an asset but is expected to become an expense over time and/or through the normal operation of the business.

Who can use this module?

Dynamics GP RED (Revenue and Expense Deferral) was designed for any company that needs to defer revenue or expense.

Let's say you're a media company with business in magazine publishing. You're offering your magazine subscription on a yearly, semi-annually or monthly (one-time) basis. When somebody subscribes to your magazine and pay a year subscription, you'll need to defer part of this subscription. At the end of the subscription period, you will have fulfilled your responsibility and the subscription will be fully realized as earned income.

How do I book a deferred entry from a Sales Invoice in Dynamics GP?

Once you have the sales invoice created, open the Distribution window select the account you want to defer then go to Extras>Additional>Deferral. Enter your Start and End dates, billing recognition account and choose your deferral method (Days Period, Equal Period and Miscellaneous).


Things to note when using this module:
- Deferred entries are always valued at functional currency
- You can only defer one invoice at a time
- Void Deferral Transactions can be utilize to void deferred entries only and have the original document like a Sales Invoice unvoided

There's room for improvement with this module including:
- Mass document deferral
- Audit-trail from a GL entry
- Multi-currency reporting based on originating document currency

To learn more about Revenue and Expense Deferral, you can visit RevenueRecognition.com

Saturday, October 10, 2009

Get Customers with more than one address

Here's a simple SQL statement that I've used to help me resolve some of our issues be it customer, vendor or item master files:

Select custnmbr, Count(ADRSCODE) As AddressCount
From RM00102
Group By custnmbr
Having Count(ADRSCODE) > 1

or if you want detail use this

Select *
From RM00102
Inner Join (
Select CUSTNMBR, Count(ADRSCODE) As AddressCount
From RM00102
Group By CUSTNMBR
Having Count(ADRSCODE) > 1
) As A
On RM00102.CUSTNMBR = A.CUSTNMBR

Tuesday, September 15, 2009

SQL script to show last invoice detail of every customer

Copy and paste this to SQL Server Enterprise Manager:

select distinct a.custnmbr,a.LSTTRXDT,a.lsttrxam,b.sopnumbe
from rm00103 a
join sop30200 b
on a.custnmbr = b.custnmbr and
a.LSTTRXDT = b.docdate
/* show in ascending order */
order by custnmbr asc

Friday, April 24, 2009

Planning SQL Upgrade

So you finally decided to upgrade to SQL Server 2008 but it's been awhile since you looked at options for your Raid configurations. Here's a summary of what your options are:

Raid 0 - data is spread in different disk (disk stripping) but lacks redundancy
PROS - performs well on read and write
CONS - Failure of disk means data is lost
Raid 1 - known as disk mirroring no disk stripping. Data is hosted on a single drive then copied to another drive for fault tolerance. Recommended for Transaction Logs.
PROS - Redundant so failure of a drive will not stop database access
CONS - Cost since you'll need to double the size of your disk.

Raid 5 - uses disk stripping with parity. Data is spread but also stores parity information so it can be used in case of disk failure. Best for read-based not many write operations. Also avoid for transaction log because it's primarily sequential write.
PROS - Fault Tolerance at a low price.
CONS - Write Performance. Additional time is required to compute parity info before it's written to disk.

Raid 10 - cross between Raid 0 and 1. Data is striped evenly accross all drives then mirrored.
PROS - Write performance is far superior than Raid 5 and read performance is similar to Raid 5. Offers Fault Tolerance.
CONS - Cost since you'll need twice the number of disk like Raid 1.

Monday, April 20, 2009

SQL Wildcards

We implemented Multicurrency module and part of this implementation is changing the naming convention of our customers in Great Plains. We want to easily identify what kind of customer are they. Currently, our customer id ends in 001 so we decided to change it to whatever the contract currency is. So let's say ABC has a EURO contract with us then their customer id will then be ABCEUR instead of ABC001.

Our database is shared to other systems like our BI team to create relational DBs. A question came to my lap on the easiest way to segregate the customers based on their contract currency. We don't want to give them the Currency ID field as it's not consistent with other system. So I provided them with this Select statement:

Select * from RM00101
where custnmbr like '%[EUR]'

Thursday, March 26, 2009

Proud Uncle

I'm so proud of my niece, Guia del Prado, that I wanted to congratulate her in my blog for being featured in her schools website - University of Nevada, Reno : www.unr.edu .

She's currently the news editor for the campus magazine - Insight.

Wednesday, March 25, 2009

Convergence 2009 update

Just came back from Convergence 2009 - New Orleans and found this year quite a disappointment as I didn't see a lot of value to the seminars, GP ver 11 and the future of products around Dynamics GP.

Here are my updates this year:

1) GP ver 11 - The presenter in the kiosk didn't show a lot of functionality as she said they're still finalizing the list but I like the option of printing a form to Microsoft Word which many of us had been requesting.
2) FRx - No goods new here as Microsoft announced last year Management Reporter will replace it. I found that Management Reporter lacks some functionality including XBRL (I hope they've resolve this already), etc. FRx support end on December 2010.
3) Forecaster - The big question - PerformancePoint or Dynamics?
4) CRM - There were a few seminars showing the new integration between this product and Dynamics GP. One presenter said it can now compete with vendors like Scribe.

I'll post more later....