See Diagram - Best Plan for Review - Big Logic Based Reports

Hopefully a Diagram is worth a thousand grammatical attempts to explain.
My intent is to go with Plan 9 rather than my current Plan A.
Your comments would be appreciated.
Do you think Plan 9 will reduce the report to under 5 minutes (as Plan A will probably take two hours).

Trying to run an Access Query with formulas is slow - Will a Make Table then an Update Query on the local tables with the same formula be faster?
Each Cell (Row/column) processes a Select Where query, then complex logic to evaluate the result into a True/False.

The current project is developing a Rules Based Engine.
Each Column is evaluated with a Rules Function. (40 Rules = 40 columns).
Then, the next Row does it all again.

This effort is going great until a report with over 100 records needed to be created. The test with just 4 Rules (4 columns) and 1,000 records was taking over 1 minute.

As much as I want to write 40 very complex Stored Procedures on SQL Server, my feeling is that 800,000 calls across the network to execute Stored procedures (800,000 times) will still be slow.

This alternative is to use Make Table and then perform an Update Query using the vba functions. It will be less calls across the network.
Your comments on if this would work to reduce total time would be very appreciated!

Sorry, I don't get it. You have abstracted it to the point where I can't tell if it makes sense. How about giving us some real world data as input and what you want as output. You've told us how you want to process it but without the two ends, your explanation is just words.

Rx_ said:

Hopefully a Diagram is worth a thousand grammatical attempts to explain.
My intent is to go with Plan 9 rather than my current Plan A.
Your comments would be appreciated.
Do you think Plan 9 will reduce the report to under 5 minutes (as Plan A will probably take two hours).

Trying to run an Access Query with formulas is slow - Will a Make Table then an Update Query on the local tables with the same formula be faster?
Each Cell (Row/column) processes a Select Where query, then complex logic to evaluate the result into a True/False.

The current project is developing a Rules Based Engine.
Each Column is evaluated with a Rules Function. (40 Rules = 40 columns).
Then, the next Row does it all again.
effort is going great until a report with over 100 records needed to be created. The test with just 4 Rules (4 columns) and 1,000 records was taking over 1 minute.

As much as I want to write 40 very complex Stored Procedures on SQL Server, my feeling is that 800,000 calls across the network to execute Stored procedures (800,000 times) will still be slow.

This alternative is to use Make Table and then perform an Update Query using the vba functions. It will be less calls across the network.
Your comments on if this would work to reduce total time would be very appreciated!

Click to expand...

From what I can understand, neither of your designs seem quite "right" but I also can't make sense of your test results or the figures you are quoting. Why would you want to make 800,000 calls to a procedure? Sounds like you are building a sequential row-by-row process when a more set-based approach ought to be more appropriate. I suggest you should aim to replace this with server-side code that can accomplish the same process in one call or a few calls.

Your benchmark of processing 1000 rows in 33 seconds doesn't seem right either. 33 seconds is a lot of processor cycles. SQL Server can perform complex queries with millions of rows in 33 seconds. Either your hardware is utterly crippled or your rules are incredibly complex or (I suspect most likely) your code is doing this in a very inefficient method.

Maybe if you post an example rule or query someone could suggest a better way.

I didn't even try to understand all those diagrams. If the Database design is of similar complexity, then no wonder you have a problem. However to be fair, it may not be your design.

I have had situations when there has been a large quantity of data that required complex calculations. Mainly to create Graphs.

I found that the speed increase by first transferring data to a local table was considerable.

No calculations were made before transfer and the new tables were as much as possible denormalised.

Hope this is of some help.

EDIT

renormalised was changed to denormalised.

Thanks everyone for the inputs. Will report the results of the design soon. RainLover's example is very noteworthy.

There is a little confusion, absolutely on my part about how this is part of a Business Rules Engine. There is a library of meta-data business rules and actors that basically becomes a library of code with multiple steps of how they are used in the process.

The expert in the field wrote a book How to Build a Business Rules Engine - Malcolm Chisholm. In my defense, it would be difficult to describe the 484 page process in a short post. I am doing a really lousy job of trying to explain this process.

The nice part was, Malcolm used MS Access in his example DB.
The bad part for me is, his book only covers a fairly simple Reimbursement Report.
My Rules cover a very complex set of Compliance across Federal agencies, State agencies, local agencies, and private laws.
A simple example of the trading software I use to maintain would be a business process where a customer's order could not be completed by electronic-stockbrokers until it was immediately match with a counter-party, and for all the accounting and other processing to be done in real-time. Malcolm Chisholm describes this as straight-through processing, or STP. Within each process however, there will be a number of steps. These do fit together in an STP-like manner. Each step involves a different set of coherent actions that usually involve a different set of actors (the term given to participants in a process step, (and so on).
Reference: Malcolm Chisholm. How to Build a Business Rules Engine: Extending Application Functionality through Metadata Engineering (p. 67). Kindle Edition.

The truly exciting part for me is that by using SQL Server, Access and Excel, the process of meeting with Subject Matter Experts across the Enterprise has been abstracted to their terms to get design feedback at an amazing rate. This has allowed me to rapid-prototype the logic in Access quickly. As regulations change, the long-term maintenance cost is critical.

After finishing today, I will attempt to be more clear about that balance between data pulls and calculations. This might help determine when pulling data to the client is worthwhile.

Thanks to everyone for the feedback. It was important to consider all options. All comments were a great help.

RX

I just noticed a typo in my post. I used renormalised when I meant to use Denormalised.

This means you can throw out all the rules to do with table design and simply create Tables that are built for speed not to save space etc.

And strictly speaking you should be creating a new Database and new Tables that you link to the front end.

When you create a new import then delete the Database and create a new one.

This will avoid bloat. It is at the moment not important. You can do this later once you have everything working.

Spot On, that is exactly what is going on right now.
Preventing Table Bloat.

Using the SQL Server View to join tables and organize the PK the same for speed.
Then, deleting the old local tables, create new local table directly from SQL View.
Run the layers of logic code to update the local tables for metadata logic in last empty columns Vertically, rather than horizontally.
Then joining the updated local tables to harvest each local table's metadata result columns. Perform final logic summary.

Thanks for that valued suggestion. It is great to have validation from a respected peer.

Rx

Would you mind explaining to me the difference between Metadata and Data. When I did my studies we never used the word Metadata.

A lot has changed since then.

Thanks for asking a good question. (Added attachment)
It is not a simple question. Basically, it is data about data. This book names all Queries, Rules, and tables with a prefix of M_ I prefere R_ (after all, it is half of Rx LOL).
I have picked a few choice quotes, let me know if it answers your question. I read this book over a weekend then modified the architecture to work better with my specific implementation. In the "old days" (i.e. Cold War) we use to actually get very involved in this sort of thing. Since then, I have been very fortunate to apply some of these concepts to Health Care and Environmental applications.

I would bet that most advanced developers actually have a mental model of metadata. A book like this just helps put a face and name to what we often end up doing to solve day-to-day objectives. That said:
Please allow me to quote:

The Business Rules Repository
A repository is a database that stores metadata. Metadata is loosely defined as "data about data." As noted in Chapter 3, this definition is rather poor, but is very widely accepted. Unfortunately, there is not an easy distinction between data and metadata. The reality is that metadata is any data that the user of the system does not manage. In other words, a business application manages a set of data, which would in fact exist in the absence of any computerized system. Metadata exists for this business data, such as definitions of business terms. Again, a lot of this kind of "business" metadata exists even in the absence of a computerized system. This metadata may be needed by staff in order to manipulate the business data, but the metadata is not itself processed. When a computerized application is introduced there is a great deal more metadata to contend with. This new kind of metadata is everything that describes the computerized application and how it works. Again, this metadata may be needed to understand how to run the application, but is not actually processed by the application-or, at least, processing it is not directly relevant to the business area covered by the system. Only the processing of data is relevant to the business area. So very roughly, information that a user needs to manipulate is data, and all the other information that a system stores and manipulates is metadata. The data is stored in the application's database. Metadata is stored in a database that is by convention called a repository.

Application Database Metadata
Perhaps the most fundamental part of the Business Rules Repository is the application database metadata. This is metadata that describes the structure of the application database. A database has a structure that consists primarily of tables containing columns. The business rules engine has to understand this structure so that it can access data in the database and update relevant data as required. There are also relationships between tables. These are extremely important for a rules engine, because they dictate how the database must be navigated. Beyond this, there are more subtle aspects of the application database that are necessary for the rules engine. Subtypes are extremely important. These are subsets of records and columns in a table that behave in a specific way. They are not always recognized as such either in data modeling techniques or in application development. Yet they are critical for rules engines because many business rules apply only to one or a very few subtypes. Closely related to subtypes is the area of reference data. This has been mentioned in earlier chapters and consists of lookup tables such as Country, Customer Type, or Order Status. Reference data tables usually consist of a single primary key column that is a code and a single nonkey column that is a description-for example, a Country Code of "CAN" that goes with a Country Name of "Canada" may be found in a Country table. Reference data tables are primary drivers of business rules because they identify the subtypes that the rules operate on. Up to 50% of the tables in an application database can be reference tables of this kind. Here we see how reference data, which is always considered to be application data, can also be viewed as a kind of metadata, an example of how the boundary between data and metadata can be blurred.

The distinction between metadata and data can get confusing when dealing with business rules engines because a rules engine intimately entwines the two. The type of business rules engine described in this book has at its heart a Business Rules Repository that contains several different kinds of metadata. In the sample application presented here the Business Rules Repository is a set of tables that exists in a single database along with other tables that make up the application database which contains the business data. It may be more appropriate in other circumstances to separate the Business Rules Repository into a distinct database. For instance, there may be a requirement that the Business Rules Repository be a proprietary component of an application, and security can be implemented to prevent unauthorized access to it. In other situations there may be other needs that dictate that the Business Rules Repository and business data must reside in the same single database. In general, from the perspective of the rules engine, either design will work. A convention adopted here is that all tables that belong to the Business Rules Repository have physical names that end with "_M" (for "metadata"). The tables containing business data have different suffixes. This makes it easy to distinguish the tables of the Business Rules Repository in the database of the sample application. Let us now consider the subcomponents. (followed by many, many pages of the gory details... LOL)

Malcolm Chisholm. How to Build a Business Rules Engine: Extending Application Functionality through Metadata Engineering (p. 65). Kindle Edition.

Rx

You have made my head spin.

My studies did not include anything like this. I have learnt through reading Technical books and the help files in Access. The internet had just been born when I started so I did not have it.

So when I see someone using fancy words I try to check them out. Mostly it is just someone who is trying to look good without actually saying something.

I knew Metadata was more than just a word but I did not realise it was so complicated. I am now wondering that if I invest the time and energy to learn more about this, will it make me a better programmer. Time will tell.

I sincerely thank you for taking the time to reply in such detail.

To tell you the truth Rainlover.

I'm like you I really am a bit confused about metadata and further reading never really seems to clear it up for me.

As far as I can tell its just an aggregation of data - which hardly requires a separate name to my viewpoint.

What's really confusing is when you are in a meeting and people start talking about metadata as if its somehow different from data.

Surely if you have data and queries you can create whatever metadata you wish so how can you have a product that provides for data and allows you to manipulate that data but I am told doesn't allow the manipulation of metadata.

If meetings reach this level I try hard to leave and never come back again.!

Rx knows what he is talking about but a lot of people who talk about metadata are I think just confused souls.

For instance a field of names could be argued to be a collection of letters and each letter could be described as a collection of lines and each line a collection of dots so even each letter is to certain extent the metadata of the dots.

Metadata therefore is a sort of recursion of data for people who don't understand recursion.

For instance that WHOLE paragraph that goes into description of the Application database metadata.

To you and I - its the relationship diagram.!!

Try and describe a wheel without using wheel , circle , round and axel and you get to a similar situation.

Metadata is "data about data", i.e. data that describes and configures aspects of the structure, behaviour or integrity rules of other data. Examples could be data that defines data types, constraints, dependencies or mapping rules. The distinction between data and metadata is necessarily somewhat flexible and relative - but no less important for that. Let me mention just two reasons why metadata is an important topic in its own right and is worthy of time and attention.

Firstly there is the desire of businesses to support rapidly evolving requirements by making the "active" metadata in a system easy to configure and maintain (by "active" I mean the metadata that actually controls data structures and processes rather than simply describing them). Businesses often need to adapt and change not just the "operational" data in a system but the data structures, interfaces and business rules as well. They want to be able to make such changes easily and reliably without incurring long development and test cycles. So defining what configurable metadata exists in a system and how it will be managed and maintained is an important aspect of overall systems design.

A second reason why metadata is important is due to the need for interoperability between systems and between organizations. If two systems share the same metadata then that makes it easier for them to talk to each other. If different organizations follow a common set of metadata standards then they can share data with each other more easily and successfully.

Metadata has a lot of investment riding on it. The last decade or so has seen a whole lot of activity in the field of metdata and an industry of metadata management tools, metadata repositories and metadata standards has grown up to support growing demand. On the whole that's a positive thing and ought to be of interest to anyone working in the data management professions.

That is a much better explanation for Metadata. The next level of MetaData with Business Rules in the regulatory sense is to have the meta data choose what code (functions and / or queries) and in what order they are to be run.
So, the meta-data can choose the code on a record by record/ field by field point of view to determine the outcome. This is why the speed is much different than an ordinary query or transaction.

So, given a business management system with the internal and / or external regulation management (e.g. Compliance), the dynamic libraries and repositories have a lot to do with business process outcomes.

Metadata management is not for every application. Something like a check-book system probably has little of no metadata. Rough estimate: probably 1% of DB applications?

An American Tax Preparation software would hopefully fully utilize the metadata with associated business rules. For example, in the US Tax Code, a child born before December 31 entitles a tax filer to a specific class of tax deductions. So, if the taxes were completed and a new child appeared at the last moment, it would be worthwhile to enter. However that entry will probably affect many other aspects of the total Gross Income. That causes a cascade of many other inter-related rules to trigger on weird things such as medical expense above 2% of Gross Income and then apply to a graduated payment scale. That in turn triggers other values, rules and scales that further adjust the outcome. If you are a self employed corporation, it just get more weird than can be imagined by a quantum physicists.
This is probably not new. The above example is my theory of why Jesus was born in a barn on December 25, so his dad (a business man on travel to pay his taxes to Rome) could hide the birth until next year and thus avoid recalculating his taxes manually without the assistance of his tax lawyer. But, as usual, I digress.

Metadata is growing in demand: While many of our respected countries GDP remains stagnant or weak, the one thing we can all count on growing at a record pace is "regulatory compliance". If there was a stock index ticker for regulatory compliance, the scale would need to be logarithmic. It is my theory that metadata just to deal with the regulatory compliance requirements for business will create the next Industrial Revolution for data managers.

When I was researching how to design a custom business rule engine and explain all the prep time to the client... this chapter preview came in handy.
See attachment: "change the extention back to a PDF to open"