On yet another application review with a prospective client the issue of potentially troublesome data entity relationships came up. A number of our clients are existing QuickBase users who have made an initial attempt to design their applications and find that some assistance is needed. The first thing we look at to understand their application requirements are the data entity relationships.
This is done in the context of both their existing application as well as their general business requirement because often applications are not correctly designed to support the structure of the business information.
One of the most common mistakes we see is when application designers build inflexible structures into their applications because of a lack of understanding of relational data. During the client call mentioned above we saw that they had set up a table of Projects and then wanted to capture monthly budget data. To do this the application designer created twelve fields, one for each month, on the project form to capture the budget information. For spreadsheet abusers this kind of flat structure may seem natural. However, in a database environment the goal is to structure related data to support reporting, visibility and analysis of information.
Data Entity Modeling 101
Whenever we start development of a new client application our first step is to model the data entity relationships. The key to this is asking detailed questions about how information in the organization is structured, used and viewed. A simple line of questioning can help flesh out data entity relationships.
1. Start with element of data that is at the core of the process being evaluated. In most cases this should be relatively easy to identify; for example, when discussing a Project Management application the core data element is typically a Project. CRM or Sales force Automation applications can be more challenging but typically have an entity such as an Opportunity, Case or Lead as the core.
- What is the main function of the application?
- What do you call the form that you currently fill out as part of the process?
2. Identify primary relationships to the core data entity (see Example A). Almost all business processes involve data that has some kind of hierarchy so it’s should be expected that a core data entity will have one or sub-entities. A sub-entity is a child relationship where the core is the parent and the sub-entity has one or more pieces of information related to the parent core entity. There are many examples of this; Projects almost always have a list of associated Tasks, Quotes have Lines or Quote Items, Sales Orders have Order Lines.
- What other information is captured as part of the primary function?
- Is additional information singular to the core entity or might there be more than one piece of information of the same type?
- Is information entered on a separate form linked or referenced to the core entity?
3. Identify auxiliary entity relationships to the core and primary data entities (see Example B). We often refer to this auxiliary information as meta data because it is really information that helps qualify another data entity. Auxiliary information is best defined as information that helps define, assign or organize a core or primary data entity. Often information that is contained in a drop-down field is converted to an auxiliary data entity to allow for easier management of the list. Another very common auxiliary data entity is a Staff or Resource table used to assign a Task or a Project Manager.
- Is the field on the primary or core entity a drop down that needs to be selected from a dynamically changing list?
- Is there additional information that needs to be passed to the primary or core entity from a selection other than just the selection value?
- Is there categorical or list data that is likely to be used in more than one place in the application?
The Benefits of Proper Data Entity Modeling
Circling back to the example above where the budget data was set up as a flat structure with a field for each month there are a number of considerations to point out.
1. It’s would be confusing to interpret a project budget that spans the end of the year because there would be data points in the ending months and beginning months but no specification as to which year the months belong in.
2. No project budget could ever be more than year long (there are only 12 fields to capture data).
3. Reporting trend and aggregate data would be very difficult due to the inability to easily identify the budget year and not having budget amounts in 12 different fields.
The ideal solution would be to set up a Budget entity related to the Project and capture each month as a single entry with the month, year and amount entered. This would allow QuickBase and really just about any database to much more effectively report the budget information.
This small example is just one of many such data entity challenges we have seen. In some cases the limitations caused by inefficient structures is not realized until substantial data and process development has occurred and correcting it can be a major project. Therefore it is critically important when designing any database application to go through the data entity mapping exercise as an early step.
Our conversation continues about the optimal mix of technology to support business process. In our last TechWise blog,“Living IT, QuickBase Leads The Way”, we discussed the concept of Living IT and why organizations should plan for changing and dynamic technology. To elaborate further on this topic we want to introduce some ideas around what we refer to as the the Application Ecosystem and how technologies such as PaaS and ETL fit in.
The Application Ecosystem of an organization is a broad way to refer to the various technology tools that the organization uses. This applies to government, for-profit and non-profit organizations. At the center of the Application Ecosystem is what we refer to as Core Systems. These tools are typically associated with basic organizational functions required for accounting and transactional purposes. For smaller businesses, tools like QuickBooks or PeachTree are the frequent choice while larger organizations have mostly deployed ERP systems like Oracle ERP or SAP.
While accounting and ERP systems have expanded to include broader functionality few if any organizations are able to function with a single technology to manage business processes. This is due to the fact that ERP and Accounting systems are designed fir best practices and with transaction management as the primary focus with process management flexibility given limited attention. This means that organizations are pushed to find technologies that complement and extend core systems. We refer to the multitude of applications that support defined and ad hoc organizational processes outside the core systems as the Extension Layer.
The Extension Layer
There are two basic types of applications in the Extension Layer, Point Solutions and Situational Applications. Point Solutions are specialized, typically best of breed applications that solve a specific and typically well defined need. Image management, warehouse management and CRM are areas that often are targeted for point solutions. The other type of application in the Extension Layer is often referred to as Situational Applications. These are applications that solve more unique or possibly temporary organizational needs where no viable Point Solution exists. Often these types of applications are managed ineffeciently using spreadsheets or simple databases.
The final but very important element of the Application Ecosystem is the body of interconnections between applications. These connections or integrations may be between applications in the Extension Layer or with Core Applications. In many cases organizations lack the technical capability to effectively integrate applications so information is moved between applications with human, manual processes. Only when extension applications reach a significant size and value are they integrated in an automated way with other applications.
Paas & ETL Enable Flexibility in Extension Layer Application Creation and Interconnectivty
Platform-as-a-Service (PaaS) tools such as QuickBase and Wolf Frameworks provide organizations with tool sets to rapidly build and deploy Extension Layer applications that extend core accounting and ERP systems or provide effective departmental or workgroup functionality. PaaS is frequently utilized for Situational Applications but also quickly becoming a strong choice for CRM, sales force automation and other areas often relegated to Point Solutions.
One of the main benefits of PaaS as part of an organizations Extension Layer is the ability to quickly and easily interconnect data and processes between applications designed on the same PaaS technology. For example, QucikBase allows the ability to create cross application relationships between applications as a simpel and easy way to share information.
A technology that is rapidly becoming mainstream is Extract-Transform-and-Load (ETL) tools. Open Source technologies like Talend ETL are allowing rapid, low cost development of integration. This means that organizations can choose to automatically tie together applications, processes and information that previously could not have been efficiently integrated.
It seems there is a continual push and pull in the technology world between centralizing and consolidating technology vs. distributing and diversifying. We’ve asked ourselves this question often of whether our clients will be better served with a single technology to support their needs.
This line of questioning typically leads to the evaluation of the trade off between a single multi-purposed technology that solves many problems but none well as opposed to a number of “best-of-breed” solutions.
In reality organizations seem to answer this perennial question with decisions that shift the balance continually. These decisions and the resulting daily interactions with IT systems are what we refer to as “Living IT”. By acknowledging that technology within an organization is a dynamic entity allows for a whole new approach to solving business process and technology challenges. A number of important factors are defining “Living IT” and it’s place in the 21st century organization.
1. The gap between management and technology is shrinking
As the Cloud evolves and the explosion of advanced collaboration and business process tools continues, technology that previously relied on IT staff to deploy and support is becoming accessible directly to information and process managers. Platform-as-a-Service (PaaS) technologies such as QuickBase are a perfect example of tools that are narrowing this gap. While QuickBase has the ability to handle advanced technological problems, the codeless development environment is used by managers with limited technical skills in tens of thousands of companies to deploy collaborative information gathering and process applications.
2. The costs and risks of change are shrinking as the benefits are growing
The 21st century company has to adapt quickly. This means adding capacity and scaling quickly but also being nimble enough to shrink and shift directions nimbly. In the “Living IT” environment companies need tools that are flexible, scalable and in many cases expendable. QuickBase is again an ideal model for supporting change at a low cost. The QuickBase subscription model is user based allowing organizations to develop as many platform applications as are needed with the scaling costs based on users only. As organizations needs change applications can be retired without concern and new ones rapidly developed and deployed.
3. Interconnectivity and security, two sides of the same coin are the main drivers of 21st century technology
Ubiquitous access to the internet has opened the doors to a new wave of collaboration between people and between technologies. This new power of communication has also brought with it very real organizational concerns about security and accessibility of information. QuickBase provides powerful tools to take advantage of collaboration but also manage the associated risks.
- Domain/application user model: QuickBase users are registered with the QuickBase domain or with a private domain for enterprise accounts. Application managers can then invite users who have registered in their domain to applications to allow access. This simple but powerful model allows for easy collaboration with users inside and outside the organization without compromising security.
- Role based security: within an application access is defined for each user by designated roles. Roles can be easily configured for simple access rules or more complex data driven models. This enables applications to effectively create different user and data experiences tailored for each application user.
- Secure and capable API: QuickBase supports communication to and from external application via an HTTP API model. The API enables interaction with all application tables for query, record add/update and import actions as well as more advanced interactivity. All API interaction required authenticated sessions and for increased security, applications can enforce API token requirements.
The most important consideration in creating a true “Living It” environment is to embrace and not fear technology change. Even very traditional technologies like accounting systems have to flex and change as the tax and other financial policies change. Process and collaboration tools need to be even more flexible to support organizational evolution. QuickBase is in ideal tool for supporting the “Living IT” environment due to it’s ability to enable development of secure, collaborative process and information management applications.
I first stumbled onto QuickBase more than five years ago as a Sourcing Manager at American Greetings. I was looking for a better way to collaborate with the Product Managers than sending a barrage of cost requests to my buying team. My first app, the RFQ Manager, was an instant hit. In fact, the Product Manager I tested it on liked it so much I did not even need to ask the other PM’s to start using the tool, they were asking me where to sign up. It wasn’t long before I was literally touting QuickBase as “the best thing since sliced bread,” but it would take five long years, hundreds of developed applications and a deep search of the PaaS marketplace before I really understood what makes QuickBase special.
With so much time behind the wheel of QuickBase, it seemed that the almost instant access and reportability of entire datasets linked across multiple applications, all updated virtually in real time was something to be assumed. Last winter, our MCF leadership team spent several days with QuickBase and talked a lot about what’s under the hood, namely the use of a RAM/in-memory database. However, at the time I did not fully grasp the meaning until I explored other PaaS and SaaS offerings and realized that few if none were delivering the kind of data experience that QuickBase could provide.
In-memory databases (IMDB) store information in the RAM memory of a CPU whereas main-memory databases (MMDB) store data on disk space. IMDB storage is not-persistent, meaning that when the power is turned off the data is lost but accessing the information is must faster and requires fewer computations. It’s basically equivalent to the RAM on your PC where the information is stored for running applications that need fast access to the data. This model is often used in applications where access speed is essential such as 9-1-1 response systems and telecommunications.
What this means for QuickBase end-users is that the accessibility of the data, the computational capability, is optimized for speed. To translate this into terms that may be more familiar, imagine a totally different setup for your PC where instead of 1 or 2 GB of RAM and a huge hard drive, you really only use your hard-drive as back-up and your PC with 200 GB of RAM is never turned off. This means that every file and application is almost instantly accessible, there’s no booting up and no time loading applications because everything is always loaded in memory.
With all this in mind, it is clear why QuickBase provides world class collaboration, access and reporting for work groups, teams, smaller businesses, project managers, etc. It’s important when choosing where and how to deploy QuickBase to consider the use and underlying data needs, as some requirements are not ideal for IMDB due to dataset size and require the larger and cheaper data storage solutions. Fortunately, integrating with QuickBase is straight forward, allowing it to serve as the application for working data and with transactional or unnecessary data archived or purged.