Home » information technology » databases

Category Archives: databases


What is a DSS (Decision Support System)

decisionThe Decision Support System (DSS) is a very specialized application. The decision involved can be any decision a person or manager should need to make. Support means that this system will do the tedious, time-consuming, number crunching, information gathering parts of knowledge-collecting, allowing the user a full and impartial view of the facts from which to make an informed decision. Your spouse is a Decision Support Person – s/he listens to your ideas to move to Albuquerque, then says, “But, dear…you are allergic to yucca plants!”

At one time if an executive wanted information to aid his decision, he had to go to a computer operator to have the operator phrase the questions to a mainframe by punch card. Now executives keep their own computers and develop their own software. Now query languages are easier to learn, and applications software is a standard element of the desktop. There is a natural progression from EDP to MIS to DSS. An MIS system was used to help make decisions simply because the regular reports and metrics showed trends, successes and failures, as long as the manager using them remembered the results of the past ones and the points of change. But they couldn’t ‘predict’.

What does a person have to do to make a decision?

If the decision is more important or more complicated, one may need to devote more time and effort into making that decision. So let’s take a real-life example – Mrs. A got a good job offer to work in Maryland for a “Beltway Bandit”; her company was closing the local offices. She can either move to Maryland or stay here and get a different job. Her husband has been thinking of opening a business, and has started inquiring about office space and equipment. Mrs. A needs to do more than list the pros and cons; she needs to weight these pros and cons according to how important each item in the list is.

The SDLC (life cycle) of a DSS may follow these steps:

  1. Determine the decision that must be made.
  2. Determine the facts (knowledge) that need to be collected that might affect the decision.
  3. Collect the knowledge into a knowledge base.
  4. Determine the processes that need to be calculated from the facts.
  5. Run the processes on the knowledge.
  6. Determine the best way to present this derived information to the decision maker.
  7. Often, as a sort of post-mortem, people look at the collected knowledge and see other decisions that could use support by applying different processes.

One of the challenges I give to students who are learning spreadsheets is to write a spreadsheet that replaces their check register. While it sounds simple enough, the only way to do it is to break down functions we normally do “without thinking” (heuristics). How do we make decisions when we enter something in a check register? We look to see if we wrote a check or made a deposit, in order to know whether to add or subtract the amount from the running balance.

Codewise, we’d have to create a field that says ‘this is a deposit’ or ‘this is a check’, then write an IF statement that checks that field and then decides whether to add or subtract. Very awkward. Wouldn’t it be easier to understand that the spreadsheet recognizes a blank cell as zero? So if it adds everything in the “deposit amount” field and subtracts everything in the “check amount” field at every line, wouldn’t this work?

To support a decision we must also simplify until we understand the tools we are using, the value the decision maker puts on certain results, and the extent to which our system can help.

Today’s CIOs must understand business as well as technology. Pfizer resolves this by having two Project Managers on a system development team – one from the technology group, and one from the business group.

Take a look at your organization – where are decisions being made? Are they to influence planning, organizing, directing, coordinating, forecasting or investigation? Often we don’t recognize “decisions” unless it’s obvious, but actually each of these functions involves the analysis of knowledge and a decision to be made. Whenever a person develops a spreadsheet or a table, s/he is trying to organize knowledge in order to come to a decision – every “what if”; every “let’s look at the big picture”; every time someone compares two things. One only analyzes things when one wants to make a decision about them.

Do we have to classify and define such ethereal concepts as knowledge or decision power centers? Yes, viewing of types of knowledge and decisions, etc., helps us to look at the possible ways in which computers may assist decision making. The extent to which a computer can help a manager plan, organize, command, coordinate or control depends on reaching a more detailed and precise understanding of the functions and their interrelationships.measurement

An anecdote about knowledge: When my father was working at Sperry (now Unisys) he had a little black box that had blinking lights running on batteries. He’d take it into staff meetings, and as he spewed out statistics of the state of the company, he’d pause, peer at the box, then recite the statistics. Everyone thought he had gotten his hands on a remarkably tiny mainframe, chock full of information. This was long before flash drives!

Most people don’t recognize “knowledge” per se. If you have ever had to graph a data flow diagram, as opposed to a process flow chart, you have experienced the dilemma of identifying a single type of knowledge. How can a single piece of knowledge shift an entire organization? Think back on your workplace – changes in personnel can completely realign the priorities (as when a new CEO takes over an ailing company). A shift in industry can greatly affect the knowledge needed and handled, as when networking made personal computers a standard business accoutrement, which caused IBM to do a reversal on their whole production scope. Changes in the clientele of a business can alter the knowledge base. With the economic crunch, many companies that had been diversifying had to reverse the process and start divesting – a decision had to be made as to where the company wants to go and what to let loose.

While we easily say “a computer could do this” when it comes to crunching numbers, or reporting on data, it is much harder to recognize how a computer can come to a decision. It sounds like science fiction.

In reality, computing systems rarely come to decisions. But they can be a tool for coming to a conclusion or decision yourself.

An example is a program a friend of mine just built in XML – it’s a GUI for the intranet which lists all the personnel of the CRi department, whether they are fulltime employees or contractors, who they answer to, and how long they have worked there. The purpose of the GUI – the department has been hiring contractors without restriction whenever a supervisor requested them. So this application is taking specific HR information and creating an ‘organization chart’ of sorts to help them get a view of the sub-departments, balance out the roles and establish a personnel budget.

Let’s look at a decision – what to have for breakfast? Some of you may have the privilege of just sitting down to a table festooned with goodies prepared by another. OK, now for reality – what are the influences on our decision? Whether you have to watch your cholesterol. Whether you have to watch the calories. How much preparation time do you have. Whether others must also be fed. And, of course, what’s in the refrigerator.

A manager must play interpersonal (getting one team member rolling so the other team members can get to work), informational (notifying users that there will be server down time) and decisional roles (where shall we build the new warehouse?). Interpersonal work is rarely a computerized function, whereas informational work is almost always supported by computerization. Actually, a lot of decisions use computer-based tools to help, without even realizing it – writing a white paper on the merits and disadvantages to a decision, developing a spreadsheet with the variables of a problem and changing the values to see the results, or extracting data from factory machines and downloading it into a database. I recently took a bunch of sales records that came in an ASCII (unformatted text) file for a pharmaceutical company, converted the data into tables, and did a variety of business analysis reports on the profitability of the company and the possible areas of change (including the outcome of those changes).

An example of a team decision maker – the President and his cabinet. The cabinet members are supposed to be experts in their fields. A good manager also works this way, trusting his/her finance people, HR people, etc., to input the specific knowledge of their specialties, so that the manager can come to the “informed decision.”

There are applications out there which actually evaluate a decision maker’s value system, to determine which shift s/he has. The manager answers a series of questions such as “Which would you prefer to do – buy a daily lottery ticket for $1 which pays out $50, or a weekly $5 lottery ticket which pays off $500?” The answer is given a numerical value, and the manager’s willingness to take risks is calculated. Then the DSS designed for that manager knows which solutions to drop and which to include, depending on the risk factor.

The design of a DSS depends on the decision context and decision type, as well as the nature of the decision maker. This is where you need to concentrate. You must also watch for sources of knowledge, such as databases you can tap into for information, rather than relying on input each time, which is time-intensive.

change controlWhen designing a DSS, try to incorporate as much flexibility as possible, rather than working toward a single decision. Make it as user-friendly as possible. Be sure it uses reliable materials – remember the GIGO saying – Garbage In, Garbage Out. Determine the What, the How and the Why of any knowledge you want to incorporate.

Ad hoc reports encouraged the development of DSSs. A lot of times a “DSS” is developed on the spot for support of a decision, but it is simply not recognized as such. For example, I datawarehouse for a particular clinic, with monthly, quarterly and annual analyses generated for the agencies which support the clinic. Every once in a while the agencies ask for additional information, to view the effectiveness of this or that – for this purpose I have a database called “ad hoc reports”, which links to the general knowledge base used for the other reports; it is there that I develop specific queries to look at the data from a different angle to generate these ad hoc reports. It’s a great deal faster than the clinic dragging out all the old reports, or all the files for their 500 active patients, to figure out the answers. These reports are used by the funding agency to develop guidelines for its clinics, as well as determining which clinics get which funds.

The cost to develop an ideal DSS often outstrips its utility for a one-shot deal. It would only be feasible if it was flexible enough to handle a series of decisions. For that to occur, it might be better to develop an Expert System and query from there.

I’ve developed DSSs with a collection of variables – The user selects/fills in whatever variables the user wants to use, then selects the type of report s/he wants to generate. To allow this amount of flexibility, the DSS gets pretty darn large – a single module of code may be over 4 MB in size.

There are managers who will ask their direct reports for decision support development, regardless of the ‘cost’ in time involved. But in reality, there is an ROI (return on investment) – time saved in research, accuracy of information, access to information bases, speed of solution proposals, the opportunity to look at things from a different angle, and it can be used to stimulate the decision maker’s thoughts about the problem. Recognize that it is currently not possible (and may never be) for a computer to use imagination; that’s why it is preferable to create a support system than a decision maker – let the manager earn his/her Big Bucks by adding creativity to the results from a DSS.

How does a DSS “communicate”? It is that which separates an interactive model from a static one – questions to fill in and/or answer. Take an online survey on your shopping practices. It not only asks you a series of questions to answer, usually multiple choice, but it then looks over your answers; if you fail to answer, or select more than one when only one is acceptable, the survey stops, and notifies you of the error in your input; once you answer it correctly, it continues. If you answer a particular way on a particular question, more questions or a text box will pop up for expansion of your answer. At the end, it looks at your answers and, following a set of predetermined rules, comes to the conclusion you should get a larger car and presents its findings to you. Input – clarification – evaluation – presentation.

Text-oriented DSSs appear at first to be simply libraries, but that is no longer true. Two examples of these are two companies I’ve worked with.

The first is a company that emerged originally to assist General Dynamics in preparing for a lawsuit. Designed by GD employees, the object was to scan the documents involved and create a database which indexed each document (content, keywords, whose signature is there, names, dates, etc.). The attorneys could then find the documents they needed quickly. Without this system, I’ve seen attorneys actually roll an entire hand truck of boxes of documents into trial. Save a tree…shoot a lawyer. This was so successful that the developers broke off on their own and set up such databases for other attorneys.

The second example is Pfizer’s electronic submissions for new drugs to the FDA. While they still need to truck the 2-3 semis of documentation to Washington, it is all scanned and indexed, so an FDA investigator can ask for any single or related set of documents and they are generated from the servers sent with the documentation. This has changed acceptance of a drug from the original 2-1/2 years to a matter of 3-4 months – generating literally tens of millions of dollars of revenue.

While the term Decision Support System is not that well known, they are great fun to develop and it’s amazing how handy they can be.




developing database applications – forms

formForms are objects in relational databases that we old-timers used to call screens. In Microsoft Access, they can be built automatically using the wizards, based on queries or tables. But they can also be created independent of either.

Basic forms are a user-friendly way to enter data into a table. For this purpose, I will sometimes use the form wizard and then modify it. The form wizard builds a linear form (one field under another.) To do this, select the table involved and click the form wizard button on the tool bar. The wizard will set up the fields by what data type is involved. For instance, a yes/no field would show up as a checkbox; a lookup field will show up as a drop-down list box. Certainly in the beginning I would recommend this approach, until you get familiar with the toolbox. Check to be sure the field names (the labels on the form) are clear – they can be edited because they are simple text. The wizard doesn’t always account for long labels/field names, so check that as well; you can widen the area of the label.

Forms can be very fancy, including calculated fields (create a text box, and instead of giving it a data source, type in a formula), photo backgrounds, callouts for macros or other forms… it can really knock your socks off. When working with a client who is tied to the trees (accustomed to paper forms) I use a simple display which looks almost exactly like the paper form. If a client has a data base with a huge number of fields, I will often make them a series of forms for related or rarely-entered data, reached either by a button or through a menu. I don’t use the switchboard feature at all; it’s limited, cumbersome, and a bear to edit. Instead, I have an opening screen with my copyright information, and a button which opens a menu form; I use buttons to open the needed forms from there.

Sometimes forms are the best way to create a query, because you are not tied to the simple questions a query can handle. In a query, you can put a question into the criteria, which shows up as a message box when the query is run, asking for data which is then entered into that field. For instance, the message could be “Please enter vendor’s name”; the user types in a name and hits <Enter> and the query is then run with that criterion. The biggest problem is that the user needs to know exactly how the vendor names are typed – and just what they are. Ifyou use this option, give the user advice on how to enter the information (such as “mm/dd/yyyy”).

We can get around that by creating a form which lets the user select the name or information from a drop-down list. You’ve all used these lists at least once somewhere in your use of applications, or filling out information on the Web.  This is one of the tools in the tool box. To see and play with the toolbox – click on the button that looks like a hammer and screwdriver when in the design view of a form. Try using all the tools — you can create labels (plain text), unbound fields, drop-down lists, forms within a form, control buttons – it’s a veritable playpen. And getting accustomed to the form design, you will find report design much easier.

So how do you create a variable? I create a base table I name Parameters. In this table I create any variable fields that would be appropriate. For instance, if the database were a contact management system, the fields might be report title, a numeric field, first and last names, city. It’s a handy table – you can add fields you need later on the fly. Only the first record is used (unless you want multiple reports for a special reason, like a mail merge); this is achieved by simply having the field get populated instead of appending the information – the first record keeps getting overwritten. If you use this method, be sure to delete the information at the end of the process.

Create a form based on the Parameters table and ask the user to fill in or select the information s/he wants a report on. Then create a query based on the table with the information, and either link the second table to Parameters by a field or user the Parameters field(s) as criteria:  =[Parameters].[fieldname]

Now add a control button to the form that says something like “execute”. That button can be configured to open the query via the wizard.

This way you can customize a report to the needs of the user. This can get very complex – I once had a button that used four MB of VBA code to generate the report!

Last but not least, you can use forms as message boxes or Help screens – simply don’t connect it to a table or query. A message box can be opened by VBA code. A help screen might be a control button on another form.

How to find what you need. Some of the forms you have could take forever to go through to find what you want (do I already have this movie? Is that book loaned out? Is this TV already listed?). These are forms which display a soon-to-be-large database table one record at a time.

One method I use, especially in my address book and my WWW listings, is a search. Place the cursor in the field you want to search in (say, last name), and then select Find under the Edit menu — or be lazy like me and click the binoculars on the toolbar. Type in what you want to search for; the field to be searched is already selected;  choose either the whole field (default) or any part of the field – my favorite because it saves typing time and I might not be sure of the entire field contents; click Find Next until you find the record you are looking for. If the whole table is searched without finding what you are looking for, a message box will appear telling you so.

Another great feature is a filter. This comes in very handy when you need to check information on a group of things. Let’s take my WWW sites database, which lists the sites I’m registered on [ok, I’ve been surfing for a looooong time], the URL, the type of site, the username, password and e-mail used. I want to find all email addresses which have “aol” in them because I have cancelled my AOL account and all its screen names; I want to contact all these sites and change the email used. So I place the cursor in the email field and search for the first instance which had “aol” in it (searches and filters are not case-sensitive in Access). When I find the first one I highlight only  the “aol” part of the email address, then click the filter icon (looks like a funnel on the toolbar). Now you will notice that the number of records has decreased significantly; that’s because only those with “aol” in the email address are being displayed, and they may be navigated as you would the full list. When you are done, click the ‘pressed’ un-filter tool to get the whole listing back.

One warning about filters – if you go to close the form and are asked if you want to save design properties on the table, do not click Yes, or the table will always open up filtered!

I use the A–>Z, Z–>A sorts constantly — to find out what was done on a certain date, what fields have blank entries (complicated queries can drop records because there is missing information in one of the fields), and a dozen other things. I tend to use the sort in the table directly; and I will hide certain fields in the table so I can scan information from the sort – this is akin to hiding columns in Excel. Again – be careful not to save the design when closing.

More …

When you have a form that has an auto-number field, don’t put that field on the form — it confuses a lot of users as they try over and over to fill that field. Or remove its tab index so that they don’t tab into it.

Huh? Tab index????? Every field in a form has a Tab Index (properties / other tab). This tells the order in which the user should tab through the form, and starts with zero. So tab index 0 should be the upper left hand corner, since that’s the way we read in this country. I can tell when a developer didn’t use a wizard (or seriously changed the form if he did) because the tab indexes are all messed up. They are determined by the order the fields are placed on the form. To check it, just place the focus on the first field and start tabbing. Lost the thing? It backed up three fields? Gotta fix that. To fix it, open Properties / Other on the first field. The tab index should be zero. Then click on the next field — the properties will now show for that one – it should be one. Keep clicking through till you find a number in the wrong place, and type over with the correct number. This needs to be done in the desired order because Access adjusts from the change, giving that wrong tab index to another field and readjusting the sequence.

Justification. My justification for writing this tutorial is…. no! The other kind — like in Excel, the fields justify left for text and right for numbers and dates. You can change the justification of any field and any field label just like you would in Excel. Saves searching a text box to find the data!

What if you have a form where you just want to discourage users from changing the data? Make the background of the text box holding the field values the same color as the form background. Top it off by removing the border.

If you have a form for adding a record, you will never stop users from overwriting the first record by accident. One way to handle this is to have a datasheet view, where the user can see the next available record. But most records take up a whole form for fields (unless you make your user scroll right forever). So make the form data-entry-only. To do this, create a regular data entry form. Then go into its properties and go to the Data tab. At the bottom you’ll see a property called Data Entry, at the default No. Change it to Yes. Then when the form is opened, the user will only see an open record to fill out. The user will only be able to see those records created during that session of having the form open. Another approach — you could view all the records, or you could click a control button called “Add New” — and you are faced with an empty form. As with all design features, there is more than one way to skin that cat.

If you have a text field as a Lookup data type you probably noticed that when you put it into a form, it automatically becomes a combo box. I find this unnerving (OK, I don’t trust users a whole lot.) Sometimes these are fields that might change frequently for one record, but that’s rare. For this reason I prefer to make a base table with the lookup list in it and refer to that in a combo box tool when I want that ability. This also allows the user to type in her own information if it isn’t available in the list.

You may want to create an unbound field which does a calculation for you. I do this when I record my gas purchases. I enter the odometer reading and the number of gallons. The calculated field then tells me what my miles-per-gallon year to date is. Lets me know when I need to get a tune-up! For clients, this might calculate commissions or tax.

Last but not least the Parameter Table. This is a one-record table to which you would probably add fields as you deem it necessary. It is one method for saving a variable. I usually start out with StartDate and EndDate fields, for queries  which need date brackets or a single date selection. I also like to have a ReportTitle field for regular reports so I can change its title each generation — as in “BRS demographics, First Quarter 2006”. Anytime you want to change a parameter in a query, it is better to let the user select from a list than to force them to memorize the item, or to change the criterion inside a query. Create a form based on the parameters table which requests the user to select the criteria (parameter) from a combo box. This gets stored in the appropriate field in the parameters table. Add a control button which opens up a query which answers your questions, using the parameter table’s field as the criteria. Badda bing, badda boom…




COPYRIGHT 2001: BONNIE-JEAN ROHNER. All rights reserved.

%d bloggers like this: