Cutting the long story short, suppose you want to change your schema name and display a folder with only its views inside it.

I will use the Metadata Container Editor tool to cope with this tasks in a few clicks.

Metadata Container Editor lets create and edit the content you see in the database schema tree without coding. It is included in all versions of Active Query Builder. Its main window looks as follows:

​There are two trees, and one properties pane at the right that displays properties of the focused node of one tree or another.

The left-hand tree displays the content of MetadataContainer which reflects the physical database schema. We highly don't recommend renaming or moving objects ​in it. You can hide some of them or remove unwanted ones. Also, you can specify alternate friendly names for real objects, so the user can quickly catch what data it contains.

The tree at the center displays the MetadataStructure object. It is used to customize the arrangement of objects within the tree. If not filled, it reflects the physical structure according to the QueryBuilder.MetadataStructureOptions settings (there you can define grouping by types and namespaces, etc.)

By default, both MetadataContainer and MetadataStructure objects are filled during the metadata loading process automatically, but you can override this by loading them from pre-generated XML files. You may load just the MetadataStructure from XML and leave the MetadataContainer to be filled automatically, or vice versa.

So, to cope with the given task you can do the following.

1. Open the Metadata Container Editor, select the "Metadata Container" - "Load from database" menu item, follow the Metadata Load Wizard steps and get your database schema loaded to the right tree.

2. Add the following nodes to the Metadata Structure:

- Disable automatic loading of child items for the Root node.

​- Drag your schema from the Metadata Container to the Structure tree and disable loading for it, rename it as you need.

​- Add a new item and set its caption as needed (I named it "OnlyViews"),

​- For this node, create a metadata filter and define it to see only objects of View type.

​When you finished, you can click the "Preview" button and see how your tree will look for the end-user.

​Of course, you can do the same in code. But this tool lets you experiment with the tree structure faster. You can read this article to learn how to tune the MetadataStructure to suit your needs: Customizing the Database Schema Tree structure.

Entry-level SQL users sometimes have a hard time learning the language or hesitate to apply their knowledge in the real world when working with production databases. These are just two of the many possible reasons that hold beginners back from accessing the data they need. Active Query Builder helps SQL learners and newcomers resolve all kinds of issues when creating SQL requests.

What SQL newcomers will get with Active Query Builder:

• Visual drag-and-drop query editor
• SQL learning capabilities and auto-assistant
• Query syntax and logic checker

Easy solution for all entry-level SQL users

There are many typical minor problems and questions that beginners usually have. If you’re one of them, here are the answers.

Do you need help with SQL queries?

People who know SQL a bit but don’t have much experience need to build confidence. Active Query Builder allows them to use a handy drag-and-drop visual query editor, which is probably the most beloved feature. It lets users create real queries as easily as putting together a child’s Lego project.

Write your first request in less than 2 minutes. You’ll be surprised by the speed at which you can construct queries from simple building blocks and see results right away.

The query syntax and logic are always under control. Get rid of missing commas and unmatched fields when doing SELECT and GROUP BY.

No more waiting in line for help. You no longer need help with every query because you can do most of the tasks yourself. In addition, IT staff can do their routine with little or no in-house consulting.

Did you learn SQL a while ago and need to refresh your knowledge?

It might be painful to go back to where you left off, especially after years have passed by. But, on the other hand, you might need to ask a few people for advice, and it can be tempting to procrastinate a bit before you come up with a query text.

Even if you don’t remember how, you can still do it. No need to read the documentation if you can’t remember some SQL keywords. They’ll be at your fingertips while constructing a query.

One query, two views. Constructing a query, you can switch between two editors: the visual one at the top and SQL Query Text at the bottom. Any change in either mode will automatically appear in the other.

Auto-suggestions will help you write query text and prevent you from getting stuck.

Try out Active Query Builder now. This tool can be equally valuable to both professionals and newcomers.

Knowing where to look for the necessary data in a scarily large database schema is vital when you write SQL queries.

Sometimes, even database designers have a hard time guessing which object contains the required data. Documentation and even professional advice isn’t always such a great help for intimately understanding a database schema and navigating it like your own backyard.

Corporate databases usually contain thousands of tables, and a simple search by name may not work. Another trap is searching in the wrong place by selecting the wrong object as the data source when typing a query.

In this article, we’ll talk about the most common problems that crop up in large relational databases, and how to optimize data searching mechanisms. Solving these problems will benefit data analysts and operators who need to get data, as well as developers who control DB infrastructure.

Make it simple!

Self-explanatory name aliases

When creating a database, an engineer may give tables and fields nondescript names, making it difficult to navigate in the future as the DB continues to grow. Unfortunately, this issue can’t be resolved by simply changing the names, because that will cause addressing problems in linked services. Another time this type of issue comes up is when website CMSs create databases automatically and assign ‘machine’ names. To recap, here we have two main problems:

1. Indistinct names given by humans (e.g., AGC_SPAN2WAY_COL).
2. Automatic machine names (e.g., TBL-001, TBL-002).

Either way is human-unreadable, and it doesn’t serve you well when you need to find relevant data quickly.

The best solution here going forward is to assign clear and descriptive names when creating a new database, but what if you’re stuck working with an existing database that supports a large system and is impossible to change?

Active Query Builder allows you to assign aliases to any table or field name. Your queries will be self descriptive and easy to understand. Aliases can include text in any language.

Display only what matters

There’s no point in showing an entire database structure with thousands of tables. As you work, you can selectively display only the tables you need to see. This way, the database will become more transparent from the user’s perspective, featuring a tree of only the most relevant data for current business processes.

If the number of objects you need is large enough, you can group objects in folders in accordance with areas of business use and subject. You can put a few main groups in the root folder, and divide the rest into subfolders (similar to a file manager).

It also makes sense to create different views according to roles or user groups (for example, the accounting department sees one structure, business analysts see another).

Group adjacent entities into virtual objects

Creating virtual fields and objects really helps give you a clearer database schema view, making it easier to manipulate a smaller number of objects.

Virtual fields minimize the number of reference tables needed for creating a query.

A database usually stores information about a single entity in multiple tables, which makes data searches much more complicated. For example, let’s say each product has a specific category ID, which is a numeric value. To match a numeric ID and category name, you need to refer to another table. Only when you open the table that matches category IDs and their names can you find out that, e.g., #304512 is the sports shoes category, and then continue the search.

With the help of Active Query Builder, you can add a virtual field to the product table that doesn’t appear in the real database, but helps to navigate linked data. In the case mentioned above, the category names will appear in this additional virtual field (next to the category ID column), which will minimize addressing to corresponding databases throughout the data search.

Virtual objects collect all the necessary information about a single entity.

You can combine explicit information about a single entity within a virtual object. It's like a pivot table where you can choose what data to show, group, sort, combine, etc.

For those who are aware of how views work in SQL, this topic won't be hard to understand. It's literally the same thing, but applied on the client side only, without modifications to the real database.

How does it work with SQL?

How is all of the aforementioned related to querying an actual database? Active Query Builder handles the entire process and also allows users to work with aliases, virtual objects, and more. The end user might not even guess that the names s/he is using are overlays of what exists in the actual database. Active Query Builder automatically turns readable name aliases and phantom schema elements into original names and SQL expressions when completing queries.

Usage scenarios

Let’s look at a few common usage scenarios related to working with a corporate database. Different user types can configure a database view for the end user to work with the database and resolve their day-to-day tasks.

Lone warrior (a programmer)

The main task of a programmer is to support and improve the company’s system and release an application or a service that works with the database.

An approach where a programmer is also responsible for handling the database is usually applicable to small projects and organizations. The developer knows the database structure and can provide access to the required fields and tables for specific groups of users. This process requires some time, but it pays off when the user roles are pre-determined, and the company doesn’t have a dedicated employee for the task. In that case, it’s easier for the programmer to configure how the database is represented to end users within the company.

After it’s done, the developer can work on improving the system, which is a more strategically important task than helping colleagues with SQL queries.

The drawback of this approach is that any changes in the database schema will require releasing a new program version. Again, with the help of Active Query Builder, you can make the database way more transparent with name aliases, virtual tables and objects without altering the production database.

Enterprise style (a supervisor)

Note: To avoid confusion with DB admins, let’s say that an employee who configures database views for end users is a supervisor.

In larger organizations with a complex data structure, a programmer should exclusively work on improving the entire system and its apps. A database app supervisor will maintain the database and handle the ins and outs for regular users (other employees). Active Query Builder has a dedicated UI module for that workflow.

The DB app supervisor can customize the database view for different groups of users in accordance with what they do within the organization. For example, accountants will get one DB view, and marketing specialists will get another. Both views will lack any extra stuff that distracts them from solving actual tasks.

The database admin can add virtual fields and objects, delete them, and create name aliases that show what data is stored there. If the adapted name is not enough, one can add field descriptions with more comments.

Self-service (an end user)

The programmer doesn’t always know the entire database structure along with every user's role. Even if that’s the case, users can retrieve the necessary data with minimal experience. If a user periodically works with the SQL database in the organization, typical queries can be saved for future queries in the repository. After that, those query templates can be used in other queries as virtual objects. The approach of using a query within a query requires little more than average experience, but it’s really helpful if users have the freedom to work with multiple databases like supervisors.

Conclusion

If your RDBMS has a complex schema, building queries doesn’t necessarily entail the cumbersome, painful process that’s involved when using regular database query tools. You can create a comfortable ecosystem for employees using a specialized tool like Active Query Builder. End users can feel comfortable working with the data without SQL barriers, and programmers can avoid the hassle of modifying the real database.

Is it possible to make users of your database information system (ERP or CRM) self-sufficient in serving their needs for data without the use of complicated and expensive BI tools?

This article is about straightening out the process of getting data within an organization for all employees with different skills and knowledge.

While programmers and database admins can freely write SQL requests and export data from a database, this task will make the hair stand up on the back of a business user’s neck. Still, they need to have access anyway, so the data must flow!

Any PC user can work with spreadsheets, but accessing SQL databases is way too hard for an inexperienced user. In this environment, IT-savvy people get more and more strenuous workloads retrieving data, but is there a solution which can be integrated into your internal system that’s as easy to use as a regular spreadsheet editor?

Live Example

Let's review a case with a sales operations manager in a grocery store network who needs to monitor the remaining number of products in warehouses across the state to minimize leftovers. It is an essential economy and supply chain task that reduces surplus (which leads to a crisis when left uncontrolled). This task, when perfectly executed, maximizes profit and customer satisfaction.

An ordinary sales operations manager doesn't have enough skills to write SQL requests, but needs to access data every day. For this purpose, s/he asks an SQL-proficient colleague to get the data from the database. A single work-hour from an SQL expert costs anywhere from $25 (SQL data analyst) to $55 (SQL developer), but even worse is that a developer has to switch from other strategically important tasks to the low-qualified (from a master’s degree developer point of view) job of accessing data.

The worst-case scenario is if a developer cannot afford to be distracted, and a sales operations manager becomes responsible for leftovers and spoiled products, which will lead to internal tension in the company. Why not remove this potentially dangerous extra communication chain?

Otherwise, you can hire a full-time programmer, who will specifically perform these types of tasks, but why do you need a guy who manually transfers product from one conveyor belt to another? It’s better to “hire” a robot to do that.

How can an SQL Query Builder solve this problem?

An SQL database operator needs to be qualified enough to access an SQL database. However, with a DB access tool, it becomes available for everyone, so it can serve as an alternative for a business intelligence tool. Resolution of this essential problem will minimize delegation chains for accessing data and will create incentives for employees to work more efficiently by accessing data on their own.

This solves not just one problem, but many subsequent problems as well, which adds up to process optimization and business growth.

With the help of an SQL data access module, data analysts and other users who were struggling before will start getting data directly for their day-to-day activities. This will save you more resources for strategic development rather than routine (yet important) tasks for accessing SQL.

SQL simplified

How can a non-technician learn SQL and become familiar with a database schema without taking extensive courses? A visual interface for building queries solves only a part of the puzzle. End users will also need to see where the required data resides in the database, and how each database table is related to the others. Just provide users with a clear view of the schema, and let them explore it and try out standard access operations. It’s the quickest way to learn how to operate a database.

Active Query Builder lets the DB admin prepare an easy-to-understand presentation of the database schema for users. Plus, a live update of the data result they’re searching for is a great help. A perfect analogy for that is autocomplete suggestions in smartphones and Google searches that simplify day-to-day life.

Building your own DB access module with the help of Active Query Builder will optimize business processes in your organization once and for all. Even if your business arises rapidly, the access module will continue to serve all employees well.

Does your business need a DB access module?

Let’s walk through the checklist to figure out if it’s appropriate for your situation. There are a few technical prerequisites for you to consider as well before implementing a DB access tool.

Mid-to-large business

Usually, this solution is acquired by mid-to-large companies with more than 30 people on staff. Smaller companies, especially IT companies, as a rule, don't have very complex databases with CRM and a lot of operators and can handle them on their own.

It must be an SQL database

SQL databases have proven to be the most commonly used databases. There are many database management systems: MSSQL, MySQL, PostgreSQL, Oracle, etc. Any of them will work fine for organizing universal DB access.

Proprietary ERP or CRM systems

A large number of companies use their own in-house systems to organize internal data, for example, a self-written CRM to organize a customer database.

Note: Commercial CRM systems, like SalesForce, won’t work because they work with databases in their own hidden way.

Are there cases when you don’t need it?

Aside from proprietary systems that manage databases in a way that you can’t interfere with, there are some other reasons you might not need to implement a DB access tool. Here are a few cases where you’d be better off without it:

• If you work with regular Excel datasheets, not databases
• If you don’t need ad-hoc reporting
• If you already have all necessary reports possible ;)

How to set up a data access module

Setting up the module will take minimal effort and produce maximum results. No heavy scripting is needed if your database is clear enough to understand by users. Advanced configuration process doesn’t usually take longer than a few hours or a couple of days, depending on the complexity of your database and your perfectionism.

If your case is not a trivial one, or if you have particular questions on how to set up a module for your organization, please contact us, we’ll be happy to help.

Summary

Living in the modern era, we have to use technology that’s easy to use but hard to understand from top to bottom. It may seem that many things have been simplified during the last 10 years, like user interfaces and programming tools, but the amount of business data fills out day by day. More parameters are being tracked, and more automation is being implemented.

When you start any B2B business, you need to access partner’s remote data. Each company has its own database and you need to set up data import individually. The manual approach is like marching into the company headquarters and asking the CTO to show you where to get the data. Then repeat the process with every client. Sounds insane, right?

Scaling up your business isn't possible with this unbalanced workflow, and you need a universal way of accessing data across multiple databases.

In this article, we’ll learn how to do it, referencing a live example of a solution that connects a network of web stores that accept payments with a network of delivery companies.

Each store delegates parcel services to a number of delivery companies. Delivery agents need to access an internet store’s database to get the data needed for mailing, but they shouldn’t have full access to customers’ credit card information, etc.

Access a third-party database like your own backyard

A manual data access approach is feasible with up to ten clients. When your business grows and you connect with hundreds of companies, more and more people with different skills get involved and they need to access data across multiple databases. To satisfy everyone's needs, developing a convenient database backoffice will turn into a nightmare without proper tools.

Partner’s database is shrouded in fog for an employee from another organization, so you need to build a data access module that’ll help to read the DB schema. Let’s see what a data access module task is.

A database consists of many linked tables that contain all sorts of data. Sometimes, the required data is stored within XML or JSON fields. SQL is the key to translating a very complex structure into a readable table format. However, it’s merely a data access language, not a ready-to-use solution.

Data access module tasks:

• Reveal a database structure
• Let all types of users write SQL queries (even beginners)
• Instantly show result data as you type a query
• Map data to the structure
• Convert data to convenient file formats

The module should provide safe and controlled access to an external database through the easy to use and understandable interface. A little assistance from the partner company might be needed, but it’s nothing compared to making regular requests to provide papers and separate chunks of data.

Business example: Sending packages

Let's take a look at a specific example with a web store that sells various items, and several delivery companies that deliver the orders to customers’ addresses.

Let’s say that you own a B2B solution that connects to the web store and passes this data to a number of delivery companies. Both parties — the web store and the delivery company — benefit from this partnership. They don’t bother about the peculiarities of the SQL database but simply do their part of the job: grow their business. Both parties should also take care of confidential information, such as end users' personal data by providing limited access. This data must be safe according to the user policy and non-disclosure agreements.

When a user buys something from the web store, the order details should be sent to the delivery company ASAP, so the web store must grant direct, yet controlled access to its database.

The partner’s internet store has an SQL database of order IDs, a list of items in each order, customer names, delivery addresses, etc. Of course, all this data is represented in different tables that are linked with each other. One store may place multiple orders, and each order may include multiple items, so the information can’t be represented in a simple spreadsheet.

Imagine how long it would take for their DB admin to contact the web store, obtain the database schema, create the necessary SQL requests to set up a correct ETL (extract-transform-load) configuration for getting the necessary data and sending parcels. It’s ridiculous! No business can grow with such an inefficient solution.

Accessing the database implies getting only the required data (via SQL SELECT). A third party can’t write to the external web store’s database or read sensitive information (e.g., users' credit card details).

With this approach, you’re delegating the data configuration task to one of the parties, who knows what data should be provided. This party is also responsible for data protection and you free yourself from possible legal issues.

In the end, the user needs to do is match the data columns of this query to the fields in the destination table. This process is also called mapping. With simple drag’n’drop, the end user moves the data columns to the fields in the table that retrieves the data.

Voila! Now we have a ready-to-use query, with columns matching the columns of the recipient/receiving table. The only thing left to do is to import!

Summary

Data access is crucial for business processes between multiple companies. When you run a business, you don’t want to spend a lot of time digging around for data on a regular basis. Make Active Query Builder one of the core components of your solution for letting businesses interact and thrive together.

The component comes with a full set of tools needed for accessing databases, customizing the environment, executing SQL queries and performing other data-related tasks.

Check out the demo at GitHub and see for yourself how any PC user can be qualified enough to work with data tucked away in a maze of SQL databases.

Today we announce the main improvements in the new major version that will help users and developers be more productive than before. The upcoming release of Active Query Builder for .NET will be available until the end of this year.

Core improvements

The main core change is the new serializable query object model. It will store every single SELECT statement the way you see it in the query text: lists for the SELECT, GROUP BY, and ORDER BY clauses and tree-like structures for the WHERE and HAVING clauses. This model will significantly ease the programmatic SQL query analysis and modification.

Besides, we have made the new fluent API to build SQL queries the way close to the SQL language.

Three main advantages of the new API:

• It is type safe and uses the pre-generated objects from your database schema, so you get compilation errors when table or field is missing or misused.
• You get SQL text for any database server supported by Active Query Builder from the single query definition.
• It is easy to read and easy to write queries with it using the IDE auto-completion: lists of SQL functions, database objects and their fields are always at hand.

Visual improvements

The tree-like interface to edit conditions in the WHERE and HAVING clauses will be available in all versions.

You can let users define conditions in the old way, so users can gradually move on to using the new interface, as soon as they realise its advantages.

Only four columns in the Query Columns Grid will be necessary to build a query: SQL expression, Column name, Sorting and Grouping/Aggregate.

Further improvements

For the rest of the year, we aim to provide a more compact and clear view of SQL queries and database schema for end-users.

Objects on the design pane match real-world objects

Complex real-world entities are represented in a relational database by several tables. Assembling them in a single object on the design pane will give a more clear view of the database schema.

Hiding intermediate objects

Hiding objects which are only used to define many-to-many relationships from the Design Pane will simplify the query diagram.

Trivial actions must be trivial

Users will be able to define conditions, grouping and sorting right from the Design pane.

We hope these improvements will meet your expectations for a convenient query building interface. Please feel free to submit your feedback.

We are working on the possibility to define SQL queries in a domain-specific language. The API will allow for type-safe SQL construction, database object referencing through the generated database schema classes like the following:

sql = DSL.query
    .Select(
        Orders.OrderId.As("id")
      , SqlExpression.Count(Orders.OrderId)
    )
    .From(Orders)
        .InnerJoin(Customers)
            .On(Orders.OrderId == Customers.CustomerId)
    .Where(Orders.OrderId.Greater(10))
        .And("1 < 2")
    .Having(Orders.OrderId.Count().Greater(5))
    .OrderBy(Orders.OrderId)
    .GetSQL;

So, when the database schema is changed, the programmer can re-generate the code of your database schema classes and get compilation errors in case invalid objects or fields are used in queries defined via DSL API.

Consider a situation when programmers have to define lots of SQL queries for reports, analytics and similar tasks saved as string resources. Changing the schema may lead to a case when some queries become invalid, and the only way to check for this is to run that report. If they are defined via such DSL API, the programmer will be notified about the problem at compile time.

Would you be interested in using such API in your projects?

We'll be grateful for your feedback.

Sometimes we get a question from our customers: what do we get on upgrading Active Query Builder to the third version?

Honestly saying, almost nothing. But your end-users will get a lot! :)

Most of the core and API improvements have been delayed to the next major version. The component's user interface has been significantly changed to improve the user experience.

In this article, we tried to sum up more than four years of the component development.

Means to customize the user interface

• The visual interface now consists of separate controls. You can combine them the way you like.
• Dockable panels help to keep essential controls at your fingertips without clogging the UI.

Usability

• Buttons next to fields involved in foreign key relationships let add linked objects in a single click.
• The new properties bar allows setting the query, link and datasource properties in a handy way.
• The new Sub-query Navigation Bar lets work with UNION sub-queries and Common Table Expression (sub-queries in the WITH clause) conveniently.
• Search and filtration in fields of datasources on the Design pane helps quickly find the needed field.
• Reusable queries panel allows for organising user queries and using them in other queries just like ordinary database views.

Care

• New tips help users eliminate common issues in their queries.
• Smart notifications about erroneous SQL expressions the user typed let quickly switch to the place where error origins and correct the mistake.

Speed

• Speeded-up database schema tree loads thousands of objects almost instantly.
• Optimized metadata loading process.

If you have any thoughts about what we can do better in Active Query Builder, please share them in the comments to this article.