Drill Actions

Center of Excellence

1. Abstract

Board offers multiple drill options, it’s important to consider the requirements when planning or setting up drill options and to design the solution accordingly.

2. Context

Board offers multiple options when drilling into data, which is the right one to use depends on the specific requirements – whether the data needs to be formatted in a specific way, how up-to-date the data needs to be, and whether a single drill path is required or a choice of multiple. The use of drill in particular can have a significant impact on the design of a database.

3. Content

3.1 Drill Down

A basic drill down on a data view or chart will simply pop up a new window in the same screen being drilled, the drill down will inherit its basic format from the object being drilled. This type of drill can be useful where specific formatting is less important than being able to see the details behind a number or set of numbers quickly and easily. Seeing data in the same screen in this manner is typically used where the user may want to see more detail than the base layout is showing but wants to stay in the same screen rather than popping out a new screen, and where the data already loaded into Board is sufficiently up to date for the user’s needs.

Board's best use case is to indeed generate aggregated analysis and allows drilling down on elements of interest (as opposed to large/detailed/flat spreadsheets to scroll).

A default drilling path with a customized layout could be defined in design mode to simplify the user experience, otherwise, users can always use the Drill-Anywhere to choose their desired level of granularity.

3.2 Drill to Screen

Drilling to the screen allows you to essentially use a combination of the screen select and the entity tuple being drilled as the select for invoking a second screen. Typically, the drill screen will be opened in a separate tab to

allow the user to compare the aggregate data with the drill and to easily drill a new tuple if required. Which screen is opened can be dynamically determined if needed when drilling from a data view.

This type of drill means that the drill, rather than simply being a single layout, can be a whole new screen or dashboard containing multiple new layouts with detailed information about what’s being drilled into. An example of this might be where a dashboard shows the top 20 customers by spend, rather than simply seeing the detailed numbers behind that spend it might be desirable to perform a full analysis of the customer’s spend patterns, in which case being able to drill to a whole new dashboard containing a variety of charts and tables putting the customer’s spend into more context and showing how it compares to past years might be more useful.

3.3 Drill to Procedure

Drilling to procedure allows you to use a combination of the screen select and the entity tuple being drilled as the select for invoking a procedure, this can be used for different purposes – drilling to a second screen but amending the select before calling it, or even for triggering a process which doesn’t even actually show a drill down, but perhaps performs a task of some kind. If this method is used for drilling to a screen it just means that you aren’t then tied to the selection at the point of drilling – in the example given before for drilling a customer, the screen being drilled from might have a selection on the current month, however on the target screen you may want to display the spend history for the entire year, in which case drilling to the procedure would allow you to make the required adjustment to the selection and then call the drill screen.

3.4 Drill Through

The final type of drill is something that is normally considered at the design stage of a database as it directly impacts the granularity of data the database will hold. Drill Through allows you to drill from Board into a third-party database such as SQL and retrieve data via a query in that database.

Setting up a drill through is different from the other types of drill as it needs to be set up similarly to a data reader before it can be used, you specify the connection to use, the query to run, and map the fields to match entities in Board, this then allows Board to use your selections on those entities to drive the query. For example, if the drill though is set up to query highly granular transactions it might be mapped to Board entities such as the customer, month, and product, this would allow Board to hold data at a more aggregate level and still allow the user to review the transactions which make up those figures without the need to load that granularity of data into Board itself.

This type of drill is normally used where it’s necessary to be able to review the most up-to-date data, normally at a granular level, without needing to first load it into Board, alternatively, it might be used simply to avoid loading highly granular data into Board where such granularity might only be needed for occasional queries, and the rest of the time far more aggregate data might suffice.

The upsides of this method are the ability to interrogate highly granular data without first needing to load it into Board, meaning the database can be kept smaller and more performant than it might be if a large amount of transactional level data were loaded into it, and the ability to access the most up to date data from a source system without running a load. The downside to this approach is that the drill can’t be formatted and is displayed in a specific dialogue box which allows the user to review the data and export it if required, but doesn’t allow other actions like further drills into the drilled data in the way that the other methods would. It also creates a dependency on the source database which means it will need to be available any time users are to use the drill functionality, rather than just while an overnight load is running for example. This may not be a concern but should still be considered as a factor in case it either causes issues with the load on the server being queried, record locking if the database is also being used by another system, or simply the availability of deployment windows for making changes to the database.

Another point which should be considered is that the data in Board itself will only be as timely as the last time it was refreshed, the data in the database being drilled too may be far more up to date, this is great in terms of the user being able to see the latest figures, but may mean that the transactional level data no longer matches the aggregate figures which were loaded into Board, users will need to be aware that this might be the case and that while both datasets might be equally valid, that validity exists within the context of when they were last refreshed.

3.5 Conclusion

As we have seen, Board provides multiple options when drilling data to get more context, each method can be useful but which to use depends entirely upon the specific requirements. More than one drill type might be used in a single solution where the need exists, the important thing is to consider early in the design of your solution what the drill requirements might be and to ensure these are considered in the structure of your data, particularly if a drill through might be required.

The most impactful in terms of the need to plan and design for it is the ability to drill through to a second database, however, due to its nature this drill method will normally only be used where the situation calls for it, if one of the following is true:

• The data set to be drilled is too large or granular to load into Board itself
• The users need to be able to see very up-to-date data without the need to first load it into Board

If one or both is the case, then you’ll probably want to consider setting up a drill-through and using that.

In most cases, however, the user will probably just need to interrogate the data loaded into Board itself, in which case the key questions to ask yourself when deciding which method to use are:

1. Does the user just want to see a single layout at a time giving more context to the figures being drilled or does the user want to see more than one layout to give more extensive information about the data being drilled?
2. Does the drilled information need to have a specific format or will a basic data view suffice?

If the user needs to either see multiple layouts giving more extensive information, or if the drill needs to have a very specific format then you’ll probably want to use either drill to screen or drill to procedure, depending on whether you need to change the selection before calling the drill screen, if the user only needs to see a single layout at a time, and complex formatting isn’t required, then a basic drill down will probably suffice.

Center of Excellence

Thanks @Robert Barkaway for the insights on this subject!

Neha Hasija

Hi Team,

Thanks for the best practices on this topic

Can we configure multiple level drill-downs actions i.e combinations of both drill to entities and drill throughs in Design mode from the layout configuration and not the drill-down menu?

Thanks & Regards,

Neha Hasija