From Table Relationships to Slicer Comparisons, Experience Dundas BI Version 7

For more details on Dundas BI Version 7, check out these blogs:

Luckily, just like sandwiches, there is some serious meat to the next three major features added to version 7 that are very much worth writing about. Dundas BI is synonymous with flexibility and was just ranked #1 for Flexibility in BARC’s The BI Survey 19. The features that I’m about to highlight all focus on providing even more flexibility to what’s already the most flexible Business Intelligence (BI) software.


New Measure Options – Median, Mode (Max), Mode (Min)

When building datasets in Dundas BI, you’ve always had a lot of control over the type of aggregation that you want to use. For example, you might have a number that you’re grouping by something (such as continent) and aggregators let you choose how you can group the data so that you can get it down to a single value for the grouping.

If you’re not familiar with this concept, here’s a quick picture to help explain why aggregation is important. It’s all about taking raw data and grouping it into a single value, and in this case, multiple data points into a single bar in a Bar Chart.

Notice how the raw data doesn’t fit evenly into each bar? How do you collapse two Africas, and multiple Oceanias into a single bar?

That’s why aggregators are important as they solve this problem! You could add up all the values using the SUM operator, or if that’s not your cup of tea, could average them to make them fit. It all depends on what you’re hoping to achieve.

These aggregators exist to help you properly structure the data the way that you need to have it. Dundas BI offers a large set of aggregators that you can choose from, and with the release of Dundas BI version 7, you now have some new options to allow for Mode (most common number) and Median (middle number) calculations.

The neat thing about these options is that without these built-in aggregators, they would typically not be very easy to calculate. For example, if you were to try to do the same calculations using a SQL Query, some of them would require quite a bit of code. Just calculating the Mode would have taken a script like this, which has been completely replaced with a single click.

SELECT TOP 1 with ties DATA
FROM @Temp

Now you have many aggregators available to make sure that you can group your data to meet both basic and more complex calculation requirements.


Slicer Comparison

Have you ever looked at a visualization that shows a total and wondered, “How does my portion contribute to the total?” The new Slicer Comparison is a specialized type of filter that allows for exactly this possibility.

Here is a quick example to show you what I mean. The following Line Chart shows the total C02 output globally over time.

Using the new Slicer Comparison, the user can choose one or more countries to compare this visualization against. In this example, we’ve even gone a bit further by having an additional layer pop-up to provide even more information in the comparison.

Let’s see what happens when we choose China, the United Kingdom, and the United States. Now we can understand how these countries contribute to the whole, right on the dashboard as we perform real-time data-discovery.

The Slicer Comparison is also available for any visualization that will allow you to create interesting infographic style dashboards if you’re looking for something different. I’ve thrown all best practices out the window with this example and tried to provide a fun way to compare your car to the average specs in the industry. Here’s how your Toyota Corolla stands up against industry averages.

This feature is also very useful if you simply need to show multiple versions of the same measure with different characteristics in a single visualization without the interactivity. In the table below, we have revenue by year, but what if the business is also interested in seeing an additional breakdown of the data for North America and South America together?

By adding a Slicer Comparison, we can add a new series that is drawn from different filter values.

And the result:


Table Relationships – Data Flow

If you’re doing data-discovery or content creation and you’re working with data that someone else has provided, it’s often hard to understand the relationships between all the data. For example, you might be working with the Sales Order Detail table, but wouldn’t it be neat to drill-down to specific Work Orders from a sale? Can you even drill into this data? It really depends if there is a relationship between the tables that you are using.

If you’re familiar with this idea, the relationship is critical because it’s the mechanism for which different tables are joined by defining what is connected and how. Usually, these relationships are clued about in a database to make things easy for analysis, performance, and accuracy, but often they are not defined, which causes usability concerns for third-party tools.

Business Intelligence tools like Dundas BI give you the ability to define relationships outside of the database if they don’t exist, but there is still a problem because it’s hard to visually understand how things are connected if you didn’t design the database. It’s unlikely that a lot of database engineers will provide you with a diagram to help map out the database for you.

Dundas BI version 7 has gone a long way to unveiling the mystery by adding a new Data Flow button to help users understand existing relationships using a clean graphical representation. By pressing the Data Flow button on an existing metric set, you can instantly see both the relationships that went into building your current metric set and the existing relationships that could help augment your visualization even further.

From my earlier rhetoric on drilling-down to the Work Order, notice below that we can, in fact, connect our Sales Order Data to our Work Order data since a relationship does exist. We can even see the steps required to get there and how’s it’s related.

You can also modify the relationship itself by right-clicking on any table in this view and clicking on Relationship Details. This functionality is significant because it means that both power users and analysts can modify the different join types being used between tables.

You’d particularly want this if you’re not the owner of the data so that you are no longer at the mercy of something that you don’t control in order to get the results that you want.

There’s also one additional benefit in that in this single location of the application, you can also add new relationships as you go. All you’d need to do is right-click and add a relationship using a very similar screen from above.

Dundas BI 7 is here, but that doesn’t mean the innovation is coming to a stop. Our platform is constantly moving forward, evolving, and bringing you cutting-edge capabilities to handle your data analytics. This blog offers just a glimpse into this powerful, new version. There’s plenty of new features and capabilities in this release, and even more on the horizon. I hope you’re as excited as I am!

Interested in seeing what else Dundas BI 7 has to offer? Schedule a personal live demo with a Dundas Solution Architect or start your free evaluation of Dundas BI 7 today! With Dundas BI, the possibilities are infinite.


About the Author

Jeff Hainsworth

Jeff Hainsworth is a Senior Solutions Architect at Dundas Data Visualization with over a decade and a half of experience in Business Intelligence. He has a passion for building, coding and everything visual – you know, shiny things! Check out "Off the Charts... with Jeff", his platform for great content on all things analytics, data visualizations, dashboards, and business intelligence. There’s something for everyone!