Importing Your Exports

You know that you can count on Monarch to mine data from your reports. You might even be using Monarch to combine data from various sources. But have you considered using Monarch as your main database program?

Developing database applications is usually the realm of programmers and systems analysts, but you may well have the tools and the knowledge to build an entire system yourself. And rather easily at that!

Simply put, a good database has only a few fundamental roles to play:

• It provides an input mechanism to allow for new data and updates to existing data.
• It handles the data safely so that the database is trustworthy.
• It outputs reports to let you see the data that is stored.

Let’s say that your big expensive accounting system does a great job of storing gobs of transactional data in nitty-gritty form, but it does a lousy job of giving you all of the key performance and operational metrics you need to help manage your business. What are you going to do?

We Have the Technology

You could decide to invest heavily in the latest and greatest cutting-edge business intelligence software to “leverage your existing information systems”. But you’ve got Monarch and Excel, so you think better of that and decide, for now, to see what you can do yourself. After all, a dashboard reporting system (link to other post) alone might well be enough for your organization.

Depending upon your needs, your reporting system will need information on at least a few different areas of your operation. Wouldn’t it be great if you could store data on all of these diverse topics in one place? It sure would make using it easier, and it would probably be easier to put it all together in the first place.

Monarch makes it easy to manage this by letting export data to Microsoft Access databases. Not only can you create the original database file with Monarch (you don’t even need to have Access installed), but you can add new data to the database file you created previously.

Your system is bound to create a slew of transactional detail reports on a variety of the topics you want to include in your dashboard report. The first step is to create Monarch models which summarize these reports, keeping in mind how you want to display the data in your finished product.

Now that your models are made (wow that was quick!), you need to export a summary, or a table window depending upon your needs, for your first topic to a new database.

Next, export the data for remaining topics with your library of models, adding new tables within the existing database file for each topic. You can have a large number of topics, or tables, in your database, but you should try to have only data specifically required for a particular topic in that topic’s table, keeping other data in another distinct table. Database experts refer to this practice as normalization.

What this really means is that you wouldn’t normally keep your customer’s name, address, and phone numbers in the same table that tracks how many widgets they bought; you’d probably only keep their unique customer number in that table. If you need the address, you can go read it from the customer information table based on the related customer number.

Now you’ve created a database and populated it with a lot of information. All well and good. Now what?

“I’m no programmer”, you say. “How do I use this thing for any productive purpose? I can’t write any database reports.”

The Right Tool for the Right Job

Now you’re glad you elected to install Monarch Professional, because this version of Monarch can connect directly to databases to mine data and isn’t limited to regular report files.

The reports that you run and print from your accounting system are simply printed versions of database queries. The database programmer put together a way of extracting specific information from the database, and (hopefully) laid it out in a manner which makes sense to you.

To extract data from your database you just tell Monarch to open the database and select the table from which you want to extract data. Now with this data available to Monarch you have a number of choices. You could export it to another format to share specific data with someone. You could further summarize it, if required.

But one of the most powerful things you can do is combine that data with other data from another table. This is really exciting, as it will allow you to do things your existing system just isn’t designed to do.

Imagine being to able to analyze transactions based on customer that aren’t normally based on customer data. Perhaps your system gives you product profitability, but you have no idea how profitable your customers are. Tie those topics together in your database, and you’ll know. Perhaps you want to see your profitability in a different light altogether, like by the customer’s city, state, or even country.

What If?

Maybe you are envisioning a reorganization of departments and want to see how historic information would be impacted by such a move. Build a table that maps the old departments to new departments and you can see the impact of the proposed change on all of your other topics by bringing the re-org data into each of those other topics for which you’ve collected data.

How do you connect the tables without programming? You use another feature of Monarch Professional, the external lookup.

Let’s consider the customer data example. We’ll go so far as to assume that the limited sales analysis in your main system gives you product profitability by customer number (who bought what and how did we do on it?). However we want profitability by customer country because we’re running a global business here, and we want to know where to focus. It’s a big world, after all.

When we build an external lookup we’d start with the product profitability data. Then we’d select the customer data table, and tie the customer number from the product profitability table to the customer number in the customer table. Within the customer table, we’d now select the Country field to bring into our existing data.

Once that’s complete you could analyze country profitability by creating a summary based on customer country.

This is the foundation for building a complete database system, complete with customized views of the data and specialty reporting, that you can build with Monarch; without a single line of program code.

All you need is a model file for each customized view of the data you require. Use your imagination and you’ll be able to slice and dice your main system’s data in ways that system only wished it could.

IT’S ALIVE!

You’ve designed and built a database (what database types call the “back-end”), based on your specific needs, that generates your custom queries, or reports (part of what they call the “front-end”) purely by both exporting from and subsequently importing into Monarch.

Now you understand why I believe that creating and using your own database really allows you to excel with Monarch.