In this final, 1-hour video of this course focused on SSAS cube design, Chris tackles the goal we all aspire to: performance. Aggregations are one of the easiest ways to improve the performance of your queries, assuming that those queries do a significant amount of work in the Storage Engine. An aggregation is a pre-aggregated copy of data in a fact table, like the output of a SQL GROUP BY query, created by SSAS at processing time. If an aggregation exists, it allows SSAS to avoid having to do a lot of expensive calculations when a query runs.

The more aggregations you build, the slower your processing becomes and the more disk space you need. That said, there is usually a happy medium that can be found where your cube processing is not unduly affected, but your query performance is boosted sufficiently. If you were to pre-aggregate all your data, you would find cube processing times would increase exponentially and the size of the cube would explode.

Aggregations are only useful if you have at least a few million rows of data in a partition, and your queries are slow, because SSAS has to do a lot of work to aggregate data at query time. They are no use if your queries are slow because of MDX calculations.

The aggregations that you want to build for a measure group are specified in an object called an Aggregation Design. When you associate an Aggregation Design with a partition, then the aggregations listed in the Aggregation Design will be built for that partition, as we show in the detailed demo.

Before you design aggregations, you must ensure that your dimensions are not going to change significantly in the near future because of dimension design. Especially, attribute relationships can have a big impact on the effectiveness of aggregations. After that, you will need to set a number of properties which effect how the aggregation wizards behave, such as the various count properties and the AggregationUsage property, and then you will be ready to run the Aggregation Design wizard, which Chris shows in the video, showing you how to build aggregations. This wizard designs aggregations based on the structure of your cube and dimensions, and can be a little hit-and-miss. Once you have deployed into production, you should turn on the Query Log and then run the Usage-Based Optimisation Wizard. If all else fails you can even design aggregations manually in SSDT, although this can be a very difficult and time-consuming task. After having designed aggregations and processed them, you should use a Profiler trace to determine whether your queries are using these new aggregations—watch Chris profile queries to find out if his aggregations are being used.

Bear in mind, that when designing aggregations, and especially when designing aggregations manually, you must be aware of cube design features that can affect how aggregations are used such as many-to-many relationships, semi-additive measures, partitioning strategies, parent-child hierarchies and MDX calculations. Make sure to watch the previous modules in this course, which explain these subjects in great detail.

• 31 October 2015
• 5197 views

In this 30-minute video Chris Webb discusses the operational side of cubes: deployment, processing, backup and restore, synchronization, and scripting.

Although you can deploy changes from Visual Studio to your SQL Server Analysis Services database directly by using the Deploy option, this can be dangerous: roles and partitions created outside the project could be overwritten. As a result, it is safer to use the the Deployment Wizard to handle deployments to production—it gives you a lot of control over what gets deployed from your project, and what doesn’t.

Partitioning your measure groups can improve both processing performance and query performance. Creating new partitions in a measure group is a task that is commonly automated. Probably the easiest way to do this is to create an XMLA script that creates a new partition in SQL Server Management Studio and execute this—although you can, of course, build partitions manually in Visual Studio, too. These XMLA scripts can be executed in a number of places, including from within SQL Server Integration Services.

Processing is the act of loading data from a data source, usually a relational data warehouse, into Analysis Services. Dimensions, partitions, measure groups, cubes and entire databases can be processed. A number of different types of processing are available in each case. For dimensions, you have Process Full, Process Update, Process Add, Process Clear and Process Default. For partitions, you have Process Full, Process Add, Process Data, Process Index and Process Default. Using different combinations of these types of processing can reduce your overall processing times.

Other important administrative tasks include backup and restore and synchronization, which allows you to copy data from one Analysis Services data to another. Synchronization is useful in situations where you need to process your database on a different machine to the one that your end users are querying.

• 10 September 2015
• 1297 views

In this 30-minute video, part of our Cube Design series, Chris Webb explains the SQL Server Analysis Services security. SSAS security is role-based, and all authentication is performed via Windows authentication—it is not possible to create usernames and passwords in SSAS. Users can be members of multiple roles, and in that case a user will have the accumulated permissions from all of the roles they are members of.

There are two types of SSAS security: administrative security and data security. Administrative security can be set at the server level and the database level, and it controls the ability to do things like: create or delete objects, process, and script to XMLA.

Data security controls what data users can see when they browse the cube. Data security can be set at three levels: at the cube level, at the dimension hierarchy level, and at the level of the individual cell. At the cube level, users can either query a cube or not. Dimension security, the most commonly-used type of security, controls which members on a dimension hierarchy a user can see. It is set up on the Dimension Data tab in the Role Editor in Visual Studio. Once you have done that, you can test the role in the Cube Browser tab by pressing the Change User button, or by using the EffectiveUserName or Roles connection string properties. Cell security controls whether users can see the values in individual cells—however, it is rarely used because it is hard to set up and can have a big impact on query performance. In some cases it may be possible to replace cell security with dimension security by remodelling your cube.

Dynamic security is the ability of a single role to return different permissions for different users. This is possible by using an MDX expression within the role that uses either the Username or Customdata functions.

Make sure to watch the remaining modules in this course to learn how to build cubes using SSAS.

• 17 July 2015
• 2 comments
• 1171 views

Following on from the module on designing dimensions, Chris Webb teaches how to build cubes, measures, and measure groups, in this 1-hour 10-minute, in-depth, hands-on tutorial. Building SQL Server Analysis Services cubes using the New Cube wizard is straightforward: you choose the tables you want to use as the basis of your measure groups, you choose the columns you want to use as your measures, you choose which of your existing dimensions you want to use, and you’re done!

The first important question you have to ask when designing cubes is should you have one big cube or lots of small cubes. The one big cube approach should probably be your default choice because that’s the only option that allows you create calculations and queries that span all of your data. That said, there are some scenarios where it makes sense to create many smaller cubes.

Once you have created your cube, you will need to set various properties—especially on your measures. For example, you will want to set the FormatString property on your measures and organise them into Display Folders. You may also want to change how a measure aggregates up by changing the AggregateFunction property. Most measures are Sums, but you can also create measures that calculate Distinct Counts or perform semi-additive aggregation such as finding the last non-empty value in the current time period.

Setting up the relationships between your measure groups and dimensions is also important. Most dimensions have regular relationships with measure groups or no relationship at all. Fact relationships are for dimensions that are created from the same fact table that has been used to create the measure group. Referenced relationships allow you to join a dimension to a measure group through another measure group; they can either be materialized or non-materialized. Finally, many-to-many relationships allow you to model scenarios such as a bank account being associated with multiple customers, and a customer being associated with multiple customers. Many-to-many relationships are extremely powerful but they do carry a penalty in terms of query performance. Measure expressions can be used with many-to-many relationships to perform simple multiplication or division calculations (such as those needed for currency conversion) before measure aggregation takes place.

Other features you can add to your cube include creating Actions, which can allow your users to click on data from your cube and do things like open a web page or drill down to low-level data. Perspectives allow you to make a single large, complex cube look like multiple smaller, simpler cubes. Translations allow you to display the names of dimensions, hierarchies and members in different languages.

• 30 April 2015
• 9 comments
• 1736 views

Let Chris Webb explain how to build dimensions: one of the foundations of multidimensional cube design, in this 1-hour video. A SQL Server Analysis Services (SSAS) dimension is made up of one or more attributes. An attribute is a real-world entity that can be used to aggregate data. For example, on a Date dimension you might have attributes like: year, quarter, month and date.

When you build a dimension with the New Dimension wizard, the first important thing you have to do is to choose the main table in the data warehouse to build it from. You will also have to define the Key Attribute: the attribute that represents the lowest level of detail on the dimension. Once you have done that, on the next step of the wizard, you can define other attributes on the dimension. Finally, you can enter the name for the dimension. When the wizard finishes the Dimension Editor will open. It is important to use human readable, business friendly names for your attributes and dimensions!

In the Dimension Editor you can set various properties of the dimension and its attributes, such as the KeyColumns and NameColumn properties, AttributeHierarchyEnabled, AttributeHierarchyOptimizedState, DefaultMember and IsAggregatable. The order of members on an attribute can also be controlled. You can process a dimension by clicking on the Process button in the Dimension Editor, and once you have done that you’ll be able to browse the dimension on the Browse tab.

All attributes, by default, become visible to end users as hierarchies. You can also create user hierarchies by combining attribute hierarchies into a kind of a multi-level drill path. Attribute hierarchies and user hierarchies have different strengths and weaknesses, and you need to understand what they are to make sure your dimension is as easy-to-use as possible.

Another important consideration when designing dimensions are attribute relationships. Attribute relationships describe the one-to-many relationships that exist between attributes in a dimension, and optimising your attribute relationships can be extremely important for query performance.

Parent/child hierarchies are a way of handling variable-depth hierarchies. They are very flexible but they should be used with care because they have a number of disadvantages, especially for performance. A regular user hierarchy can be made to look like a parent/child hierarchy by using the HideMemberIf property. BIDS Helper can be used to convert a parent/child hierarchy to a ragged user hierarchy, and this can avoid some of the problems associated with parent/child hierarchies.

Make sure to watch the remaining videos in this series, and check-out our MDX online course, too.

• 30 March 2015
• 3 comments
• 2075 views

Carmel Gunn and Bob Duffy explore the Irish Economic Crisis using Power Query, Power Map, Power View and Q&A in this 40-min video. Could Power BI prevent a small country run a shocking €200bn debt?

• 24 March 2015
• 1 comment
• 2565 views

Let Chris Webb, book author and a world-renowned expert on SQL Server Analysis Services (SSAS) introduce you to the world of practical cube design with this short, 5-minute video, which demonstrates how to use the SSAS Deployment Wizard. Log-in or get a free account to watch it!

The easiest way to deploy your work to the server is to use the Deploy option in Visual Studio that comes with SQL Server Data Tools BI. However this method has a number of drawbacks when you want to make a deployment into a production environment. For example, your production database may contain partitions or roles that are not in your project, and connection strings and other configuration properties may be different too. Using the SSAS Deployment Wizard is a much safer option because it allows you to only deploy the parts of your project that you want to deploy, as well as giving you the option to create a deployment script and create a fully automated deployment process—see Chris demo this in the video.

If you would like to learn more about design, building, and the deployment of multidimensional cubes, make sure to watch the remaining videos in this series, starting with Chris’s Introduction to SSDT-BI, then follow the in-depth modules on MDX.

• 26 February 2015
• 2 comments
• 2460 views

SQL Server Data Tools-BI (SSDT-BI, previously called BI Development Studio or BIDS) is the name for the Visual Studio templates used for building SQL Server Analysis Services projects, which are fully introduced in this 40-minute video by Chris Webb, expert on all things SSAS.

SSDT-BI should not be confused with SSDT, which is the Visual Studio experience for building SQL Server relational databases. SSDT-BI is a separate download, available free of charge from Microsoft.

There are two ways of working with SSAS in SSDT-BI: Online mode, where every time you save your project the changes are saved back to the deployed SSAS database, and Project mode where, when you save, those changes are only saved to the project and you have to Deploy your changes to the server separately. Project mode is the safest option, mainly because it allows you to use source control.

There are a number of useful properties you should set on a project when you first create it (07:00), such as Deployment Server Edition, the Target Server and Database, and Processing Option.

Once you have created a new project, the first thing you need to do is to create a new Data Source to connect to a relational database. Doing this involves two things: giving SSAS all of the details it needs to generate a connection string, and telling SSAS which credentials to use when it connects back to the relational database. Next, you need to create a Data Source View. A Data Source View (DSV) allows you to choose which tables you want to work with in your SSAS project, and also to tell SSAS about any relationships between those tables. You can also create Calculated Columns (similar to derived columns) and Named Queries (similar to views) in your DSV, although you would be better off doing this work in the relational database itself if that is possible for you.

Finally, on any new SSAS project you should also be using BIDS Helper, a free add-in for Visual Studio that adds a lot of useful functionality for Microsoft BI developers, available from https://bidshelper.codeplex.com/

If you are planning to become a cube expert, make sure to watch the remaining videos in our cube design and MDX courses.

• 26 February 2015
• 4 comments
• 1979 views

For most people, the easiest tool for opening and navigating a multidimensional (OLAP) cube is Microsoft Excel. In this short demo you can also see how to perform basic slice-and-dice analysis, drill into and filter using dimensions, and summarise trends with a sparkline.

• 28 December 2011
• 7815 views