Beyond Excel – Microsoft Office Access?

One of many Excel’s talents is its data storage; we can store a lot of data in Excel workbook. Excel 2010 64 bit can create a workbook with a size of up to 4 GB. However, Excel is not designed to be a database management system and have many limitations to handle enterprise grade business applications, e.g. each worksheet can only store up to 1,048, 576 row, it does not have robust data integrity and concurrent users support. Therefore, many power Excel users are looking for a better data management software and Microsoft Office Access is often chosen.

Access is a powerful desktop database management system and it is very suitable for desktop applications with a small number of users accessing it simultaneously. Microsoft Office Access is currently bundled with Microsoft Office Professional or you can buy a standalone version for US$ 139. It has 2 main components; a database engine and a rapid application development (RAD) tool that would generate a business application with minimum coding. Microsoft Office Access is definitely a very user friendly, powerful and flexible tool for small and medium business and its license cost is very reasonable.

Despite all these benefits, I have been recommending Microsoft SQL Server to my clients and a lot of people asked me why I prefer SQL Server. One very important reason is that they don’t have budget for an enterprise grade database management system (DBMS); they believe SQL Server is very expensive! Well, what if I tell you that it is free. Microsoft SQL Server has many editions to fit different needs and its Express Edition is FREE, we can download it from Microsoft. Nothing beats free! Yes, it is a trimmed down version but it is still more powerful than Microsoft Office Access in many aspects, e.g. it includes 10GB of storage per database when Access only supports 2 GB; it can utlilize up to 4 cores when Access can only use 1 sinlge core; it provides much better performance and scalability for multi users application. Another key benefit is that SQL Server has multiple editions so we will have a seamless upgrade path when our business grows, e.g. Express Edition supports 1 CPU (up to 4 cores) but Standard Edition can take advantage of 4 CPU (up to 16 cores). We don’t have to worry about if our DBMS can grow with our business.

Another advantage of SQL Server is its recent cloud deployment, i.e. SQL Azure. SQL Azure is a cloud database service provided and maintained by Microsoft. High availability, scalability and fault tolerance are all built-in and it has a 99.9% monthly SLA. We let the experts (who can be more expert than Microsoft when it comes to SQL Server) to manage our database platform. SQL Azure has no upfront cost; we don’t need to buy any new hardware nor software license. We just pay a monthly subscription fee that is based on our usage:

Database Size

Database Size Price Per Database Per Month
0 to 100 MB Flat $4.995
Greater than 100 MB to 1 GB Flat $9.99
Greater than 1 GB to 10 GB $9.99 for first GB, $3.996 for each additional GB
Greater than 10 GB to 50 GB $45.954 for first 10 GB, $1.998 for each additional GB
Greater than 50 GB to 150 GB $125.874 for first 50 GB, $0.999 for each additional GB

Data transfers measured in GB (transmissions to and from the Windows Azure datacenter)

All inbound data transfers, i.e. data going into the Window Azure platform datacenters, are free. Price for outbound data transfers, i.e. data going out of the Windows Azure platform datacenters, is shown below:

  • North America and Europe regions: $0.12
  • Asia Pacific Region: $0.19

With as little as a McDonald lunch per month, we can get an enterprise grade DBMS platform and supports. But if we think we can handle our own data backup, disaster recovery, we can stay with the free Express Edition which supports up to 10 GB of storage. Everything sounds great but let’s ask one fundamental question; is it easy to work with SQL Server within Excel? After all, we still want to use Excel to do our analysis. I have couple blogs about importing data into Excel, “Import Data Into Excel – Data Connection” and “Import Data Into Excel – VBA “. You will find that importing data into Excel is as easily as from Microsoft Office Access.

SQL Server can also help us to:

  • Simplify Development Efforts –friendly GUI, fully integrated with Visual Studio, and Performance tuning
  • Develop Sophisticated Applications – apart from desktop application, it is also ideal to support web applications and smartphone apps which require high scalability and reliability.
  • Deliver Better and Faster Information – empower users to access the information they need through various advanced business analytic tools, e.g. Reporting Services, Analysis Services, Data Mining….

Instead of answering why I prefer to use SQL Server, I always ask why we want to use a desktop database platform when we can have an enterprise DBMS. Why do you like to use Microsoft Office Access?

How about  rapid application development (RAD) tool that Microsoft Office Access provides?  It is also a great development tool and I will discuss if there is other better alternatives in another blog.

DATA = Gold Mine

Organizations make business decisions every day. These decisions may range from how much discount should be given to clients, how much raw materials we need, marketing promotion…etc.  They can have a direct impact on costs and revenue to the organization. For example, giving a customer a discount may increase revenue but may not help the bottom line.

How do we make business decision?  Based on experience? guessing?  Unfortunately, not all of us have the right business instinct or it would take a long time to build up such experience.  How can we make a better decision?  Information!  We need strategic information to make a better decision!  Accurate, consistent, timely information can lead to lower cost, higher revenue and better customer satisfaction.

Reducing Costs

Once the information backlogs are eliminated, we can easily identify the root causes of any inefficiency in our operations and take prompt action to fix the problems e.g. building model to optimize business processes; identifying wasted resources and reducing inventory costs.

Increasing Revenue

“Who are our best clients that delivered 80% of our profits?”, “What are our most effective marketing channels?”  Such information allows us to micro segment our markets, gain an edge over the competition and empower the sales force to focus on high profitability customers and products.

Improving Customer Satisfaction

We can dramatically reduce the time to answer our clients’ questions, better understand our customer behaviors and hence provide customized services and even provide information to our clients so that they can make smarter decisions.

Challenges

In God we trust; all others must bring data. – W. Edwards Deming

By now, we should understand the importance of information.  Unfortunately information is not always readily available to every decision maker.  They have to wait or even guess!  What are the challenges? We have many business applications; e.g. finance, human resources, customer relationship management, supply chain, manufacturing.  They contain vast amount of data; however, data is not information.  It is a long way to transform data into information!

Garbage in, garbage out

Data quality is always the biggest challenge.  Poor data quality can jeopardize decision makers’ ability to make a better business decision or even make the wrong decision.  This problem could take a lot of effort to fix; so it is far better to implement a proper data strategy to avoid such problem.

Too slow

Each system generates many reports that deliver a lot of information to the decision makers.  However, if we need extra or the latest information, then we have to rely our IT department to extract more information for us.  We all know how long it would take!  And it may take a few cycles to understand where the real problem is.  It is simply too slow and we don’t always have the time to wait!

Inconsistent

Some of our power users know how to use Excel or other reporting tools to extract data directly from the enterprise applications.  Great!  They can retrieve the latest information in real time.  However, it leads to another issue, i.e. consistency.

“How many people are working for our organization?”  It is a very simple question, but you may be surprised to find out how many different answers you can get; e.g. should I include employee with disability pay / maternity leave? how about contractor? If an employee who works 3 days a week; should we count 1 or 0.6?  Even such simple question can lead to so many different, inconsistent results.  Imagine what you would get if we want a more sophisticated report!

Lack of 360 views

Our organization has implemented more and more systems over time and each system has captured different data.  However, only a few employees may have access to all data; our database administrators.  For example, account manager may only have access to sales data but he may like to gather other information, e.g. customer satisfaction, inventory level, and other market information before he heads to his client’s office.  He may send an email to his department head; and his department head would forward his request to other department heads who would subsequently pass the request to their analysts.  Good luck if you can gather all your information in 2 weeks time!

Business Value of Data

What is the business value of data?  It is enormous!  Data in our system is the gold mine in our office.  It would help us to reduce cost, increase revenue / productivity and improve customer satisfaction!  What is the business value of a loyal and satisfied customer; especially in today’s downhill economy?

Ask yourself the following questions:

  • Do you find it takes too long to get the information that you need to make a business decision?
  • Do you receive consistent information, no matter who prepare the information?
  • How many places do you need to go to get the answer?
  • Do you know where to look for the information?
  • How often is data rekeyed?
  • How much information do you need to manually aggregate?
  • How much data needs to be manipulated to get to an answer?

Do you need a better reporting system that can transform data into a good mine?  Most companies said “No, we already have a management information system.  If it ain’t broken, don’t fix it”  However, a well implemented business intelligence solution could give your competitive advantages and empower you to grow stronger!  So act now and benefit tomorrow!

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!

Are you a decision maker who often do planning, scheduling and optimization?

Our business world is extremely complicated and anyone who did modeling before, would tell you that even the smallest model can have up to 100s variables and constraints; medium size models is in the range of 10,00s to 100,000s.  This is just too complex for “pencil and paper” and manual methods.  Fortunately, Microsoft Excel has offered a powerful optimization tool since 1991, Solver add in.  It has helped many decision makers to do business planning, project scheduling and resources optimization.  If you haven’t heard about Microsoft Excel Solver add in, you can find out more from my previous blog.

It is easy to learn Microsoft Excel Solver since it is tightly integrated with Excel and it uses Excel structures, e.g. cells and cell references that you are familiar to formulate and implement optimization models.  I managed to coach a lot of analysts to build their first model within couple hours.  It is also very powerful and flexible that you can use it to handle financial model, supply chain model and even project scheduling.

However, spreadsheet model has a few disadvantages.  It is difficult to integrate with existing legacy systems, implement version control and identify where the changes are, document and debug the model.  Excel Solver is a great tool for ad hoc modeling; however if we want to migrate it to the production environment, there can be many challenges that we have to resolve.  How can we share our model with other colleagues and not let them accidentally change it?  Can we automatically feed the results to our finance / supply chain system?  How can we document the model so that other colleagues understand our works?

Microsoft Solver Foundation (MSF) is the answer that we are looking for.  Key features include:

  • Excel add in – a complete modeling environment that allow us to develop models.
  • Application Programming Interface (API) – allows us to integrate our models into the legacy systems.
  • Optimization Modeling Language (OML) – an equation based modeling language.
  • Solver plug-in – we can develop or purchase external solvers that can hook up to MSF.
  • Parallelism – MSF will manage all threading, many-core, synchronization, and scheduling around the solvers.
  • MSF has a collection of solvers and it would automatically determine the best solver for us.
  • Simulation – We can use stochastic programming to run Monte Carlo simulation.

In this blog, I would only focus on Excel add in which includes:

  • Modeling Editor – simplify the modeling experience
  • Excel Call Binding – bind data to and from the model
  • Deployment – once we develop and test the model in Excel, we can deploy it directly into C# application

I am going to use an airline route allocation simulation model to demonstrate how MSF Excel add in works.  The objective of this model is to minimize operational cost.

It is a small model, so not a lot of data.

clip_image001

  • First, we bind data from an Excel sheet to the model through the Parameters tab.  You may notice that we can now specify description and the type of parameters, e.g. Integer, Real, InterNonnegative.

clip_image002

  • Then we bind the results of the model being solved to Decisions tab.

clip_image003

  • So far we don’t need to learn anything new to define Parameters and Decisions.  But we now need to learn how to use Optimization Modeling Language (OML) when we define the business goals in Goals tab.  OML is designed exclusively for modeling; it includes identifiers, comments, string literals, Boolean constants, and arbitrary numeric literals.
  • Our goal is to minimize operational cost which includes 2 components, shipping cost and refund.

clip_image004

  • We add restrictions to the decisions in the Constraints tab.

clip_image005

  • As we defined the model through various tabs, MSF Excel add in would automatically build the model for us and we can review the entire model in OML format at Model tab.  We can add extra descriptions to the model so other users can understand how it works.

clip_image006

  • Once the model is ready, we can click the Solve button to start the solver.

clip_image007

  • And the results publish to the specified range.

clip_image008

  • We can also review the output log.

clip_image009

  • We can deploy the model to production environment and integrate with other legacy systems after we fine tune it.

clip_image010

Microsoft Solver Foundation is a great product; we can integrate our models into legacy systems and take advantage of our multi core CPU.  We have to learn OML; however it is a simple modeling language and quite straight forward to manipulate.  So if you often need to build business models to optimize profit, schedule and other resources; you should download its express edition and see what it can offer you.

Andrew Chan is the owner and founder of ALG Inc.

We help you to make better and faster decisions!