Dimensions or Attributes in Solver
Solver Cloud is a reporting and planning solution that uses a Microsoft Azure SQL-based Data Warehouse for data storage. To get the most out of your reporting and planning, having a well-designed Data Warehouse is important. The design is important because it facilitates faster reporting, easier input, and less storage space.
One object used in a Data Warehouse is a Fact Table. Fact tables store the data you’re trying to report or enter in a planning template. For example, General Ledger and Purchases are examples of Fact Tables. Within each Fact Table are the columns which store data. To achieve the best design, you will need to decide which columns are dimensions and which are attributes. This article is going to discuss the uses of dimensions and attributes.
What’s a dimension, you ask? A dimension is a table that stores information used by a fact table. Here’s an example of some dimensions:
Each of these dimensions will have a code, like an account number, and a description, like the account description. What you don’t see is a column called Member ID. That column is an integer and stores a unique value for each dimension value. Here’s an example:
As you can see, there are several columns that describe the account. These columns, starting with “Code” and ending with “Income or Balance,” are called attributes. Each dimension table can have many attributes. Imagine all the information you might use to describe a customer or vendor.
We mentioned above an object called a fact table. A fact table stores the records that are the object of your reports. For example, account transactions is an example of a fact table. Another example would be sales transactions.
Here’s an example:
Looking at the fact table, there are columns that could be dimensions and columns that could be attributes. For example, when looking at the account transactions, you may wonder if Department should be an attribute or dimension.
What’s the Difference?
If you have values such as an account number, which would have descriptive information about it, like what was shown earlier, then you should use a dimension. Otherwise, to know the account description and other account information, you would have to add a column for each one, giving you a lot of repeating information. That repeating information increases the number of columns and makes the table bigger. By using a dimension for the account, the report will run faster as you add more records to the fact table.
Some columns, though, can easily be stored as attributes. For example, it wouldn’t make sense to add the invoice number, while the same for two or more rows, into a dimension table. The reference also should be an attribute in the fact table. Can you imagine creating a dimension table of every reference used in your fact table?
Here is an example of a fact table surrounded by dimensions.
This layout, with the module (fact table) in the middle surrounded by the dimensions in a loose star shape, is called a Star Schema.
Why Do I Care?
We described above the performance as the number of records increased and the number of columns. Look at the same dimensions and fact table in Power BI when you’re trying to create a report.
Notice how easy it is to understand where columns reside? Want to find the account description? Look in the Dimension Account table. Want to find a department description? Look in the Dimension Department table.
Need more convincing? Watch a YouTube video called “Why Power BI loves a Star Schema” by Patrick from the Guy in a Cube channel. In about eight minutes, Patrick walks through the benefits of the Star Schema.
The tables shown above are from the Solver Corporate Performance Management solution. Solver enables you to create dimensions and modules (fact tables) without any programming. You should talk to a person with some business analytics knowledge to make sure the design you create is going to hold up under thousands or more records.
With Solver, we can create dimensions when the source data does not have them. For example, what if your source data has a salesperson data but you also want a territory? You could have territory as an attribute of the salesperson dimension.
You can do that and use “Territory” in reports.
There is one limitation, however, that’s important to understand. In Solver reports, a common desire is to use a Sheet Per Value function. What does that mean? It means you can create a report that will automatically generate a separate sheet for each value. For example, you may want a sales report that shows sales per territory with each territory on a separate sheet.
To handle this requirement, Solver wants you to use dimensions instead of attributes. Thus, instead of using the territory in the salesperson dimension, you would create a dimension called Territory. Our sales module table now looks like this:
The question is, how do you populate the Territory column when the source data doesn’t have it? It’s quite easy in Solver. We’re going to handle this need in two steps:
- Keep the Territory column in the Salesperson dimension table, but instead of spelled out territories, create territory codes.
- Create a rule that will populate the fact table’s territory code with the territory code from the salesperson.
For example, let’s say the salesperson on a transaction is Charles. The source data does not include the territory, so that column will be blank when you load the data. The rule will be run after the data is loaded and match the salesperson in the module to the salesperson in the dimension. Then it will grab the territory code and update the module’s territory code.
Here’s our dimension table:
Here’s our module table after loading sales data:
Here’s the same table after running our rule to update the territory:
Now the module has the territory, and we can build reports that use the territory. The reports will be faster because we’re using a dimension and we can use the Sheet Per Value functionality in Report Designer.
Here are the tables in Solver:
Here’s the module data:
Here is the report; notice there is a sheet for each territory:
How Does This Apply to Me?
There are a few takeaways:
- Dimensions are best when there is information about the dimension that doesn’t change with each transaction.
- Attributes are best when it doesn’t make sense to create a dimension for them.
- Solver reports that use the Sheet Per Value functionality need dimensions, not attributes.