Tag Archives: Table Design

Creating rounded corners in Excel – revisited

Today we’re revisiting one of our more popular guides, Creating rounded corners in Excel Tables, and have updated it for v7.1. When Igor Asselbergs was contemplating the value of round corners in design, he came to the conclusion that in many cases they added real value to the user experience.

The effect can be explained by the Gestalt Law of ContinuityGestalt is a set of rules based on research into perception psychology, and a very powerful tool for Excel table design. In table design this effect can help us to see the table columns as a unit.

The previous process to create rounded corners in Excel tables required quite a bit of persistence and patience. In Version 7.1, we’ve introduced a feature to enable adding rounded corners in a few seconds rather than several minutes, so while the theory is identical the implementation is much improved. Take this report showing sales KPIs, where we would like to add rounded corners to the header row in the table.

To do this we first highlight the required area:


Then we go to Extras -> Add/Edit Round Corners:


The Colours and Border thickness will be picked up from the selected cells. Select the corners to be made round (in this case the Top Left and Top Right corners):


Click OK to apply the borders

 

To edit existing corners which were created by XLCubed then you can just highlight the cell or range and Go to Extras -> Add/Edit Round Corners. The changes will be applied to the existing corners (or the corners can be removed by unselecting them).

It’s a simple addition to the product which would have saved us quite a bit of time in customer implementations over the years, and hopefully now does the same for our users.

One slicer, two reports!

So today’s blog is going to show you how easy it is in XLCubed to have a slicer driving a grid and a SQL table at the same time.  There may be occasions when some of the information you require for your report is held not in an analysis services cube but a SQL table.  So you’ve created a grid report with a slicer like below:

 

 

This is a simple report with Geography on headers and Product Model Categories on rows showing Reseller Sales Amount with the Country slicer driving the grid.  The slicer is set to update cell B9 with the slicer choice.

 

So I show this to my manager and he asks for some more detail – he wants to know what type of businesses there are in each country, their names and the number of employees.  That’s when I realise that all of this extra information is not in my cube but on a completely separate SQL table.

Not a problem for XLCubed!  I can quickly create a report that includes all this data from the SQL table.  Using the SQL option within Grids & Tables I can create a report that connects to a relational SQL data source.

Create my connection to my data source – I am selecting the AdventureWorksSDW database:

Let’s build up my SQL query – I’m using the DimReseller and DimGeography tables to return the required fields.

My SQL statement is:

Select DimReseller.BusinessType, DimReseller.ResellerName, DimReseller.NumberEmployees, DimGeography.EnglishCountryRegionName From DimReseller Inner Join DimGeography On DimGeography.GeographyKey = DimReseller.GeographyKey

This is great but it returns data for all the countries and I only want to see data for the country chosen through the slicer.  So let’s add a parameter to our SQL query.

If you look at the corner of the SQL query window you will see the parameters area – with a very helpful tip on adding a named parameter.

 

 

Let’s add the following to the end of our SQL query:

where DimGeography.EnglishCountryRegionName = @parm1

Now we can define where the Excel range is for our parameter – in our example it is cell B9.  You remember that this is the cell that the slicer has been set up to output the slicer choice.

So now when we select a country from our slicer eg United States the grid refreshes as well as the table.

 

Parent-Child Dimensions in Analysis Services – Performance Walkthrough

Parent-child hierarchies are a good fit for many data structures such as accounts or employees, and while they can speed development in some cases, they can also cause performance problems in large cubes.

We often see customers with these type of performance issues, and thought it worth sharing a simple technique for altering the dimension structure to improve query speed.

The problem

Often parent-child hierarchies are created as this is the structure used in the relational source, so they seem a good fit to model the members. In many cases though data is only at the leaf level of the hierachy, meaning parent-child isn’t really needed.

Performance problems occur because no aggregates are created for parent-child dimensions, as detailed in the Analysis Services performance guide:

Parent-child hierarchies

Parent-child hierarchies are hierarchies with a variable number of levels, as determined by a recursive relationship between a child attribute and a parent attribute. Parent-child hierarchies are typically used to represent a financial chart of accounts or an organizational chart. In parent-child hierarchies, aggregations are created only for the key attribute and the top attribute, i.e., the All attribute unless it is disabled. As such, refrain from using parent-child hierarchies that contain large numbers of members at intermediate levels of the hierarchy. Additionally, you should limit the number of parent-child hierarchies in your cube.

If you are in a design scenario with a large parent-child hierarchy (greater than 250,000 members), you may want to consider altering the source schema to re-organize part or all of the hierarchy into a user hierarchy with a fixed number of levels. Once the data has been reorganized into the user hierarchy, you can use the Hide Member If property of each level to hide the redundant or missing members.

 

The performance guide hints at re-organizing the hierarchy to improve perfomance, but doesn’t say how.

The solution

This article will walkthrough the steps needed to change your parent-child hierarchy structure to have real levels, so that aggregations work, and your performance is as good as you expect with normal hierarchies.

This process is known as flattening or normalizing the parent-child hierarchy.

Firstly, let’s look at the data in our relational source.

Code: Sql Create ScriptSelectShow

Not a large dimension, but enough to demonstrate the technique. As you can see my real products are all at the leaf level.

The strategy is quite simple:

  • Create a view to seperate the members into different levels.
  • Create a new dimension using these real levels.
  • Configure the dimension to appear like the original parent-child dimension, but with the performance of a normal dimension.

Create the view

We want to create a denormalised view of the data. To do this we join the Product to itself once for each level. This does mean we need to know the maximum depth of the hierarchy, but often this is fixed, and we’ll build in some extra levels for safety.

The tricks here are:

  • Use coalesce() so that we always get the lowest level ID below the leaves, never a NULL. This allows us to join to the fact table at the bottom level of our hierarchy.
  • Leave Name columns null below the leaves, this will allow us to stop the hierarchy at the correct leaf level in each part of the hierarchy.

Code: Sql View ScriptSelectShow

Running this we get:

Obviously we can update this view to create more levels as required, but 5 are enough for now.

The Dimension

Next we go to BIDS, and add the view to our Data Source View, and then add a new Dimension based on the view.

The key steps to creating the dimension correctly are:

  • Set the key attribute to Level5ID, and the name to Level5Name.
  • Create an attribute for each Level ID, and on each set the Name Column appropriately.
  • Create a hierarchy using these attributes in order.
  • On each attribute set AttributeHierarchyVisible to False.
  • On each level of the hierarchy set HideMemberIf to NoName.
  • Set up the Attribute Relationships between the levels.

You should end up with the following:

Dimension Structure

 

Attribute Relationships

 

If you browse the dimension you’ll see that it never goes as far as level 5, even though it exists. This is becuase we set up the member hiding option, and returned NULLs in our view.

Conclusion

And that’s it done, you can now join to your fact tables at the lowest level, build your cube as normal and get the performance benefits of aggregation!

See also

A tool to achieve the same result is available from Codeplex, we’ve not personally tried it but may well be a timesaver. This works in a similar way to the example above, but it’s often useful to understand how something works, even if you choose to automate it.

Hermann Grids, An optical Illusion Best Avoided

An interesting optical illusion is the so-called Hermann Grid illusion: the effect of seeing gray dots at the intersections of a black grid on a white background or a white grid on a black background.

image_thumb3

 

 

 

 

While it’s an interesting optical illusion, it’s something we should avoid in management reporting:

image_thumb16

 

 

 

 

 

 

 

 

 

Tables formatted with medium or thick black or gray borders tend to produce Hermann Grids. Just scan the table above and you should see the gray dots in the grid intersections.

To avoid this unpleasant and distracting effect, and to maximize the data-ink ratio follow this simple but very effective table design rule:

  • Avoid using dark and heavy grids
  • Use light gray grids instead

image_thumb19

 

 

 

 

 

 

 

 

 

Above is the same table with light gray borders. This eliminates the Hermann Grid illusion and – by de-emphasizing the grid –  puts more emphasis on the numbers.

Here are some images I found on Google Image Search that show how popular it is to put your data behind Hermann Grids:

image_thumb8

 

 

 

 

 

 

 

 

 

 

image_thumb11

 

 

 

 

 

 

 

 

 

 

So I hope you are with me – get rid of the heavy grids and free your data!

Applied Gestalt Laws: Table Alignment

Most reports are based on combinations of tabular layouts, so to continue my series about visual design (see my previous post) I will focus on the most common and simple problem to fix: The fundamentals of how to align numbers and text in tables and how to treat their headings.

Here are the rules

  • Right-align a block or column of whole numbers or of whole numbers and text.
  • Left-align a block or column of whole text.
  • Align numbers at the decimal point (or imaginary decimal point).

clip_image001

 

 

 

 

 

 

Seems obvious really but they are so often rarely applied. A Google image search on “excel table” reveals what most Excel users do

….they simply use what Excel defaults to:

clip_image002

 

 

 

 

 

 

…or if people are more adventurous often feel that centered columns would somehow looks better:

clip_image003

 

 

 

 

 

 

…or even worse they apply the Excel Tables styles:

clip_image005

 

 

 

 

 

 

All those habits make the table more difficult to read. To understand why this is the case let’s use the Gestalt Law of Proximity.

clip_image007

 

 

 

 

In the picture above my brain tells me that there are 6 columns of 9 dots in one group. Simply moving the dots of the first row to the left breaks this grouping and differentiates the dots into 2 groups

clip_image009

 

 

 

 

 

This is exactly what happens if you left align column headers on numerical columns: As shown below and the brain does not associate them anymore which is what I want in most cases for headings and numbers.

So Excel Defaults are not right as shown below.

clip_image011

 

 

 

 

 

 

Right aligning the headers brings them together.

clip_image013

 

 

 

 

 

 

The grouping still works even if the shapes have a different width but remain either right or left aligned:

clip_image015

 

 

 

 

 

 

The reason for this is explained by the next Gestalt law of Continuity, the right aligned figures and the left aligned text are perceived as columns

The table below shows this affect with the arrows showing the continuation of the series and the same works with columns of left or right aligned figures or text, we perpetuate the series and perceive the column as one object. Even inserting a row to visually separate the figures and the column headers does not break the grouping…

clip_image017

 

 

 

 

 

 

….what can be explained by the Gestalt law of Closure.

Hence we perceive the columns of numbers and headers still as a unit even though the headers are placed somewhat apart from the figures.

clip_image019

 

 

 

 

 

 

If we now disable the Excel grid lines we end up with table which merely relies upon white space and Gestalt laws to format the table providing clear associations: A first class table.

clip_image020

 

 

 

 

In western cultures we read text from left to right so it makes a lot of sense to left align text columns but not so for numeric columns. The eye has to search for the decimal point to get to the ten, hundred or thousand digit, this makes comparing numbers quite difficult if not impossible when many numbers are involved.

clip_image022

 

 

 

 

 

 

Here the Gestalt Law of Continuity can help, simply right aligning brings all tens, hundred, thousand digits on the same virtual line and makes comparison straightforward and simple.

clip_image024

 

 

 

 

 

 

Unsurprisingly, centering numbers in column causes exactly the same problem as shown below. Another visualization “No No”.

clip_image026

 

 

 

 

 

 

Interestingly, the same rules apply when we move beyond simple text and numbers to MicroCharts such as sparklines, column charts and bullet graphs. Especially when the sparklines contain m
issing values.

clip_image027

 

 

 

 

 

 

Right or center alignment leads to severe difficulty comparing values of the same period in different rows in the table.

clip_image028

 

 

 

 

 

 

If the sparklines have the same amount of data points this is not an issue but in dynamic reports this may not always be the case so its better to be safe than sorry.

clip_image029

 

 

 

 

 

 

When using visual tables another nice trick to is to introduce an axis to a column chart to aid in the visual alignment and to group periods into blocks through the alternate shadings. The column chart above use a column chart to visualize units sold and an area chart for the other measures. The different shading groups the periods into 6 month units and the column bars aids the visual alignment. So to recap, make it easy for people to read your tables by following how your brain inherently processes information as explained through “Gestalt” laws. Here are the rules again

  • Right-align a block or column of whole numbers or of whole numbers and text.
  • Left-align a block or column of whole text.
  • Align numbers at the decimal point (or imaginary decimal point).

I hope that this article has been useful and I look forward to dealing with other visualization techniques in later posts.