Using Attribute Relations in Your Dimensions – Dimensions Part 3 | SharePointMike's Blog
Analysis Services is optimized to improve performance of dimensions where rigid relationships are defined. As a result, rigid relationships. Yes it would, but without defining attribute relationships, SSAS does not make these The Relationship type can be either Flexible or Rigid. It contains the. 'SSAS Tutorial' as a project, which consists 'Data Sources', 'Data Source Views', 'Cubes', . parts, i.e. Dimension Structure, Attribute Relationships, Translations and Browser. . Edit the following relationships and change their Relationship Type setting to 'Rigid': .. Set the 'Status Indicator' to 'Traffic Light'.
The OLAP data might go back 2 years, or 5 years, or even further. Additionally, a company may only incrementally update a small percent of data at any one time, leaving most of the remaining historical data as static.
Analysis Services allows developers to create physical partitions on fact tables.
Clarification on Flexible vs Rigid Attribute Relationships
By defining rules for what data appears in what OLAP partition, developers can greatly increase query performance and processing performance. Developers can even define partitions on different servers for load balancing. If end users query data that spans partitions, the Analysis Services engine can transparently query the data in parallel executions. So one of the primary tasks is to define a logical partition approach: The SSAS engine will not validate the logic of a partitioning scheme.
The Analysis Services cube editor in Solution Explorer contains a tab for partitions. By default, the Cube Wizard created a full partition for each fact table. A first-time developer might conclude that the first step to creating a new partition is to click the New Partition link - actually, before that, the developer must click the ellipsis button to the right of the partition source and modify the definition of the partition to use a SQL query to only read a subset of the data.
After doing so, the developer can then click the New Partition button to enter the correct T-SQL query for the definition of the archive partition. Whatever the requirements, partitioning can have a dramatic impact on day-to-day performance. I can build in aggregations as well. This surprises many who are impressed with how rapidly the SSAS engine will roll up aggregate measures from the lowest dimension level to a high dimension level.
Users may note that queries at the year and zone and product brand level have slowed from seconds to minutes - maybe gradually over time, or immediately upon the data team loading a new source of data. Even before I cover the tools to create built-in aggregations, I want to mention a feature in Analysis Services that allows SSAS administrators to view past queries by user to get a handle on which users are waiting the longest for results.
You can use the results from this output towards building an aggregation strategy. A strategy can include one or two aggregations, or even dozens. A common recommendation is to build the fewest aggregations that will positively impact the greatest number of end users. While it might be tempting to add an aggregation at the date year level and geography country level, such an aggregation will not help those who query at the quarter and state level.
So this process requires careful analysis to yield any positive outcome. So a database might have different sets of aggregations by partition. The next screen Figure 19 presents all the valid related dimensions and attributes for the partition sand I can check which attributes will comprise the aggregation. After re-deploying the OLAP database, the partition will contain the stored aggregations. So any queries where the specified dimension level is satisfied by any of the aggregation definitions will use that specific aggregation definition, and not roll up data on the fly - which can dramatically improve performance.
So what are downsides of aggregation? It will increase the size of the partition, though this may not be a serious issue. Once again, the use of attribute relationships may help with this process. End users might need to see weighted ratios or other weighted calculations, expressed in terms of the current year or the previous year.
You can place predefined calculated members into the cube, using the MDX programming language. In other instances, OLAP reports might need repeatable sets of data - such as top 10 products, top 10 customers, or a hierarchical list of geography elements from a country down to a city level.
In these situations, custom MDX code is required. Calculated Member and Set screen. You may be wondering - OK, but where do I pass parameters into these calculations? Remember, the whole idea of OLAP is to automatically slice measures based on dimension usage and dimension relationships: MDX calculated members follow the same pattern.
When we place these calculations in a report or simply an Excel OLAP Pivot Table, the SSAS engine will automatically slice the underlying data in the calculation by the related dimensions we specify at runtime. Analysis Services allows developers to create simple visualizations of performance metrics. Are we at our sales goal? We send out customer surveys once per quarter - are the survey evaluation results above or below our goals?
Generally speaking, most basic KPIs return one of three values for the current runtime selections: A value of 0 is a grey area: In the KPI, I can define the ranges and thresholds either based on static numbers or data that comes from another fact table.
SSAS also allows us to render a value of 1, 0, and -1 using different indicators green-red-yellow traffic lights, smiley faces, etc. Optionally, a KPI can have a trend definition. By contrast, maybe the company is doing well this period, but was doing better last year. Once again, we use a value of 1 to indicate a positive business trend, a -1 to indicate a negative business trend, and a 0 to indicate minimal or no trend.
Figure 21 shows how I can create a KPI with the following information: The name for the KPI: There are several ways to implement XMLA scripts. Suppose my ETL process encounters four new accounts, three new products, and two modified products corrections were made to the two products. Suppose I want to completely rebuild one or more fact tables or partitions from scratch. This process will delete the contents of the intended target OLAP objects.
In this instance, make certain that the underlying source data has all the data you wish to bring forward. I can use Process Incremental on the fact table or partition. Those three scenarios are arguably the most common of all. I could write an entire article on this topic alone, but here are some additional notes on this topic: If a processing environment uses Process Update often on dimensions, the environment should also use Process Index on occasion.
Process Index will rebuild dimension bitmap indexes. Your options are either to make the attribute relationship flexible instead of rigidor use Process Full on the dimension. While Process Incremental might seem like an attractive option for trickle-feeding new OLAP fact table data, some view the process as slightly fragile.
Define 'Rigid' attribute relationships when possible
Some installations will build many smaller OLAP partitions, and use a process full on just that specific partition. In OLAP, just like in life, there are red rules rules you always follow and blue rules rules you use as guidelines. ROLAP is an interesting feature. You still create an SSAS solution. End users get near-instant access to data. Almost the very second after the underlying relational source append new rows, end users can access the data from the OLAP interface.
It almost sounds too good to be true - end users get the benefit of OLAP functionality, but without the management and maintenance issues of updating OLAP data. However, there can be one major drawback: You can watch this yourself using SQL Profiler. Additionally, you might need to define your OLAP data source connection string in the SSAS project to use the Snapshot isolation level, if read locks otherwise occur when the SSAS engine reads the underlying source using the default isolation level of Read committed.
The general industry recommendation to avoid ROLAP if the underlying logical data model is complex i. To enable the ROLAP feature, you right-click and access the properties for the cube - fact table - partition - measure, and go to the property called Proactive Caching. HOLAP is the rarest of the three storage methodologies.
Suppose users are browsing an OLAP pivot table of product sales with Microsoft Excel, and want to quickly access a multimedia web page of product information for a specific product. Suppose users are browsing an OLAP pivot table again in Exceland drill down as far as they can in the cube, to see employee sales by day.
For our date example, I actually create a new data dimension, Date TEST, based on the existing date view in the data source view. As shown below, year, quarter, month, week, and date were added as attributes to the dimension. As noted in the second screen print, SSAS displays a caution triangle and displays a warning that says that no attribute relationships exists in the hierarchy and as such performance may be impacted.
Thus, we now know we should create an attribute relationship, but it is not required. To create the relationship we need to click on the Attribute Relationship tab. As shown in the following illustration, we currently do have attribute relationships setup; however all of them directly link each attribute back to the Date Key, the lowest level. No intermediate relationships exist to efficiently rollup the hierarchy levels.
You will also notice that the attribute relationship tab contains two views. The graphical view is on the top of the below screen print while the detail list view is shown at the bottom. In order to create a relationship, we can either use the drag and drop method or manual add method via the tool bar button. As shown below, to create our relationship, we actually start at the lowest level and then drag our attribute to the next higher level value. In our example, the Date key is already related to each of the four attributes in the hierarchy.
As such, our first level of the relationship, which is Date Key to Week, has already been established. As you can see in the below illustration, we now have established the Date Key to Calendar Week to Calendar Month relationships.
We could continue to use the drag and drop method to complete our relationship setup or we can use the New Attribute Relationship button. Similarly, products exist within subcategories. Subcategories do not exist within products. Another good example of a natural hierarchy might be to begin with years and drill down to quarters, then months, and finally days of the year.
Yes, it is possible that you may want to look at the sales for the fourth quarter across all of the years for which you have sales data, but that is a future discussion. Sometimes, it makes sense for the developer to predefine the hierarchy in the cube definition so that the user need drag only the hierarchy definition to the row or column of the table.
This insures that the user places the attributes in the correct order. But that is not the only reason to predefine the attribute hierarchy. By default, SSAS automatically relates every attribute in a dimension to the dimension key. In the example above, that dimension key would be the Product ID.
Type 1 and 2 Relationships and Dimension Design in SSAS | Jen Stirrup
Therefore, even subcategories are related to the appropriate Product ID. Similarly, every category is related to its appropriate Product ID. SSAS can scan through the Contoso retail sales data for sales belonging to the Recording Pen subcategory and sum the values.
Similarly, if you wanted to know the total sales of audio products, SSAS can scan through the Contoso retail sales data summing sales where the product belonged to the product category, Audio. While this method returns the correct answer, SSAS has is a faster method. SSAS stores intermediate values of measures from the fact table by the dimensions defined in the cube. In other words, it stores sums of sales by product ID if product ID is a dimension key.
However, it does not store intermediate values for other dimension attributes such as subcategory or category. To get the sum of sales for categories, it adds the previously calculated sales for each product in the subcategory. Similarly, to report the sales by category, it adds the sales for each product in the category.
Granted, for the size of the Contoso Retail cube, this difference in how long it takes to calculate values for higher level groups in a hierarchy may be insignificant. On the other hand, if you have a large cube with millions of rows of data, this difference can become significant. To begin, open the Date dimension and click on the Dimension Structure tab. Drag the fields you want to use as attributes from the Data Source View to the Attributes panel.
Note that you do not have to include all attributes. In fact, the larger your database, the more selective of the attributes you will want to be in order to maintain good performance.
The following figure shows the selection of attributes I decided to include in my Date dimension. Next, click on the Attribute Relationships tab at the top of the screen. This shows the three panels shown in the next figure. The top panel graphically displays the currently relationships.