What is best practice for procedures database or capsule?

Ashley How

There are various benefits of capsule and database procedures.

 

What is the recommended best practice for choosing to develop procedures in capsules or in the database?

 

We develop applications for customers and maintain development and production environments.  Capsule procedures offer the simplest promotion capability, although database objects need to be configured manually or via the transporter tool.

Helmut Heimann

Hi Ashley How,

 

my guiding principle is to use capsule procedures only in those cases where you're sure, they'll only be used in one capsule (or derived ones). Any other procedure will be a database one.

For promotion from DEV to PROD we always recommend using the transporter tool.

 

Kind Regards,

Helmut

Andrea Masiero

It depends on the use of the procedure. 

If the procedure should be triggered by the user and contains some goto screen or goto capsule lines the best is to use Capsule Procedures. 

If the procedure should be automatically scheduled and contains heavy back-end operations like data readers i should use database procedures.

 

Capsule procedures are transported between environments by simply copying and pasting the capsule itself. 

Database ones can be transported by copying the bm1Procedures.dat file. (you need to stop and start the service). 

The effort is exactly the same. 

Daniele Di Lorenzo

Hello

this is my approach

 

all the processes are on db side and proedures are built in "atomic" modules

then I mix them using call procedure depending on needs

 

Example: In a database the basic proceses are

 

000 Backup

001 Data Upload

002 Masters Upload

003 Trees Repair

004 Data Process

...

Then I have a procedure called commonly "MAIN_db name" with the call to all of them.

It's commonly used in scheduled batches

Or I can also have othe "MAIN" procedures for process slices

e.g. "MAIN_Masters" with calls to proc #002 & #003 only

 

In the capsules I have only the calls to main processes, and cpsx procedures have following standard structure

 

e.g. procedure name 01 MainUpload

 

call to MAIN_db name

Refresh screen

Message "Update completed"

 

Moreover I use cpsx procedure for export reports e.g. to PDF , because in this case a db procedure cannot be used.

 

As general rule all db proc have 3dgt id and cpsx 2 dgt id.

 

I find this approach very "comfortable" from different points of view, I manage about 50 dbs and 500 cpsx  and it allows me to easily manage all this stuff

 

Hope this helps

best regards

Ashley How

Thanks Helmut,

 

This would raise a larger question, which is what approach should then be used for capsules.

For example, a particular planning process, it may be decided that all planning functions be contained in a planning capsule, therefore, all procedures for the planning process could be contained in the capsule.

For data loading and transformation, more traditional BI functions, these would not normally have associated capsules and therefore be more database driven.  Alternatively, there could be a capsule for data loading functions that also could be used for reporting data integrity issues.

 

How do people approach capsule design principles?

Ashley How

Thanks Andrea,

 

Scheduling is a key consideration.

 

Copying the bm1Procedures.dat file is a problem in a cloud environment as we don’t have direct access to the C drive where these files are currently stored.

Ashley How

Thanks Daniele,

 

Thanks you for sharing this complete approach.  We find different people from the wider board community don’t have the same structured and systemised approach to developing in Board.  Sounds like a programming background.

 

I have an earlier question on capsules in this post I’d be interested in your point of view.

Daniele Di Lorenzo

Hi Ashley

 

I guess you refer to "How do people approach capsule design principles?"

 

I can write for hours about this topic

 

commonly I have 2 types of cpsx: admin and reporting ones.

In some cases (little applications) both sections are integrated in one

 

A reporting cpsx have just a Home screen and reports viewed in menus.

I always use a standard layout and through masks and copy/paste objects I develop reports very quickly.

 

No procedures are included in such capsules (except ones to e.g. print a pdf)

No hided screens, all reports are accessible from the home page.

In some cases I use buttons with pw if some sub-menus needs restricted access.

 

A admin cpsx is organized to guide the user or the administrator in process update or control.

I set a check report for each data process, using alerts: any error or strange data should be immediatly detected.

This approach allow efficiency in manage and check any process made in Board, and helps in incrase the user's perception of reliability.

 

In my case there're basically no users allowed to develop reports by themselves, power users have their test area where they can develop reports but they should be validated before make them "official".

This approach helps in use standard formats and limit the reports proliferation

 

Folders are organized by area and separated in reporting / process.

 

I attach some pictures.

hope this is what you're asking for

 

 

Ashley How

Hi Daniele,

 

Thankyou for your generous reply.  The screen prints are also very helpful to see how other people approach development.  And it’s very good to hear someone committed to reliability.  If users lose confidence in the numbers, the whole system is put at risk.

 

Regards,

Ashley

Daniele Di Lorenzo

Thanks!

I fully agree with you

The user perspective have always been a key point in my approach, and I see every day more the benefits!.

If you are interested in, have a look to Modine case study from Board web site  

 

regards

Daniele

Ashley How

Thanks Daniele,

 

I checked out the case study and it shows some impressive user adoption.  I was interested in the 35 databases.  What was the reason to have that many databases and handling data that exists across multiple databases?

 

Regards,

Ashley

Daniele Di Lorenzo

Thanks! 

 

using multiple dbs better fits our situation: the company has changed a lot during the years and still have many different sources (I keep data from 7 different ERP's) , we always have devoloped apps internally and this approach better fits the continuos evolution minimizing the impact on daily routine.

Probably in other cases having one  or a few db can be better. 

 

There are many reasons, mainly:

Better usability: different applications have different features (entities) and settings, so evry db is specialized with the features for that area only

Better maintenance and efficiency in process: improvements in one db can be made without affecting other applications, a littel db is faster from any point of view

 

Databases are mainly organized by function

I have a few CORE databases by function ( sales, accounting, materials..) and APP databases that usally are fed from some data from the core ones.

 

To cover some gaps of having data in different places:

with multidb option I can create a report linked to different dbs

different db can be kept aligned with scheduled procedures (print and reload)

 

regards

Ashley How

Hi Daniele,

 

I also noticed your "." prefix on entities.  I assume this is an indicator of their position in the hierarchies.

 

Regards,

Ashley

Daniele Di Lorenzo

yes. This is  a trick I adopted to have an immediate view of hierarchies from entities panel.

It simplifies a lot my life

Ashley How

Great idea.  Thanks Daniele.