Automate Backup of Analysis Services Database Using SQL Server Agent Job Read more: Automate Backup of Analysis Services Database Using SQL Server Agent Job - MyTechMantra.com http://www.mytechmantra.com/LearnSQLServer/Automate_Backup_of_Analysis_Services_Database.html#ixzz1RBEEQ6ps

Backup Analysis Services Database Using SQL Server Agent Job

1. Start SQL Server Management Studio and connect to Database Engine.
2. Expand SQL Server Agent, right click Jobs and choose New Job… from the popup menu as shown in the snippet below.
3. In the New Job dialog box, provide a name for the job and then click on Steps page to create a new step.
4. In New Job Step, specify an appropriate step name and choose SQL Server Analysis Services Command as the type from the drop down list. Next you need to mention the Analysis Server Name and copy paste the below mentioned XMLA script as shown in the snippet below.

XMLA Script to Backup the Analysis Services Database

<--Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <--Object>
    <--DatabaseID>Adventure Works DW 2008 SEDatabaseID>
  <--/Object>
  <--File>D:\Program Files\Microsoft SQL Server\MSAS10.SQL2008\OLAP\Backup\Adventure Works DW 2008 SE.abfFile>
  <--AllowOverwrite>trueAllowOverwrite>
<--/Backup>

Microsoft SQL Server Analysis Services TCP port 2005 issues

Default instance of Analysis Services uses TCP port 2383. Named SSAS instances use dynamic ports. You can specify port that you want SSAS to listen in Analysis Services property "Port". If property "Port" is not specified (0), then port is picked dynamically. You can check dynamically assigned port in msmdredir.ini file located in %Program files%\Microsoft SQL Server\90\Shared\ASConfig. Find tag and tag for instance you are interested in. Example

<  Instances>
 <  Instance>
  <  Name>TestInstance1<  /Name>
  <  Port>12345<  /Port>
 <  /Instance>
<  /Instances>

If you are using SQL Browser service, it uses port 2382.

Maximizing Attribute Relationship Aggregations in Changing Dimensions

When designing your SQL Server 2005 Analysis Services (SSAS) dimensions and related attributes—appropriately called attribute relationships—you should consider how changes in your dimension data will affect the underlying dimension aggregations and, in turn, processing and query performance. If you aren’t careful in defining attribute relationships, you’ll likely run into dimension processing problems such as processing errors—and seemingly at the most inconvenient time. In this article, I explore the attribute relationship property called RelationshipType, which defines whether SSAS persists aggregations when it processes the dimension. If changes such as inserts or updates are occurring in your dimension tables, read on because you’ll want to optimize this property to maximize how your cube stores aggregations. I also walk through different types of dimension changes and their effect on the RelationshipType property so that you can optimize this setting whenever possible.

Performance Potholes

I remember my first taste of dimension processing with changing dimension data. That was in SQL Server 7.0, in the early days of SSAS, which was called OLAP Server back then. I had created a dimension based on a table whose columns experienced daily updates. When I processed the dimension, I received a “duplicate dimension” processing error. I thought, “What? I don’t have duplicate dimension records in my table!” But the problem wasn’t my dimension table; it was that my dimension in OLAP Server wasn’t marked as a changing dimension. When OLAP Server processed the dimension, all existing rows that had changed looked like new records to the dimension. And the server saw a new row with the same surrogate key as an existing row—a “duplicate.”
I set the dimension as a changing dimension, and the error disappeared, but so did some supporting aggregations and, in turn, performance. With changing dimensions in pre-SSAS 2005 versions, whenever you processed the dimension, all cube aggregations would be dropped and then slowly built again—a process called lazy aggregations.
In SSAS 2005, however, the changing-dimension concept is now part of the AttributeRelationship object’s RelationshipType property. This design has two performance impacts. First, when SSAS drops the aggregations and until it rebuilds them, queries suffer because the SSAS engine must calculate the query results by computing the values from either the underlying data or other aggregations. Second, when lazy aggregations are running, generating and storing the aggregations requires server resources, adding to the overall server load.
You can set the RelationshipType property to Flexible or Rigid, depending on whether the data from the associated attribute changes in relationship to the attribute relationship. The default setting is Flexible. If all the RelationshipType settings in an aggregation are set to Rigid, SSAS can persist the aggregation even when it processes the cube. Let’s look at how to determine the correct setting for the RelationshipType property, with the goal of using the Rigid setting as much as possible, which isn’t always easy.

Defining Attribute Relationships

Figure 1 shows the Product dimension in the AdventureWorks cube, with the Model Name and Subcategory attribute relationship highlighted. Also highlighted is the RelationshipType property, currently set to Flexible.

Figure 1: Product dimension in the AdventureWorks cube, with the Model Name and Subcategory Attribute Relationship highlighted.
If you follow SSAS optimization best practices (such as the ones in the white paper “Microsoft SQL Server 2005 Analysis Services Performance Guide,” which you can download at http://www.microsoft.com/technet/prodtechnol/sql/2005/ssas2005perfguide.mspx) chances are you’re familiar with two basic optimization techniques for dimensions:

• Create attribute relationships above the key attribute that follow the natural hierarchies of your dimensions.
• Set the RelationshipType property of the attribute relationships to Rigid when the data doesn’t change between related attributes.

The confusion comes when you combine these two techniques and your extraction, transformation, and loading (ETL) process modifies your dimension table by adding new records, adding Type 2 historical dimension changes (which track the history of changes for an attribute), and performing Type 1 changing updates when some attribute values are overwritten.
So here’s the big question: When should you set the RelationshipType property to Flexible versus Rigid in your dimensions when you have dimension changes occurring and natural hierarchies built? The answer: It depends on what kind of changes are happening and where!
For example, if you’re tracking Type 2 historical changes of an attribute such as Model Name that’s tied to the key attribute, then you change the RelationshipType property to Rigid. But if your Type 2 historical change is happening to an attribute such as SubCategory that’s not directly related to the key attribute but is related instead to a higher level attribute, then the RelationshipType property needs to remain Flexible so that the change doesn’t cause an error.
Thus, the basis for determining whether to set the RelationshipType property to Rigid or Flexible is whether the keys of the related attributes change relative to one another. To determine a change, look at whether the “key” relationships (that is, the attribute key values) change—not whether there are changes to just the name property as attributes related to one another. Confused? Stay tuned.

Type 2 Historical Changes

Let’s look at an example for Type 2 historical changes. The Model Name attribute uses the Model Name column for both the key and the name property. In addition, this attribute is tied directly to the key attribute of the dimension, which is the surrogate key. Let’s assume that Model Name is classified as a Type 2 historical attribute and that Model Name can change for a record. Because a Model Name change is a Type 2 change, a new record will be created when the Model Name changes for a record.
In the following sample dimension table, a new Model Name is assigned to an existing product, which creates a new record for that product.

Product Key	Product Name	Product ID	Model Name (the attribute’s key)
210	HL Road Frame – Black, 58	FR-R92B-58	HL Road Frame
607	HL Road Frame – Black, 58	FR-R92B-58	HL Frame

Notice that a new surrogate key is also created for the new Model Name, and the old surrogate key is still tied to the old Model Name. However, because the Model Name didn’t change in relationship to the surrogate key (the key of the key attribute), the relationship isn’t changing, and you can set the RelationshipType property to Rigid for the Model Name attribute relationship. The principle to follow here is that you can set the RelationshipType property of Type 2 historical attribute relationships to Rigid if the attribute relates directly to the key attribute.
Now let’s look at an example of Subcategory tracked as a Type 2 historical attribute in relationship to Model Name. In Figure 1, Subcategory is defined as an attribute relationship of Model Name. If the Subcategory changes for a product, a new record will be generated for the product, as the following table shows.

Product Key	Product Name	Model Name (the attribute’s key)	Subcategory Key	Subcategory
210	HL Road Frame – Black, 58	HL Road Frame	14	Road Frames
608	HL Road Frame – Black, 58	HL Road Frame	17	Touring Frames

If you define the RelationshipType property of the Subcategory attribute relationship as Rigid, your processing will fail with the error “Rigid relationships between attributes cannot be changed during incremental processing of a dimension,” as Figure 2 shows.

Figure 2: Processing errors when the RelationshipType property is set incorrectly
Why the error? Because the Model Name attribute in relationship to the Subcategory attribute changed. The new record had the same Model Name, but the Subcategory ID was different. Although this was a new record, the Model Names were the same for both records, and the Subcategory was related to the Model Name as an attribute relationship, so the different Subcategory ID signified a change. The principle in this case is that you need to set the RelationshipType property to Flexible for attribute relationships that don’t relate directly to the key attribute but that undergo a Type 2 historical change.

Type 1 Changes

Type 1 changing attributes are much easier to understand than Type 2 changes. If you have an attribute whose key column is updated by your ETL process as a Type 1 update, you need to set its RelationshipType property to Flexible for all attribute relationships that it participates in at any level.
The only exception to this rule is when you’re updating only the column used as the name property of the attribute and not the key column. In other words, the key of the attribute doesn’t change, only the name changes in relationship to the related attribute. An example of this situation would be if the name of a Subcategory changes but not its key. In the following table, for instance, the Subcategory changes from Road Frames to Road Bike Frames, but the Subcategory Key remains 14 for the new record.
Original Record:

Model Name	Subcategory Key	Subcategory
HL Road Frame	14	Road Frames

New Record:

Model Name	Subcategory Key	Subcategory
HL Road Frame	14	Road Bike Frames

As long as the Subcategory Key doesn’t change in relationship to the Model Name, you can define this relationship as Rigid. The principle here is that for Type 1 changing attributes where only the name column changes, you can set the RelationshipType property to Rigid—as long as the key column for the attribute doesn’t change in relationship to the related attribute.

When a Complete Hierarchy Moves

One final note of interest: You can have a lower level attribute that changes and still have a higher level attribute in the same attribute relationship path that’s defined as Rigid, provided the relationships between the higher level relationships don’t change. For example, if Category and Subcategory never change in relationship to each other, even though a Model Name can be assigned to different Subcategories, you can still define the RelationshipType property for Category and Subcategory as Rigid. Consider the following table example, showing original and new records, with the changing record highlighted.
Original Records:

Model Name	Subcategory Key	Subcategory	Category Key	Category
ML Bike Frames	14	Road Frames	3	Accessories
HL Road Frame	14	Road Frames	3	Accessories
HL Touring Frame	17	Touring Frames	2	Components

New Records:

Model Name	Subcategory Key	Subcategory	Category Key	Category
ML Bike Frames	14	Road Frames	3	Accessories
HL Road Frame	17	Touring Frames	2	Components
HL Touring Frame	17	Touring Frames	2	Components

The Subcategory name for the Model Name HL Road Frame changed from Road Frames to Touring Frames, but the new Subcategory name didn’t change in relationship to the Category of existing Touring Frame records. It might look like the Category changed from Accessories to Components, but Category remained the same for the Touring Frames Subcategory. Therefore, you can set the RelationshipType property to Rigid for the Category attribute relationship related to Subcategory. However, the Subcategory and Model Name relationship changes, so you would need to set the RelationshipType property to Flexible for the Subcategory attribute relationship related to Model Name. In this situation, you can set the RelationshipType property to Rigid for higher level attribute relationships in a natural hierarchy as long as the attributes (their key columns) do not change relative to one another, even if lower levels in the natural hierarchy can change and are set to Flexible.

Putting It All Together

The result of optimizing the RelationshipType property is that more aggregations will persist even when a dimension is processed, leading to better overall query performance and processing. If you’re lost in the complexities of the changes happening in your dimension data and in how to navigate the RelationshipType setting, you can always try setting all the RelationshipType properties to Rigid, and then change the ones that cause an error to Flexible. But be sure you test all possible changes, or you’ll find yourself dealing with more errors than you bargained for. Better yet, keep this article and its highlighted principles (at right) handy for when you design your dimensions. Either way, you’ll gain the benefits of a cube that’s tuned for performance.

Primary Principles for Setting the RelationshipType Property

• The basis for determining whether to set the RelationshipType property to Rigid or Flexible is whether the keys of the related attributes change relative to one another. To determine a change, look at whether the “key” relationships (that is, the attribute key values) change—not whether there are changes to just the name property as attributes related to one another.
• Set the RelationshipType property of a Type 2 historical attribute relationship to Rigid if the attribute relates directly to the key attribute.
• Set the RelationshipType property to Flexible for attribute relationships that don’t relate directly to the key attribute but that undergo a Type 2 historical change.
• If you have an attribute whose key column is updated by your ETL process as a Type 1 update, set its RelationshipType property to Flexible for all attribute relationships that it participates in at any level.
• For Type 1 changing attributes where only the name column changes, set the RelationshipType property to Rigid, as long as the key column for the attribute doesn’t change in relationship to the related attribute.
• Set the RelationshipType property to Rigid for higher level attribute relationships in a natural hierarchy as long as the attributes (their key columns) do not change relative to one another, even if lower levels in the natural hierarchy can change and are set to Flexible.

How to user SSIS to process SSAS Cubes Automatically

To Create SSIS And Schedule It

1. Create a New Integration Service Project. In the control flow tab, add a connection manager for analysis Services database and refer the instance of analysis service database in which cube exist.
2. From the Control Flow tool bar tab, find the ‘Analysis Services Processing Task’ and drag and drop to the control flow panel.
3. Edit this task and add the ‘cubes’ which we want to process in the object list option
4. Build the project and In the file menu, select ‘Save…..as’ and save the package to Analysis Services Database Server
5. Connect the Database Engine using Management Studio
6. Go to SQL Server Agent and Right Click on the Jobs. Add a new Job and set the Job Type as ‘integration Service’.
7. And Select the locally saved SSIS Package and set the period option as per your requirement
8. That’s it. Now the cubes will be processed at the scheduled intervals.

Working with named calculations

http://searchsqlserver.techtarget.com/feature/Working-with-named-calculations

Named calculations are additional virtual columns on the tables in your DSV. This allows you to mine derived information in your data without having to change your source data. Anamed calculation consists of name, a SQL expression containing the calculation, and an optional description. The calculation can be any valid SQL expression. If you are not a SQL expert, here are some types of expressions that are useful in many data mining projects.

Figure 3.5 Completed MovieClick data source view
Arithmetic Operations
Standard SQL supports +, -, * , /, and % (modulo). For example you could create a Profit named calculation with the following formula:
[Sales Price] – [Item Cost]
Mathematical Functions
Mathematical functions are useful, especially when data in a column does not follow a uniform distribution. The SQL functions ABS, LOG, SIGN, and SQRT are particularly useful. Here are two examples:

To flatten out an exponentially increasing variable:
LOG([Sales Quantity])

To create a flag for over/under budget quarters:
SIGN([Actual Expenses] – [Budgeted Expenses]) Compositing Expressions
Often, the hypothesis you want to test depends on a variable that is a combination of two of the variables you already have. For example, it may not be interesting that a person is married or has children, but the combination of the two may provide valuable information. A composite expression for this situation could look like this:
[Marital Status] + ' ' + [Has Children]
CASE Expressions
CASE expressions are an extremely flexible way to create meaningful variables for data mining. The CASE expression allows you to assign results based on the evaluation of one or more conditions. Useful applications of CASE are to change value labels, manually discretize columns, reduce the number of valid states, and convert an attribute from a nested table to the case level.

To change value labels:

CASE [Category]
               WHEN 1 THEN 'Food'
               WHEN 2 THEN 'Beverage'
               WHEN 3 THEN 'Goods'
     END CASE

Manually discretize a column:

CASE
             WHEN [Age] < 20 THEN 'Under 20'
             WHEN [Age] <= 30 THEN 'Between 20 and 30'
             WHEN [Age] <= 40 THEN 'Between 30 and 40'
             ELSE 'Over 40'
     END

To reduce the number of valid states:

CASE [Marital Status]
             WHEN 'Married' THEN [Marital Status]
             WHEN 'Never Married' THEN [Marital Status]
             ELSE 'Other'
     END

To convert an attribute from a nested table to a case table:

CASE
           WHEN EXISTS
                  (SELECT [Movie] FROM [Movies]
                         WHERE [Movie]='Star Wars' AND
                                            [Movies].[CustomerID]=[Customers].[CustomerID])
                         THEN 'True'
             ELSE 'False'
     END

This would be done, for instance, when you wanted to convert a nested attribute to a case-level attribute. Note that if you still want to use the nested table in the model, you will have to use a named query to filter the attribute from the nested table, as described in the next section.
Creating a Named Calculation on the Customers Table
To create a named calculation to discretize and reduce the number of states in the Num Bedrooms column:
1. Right-click the Customers table and select Create a named calculation.
2. Enter the calculation name Bedrooms and optionally enter a description.
3. Enter the following expression:

CASE
              WHEN [Num Bedrooms] = 1 THEN 'One'
              WHEN [Num Bedrooms] <= 3 THEN 'Two or Three'
              WHEN [Num Bedrooms] >= 4 THEN 'Four or more'
             ELSE 'None'
      END

Upon closing the dialog box, the DSV Designer will validate your expression and return any applicable errors. Once you have successfully created your calculation you can see the results by right-clicking the table and selecting Explore Data.

Capture an Analysis Services trace and log it into a SQL Server table

http://www.codeplex.com/wikipage?ProjectName=MSFTASProdSamples&title=SS2008!Readme%20for%20ASTrace%20Utility%20Sample&referringTitle=Home

This sample works only with SQL Server 2005 and SQL Server 2008. It will not work with any version of SQL Server earlier than SQL Server 2005.
The ASTrace utility gives you the ability to capture an Analysis Services trace and log it into a SQL Server table. The table can then be queried or read using SQL Server Profiler. The ASTrace utility runs as a Windows service that connects to Analysis Services, then creates a trace, and logs trace events into a SQL Server table using the SQL Server Profiler format. The ASTrace utility creates the trace by using a standard trace template that you can author using SQL Server Profiler.

Scenario

Analysis Services does not have a built-in tool to extract traces and save them to a file or table. This ASTrace utility sample demonstrates how to implement that trace functionality.

Optimizing Time-Based Calculations in SSAS

SQL Server Analysis Services (SSAS) provides built-in MDX functions to facilitate time-based calculations. For example, these functions are often used for period-to-period and same-period-last-year comparisons. (If you're unfamiliar with these MDX functions and calculations, see the web-exclusive article "The Concepts Behind Time Calculations in SSAS". Although approaches to leverage the MDX functions exist, they have shortcomings. I developed an approach that overcomes the shortcomings and strives to provide end users with calculations that are flexible, simple to use, and offer good performance.
I'll begin by describing commonly used approaches to time-based calculations (which involve a time utility dimension) and the shortcomings of those approaches. I'll then walk you through the approach I developed. Although I use SQL Server 2008 in the examples, my approach also works with SQL Server 2005.
Commonly Used Approaches That Involve Time Utility Dimensions
As I discuss in my web-exclusive article "The Concepts Behind Time Calculations in SSAS," adding distinct time-based calculations for each measure (and applicable time-based dimension attribute/hierarchy) can result in bloated, unwieldy cubes. A time utility dimension can be used instead.
In 2002, I came across the concept of a time analysis utility dimension (now typically referred to as time utility dimension) while reading the first edition of George Spofford's MDX Solutions (John Wiley & Sons, 2001). In Spofford's approach, you manually add a new single-member dimension (with a static value such as [Current]), then add a calculated member to the dimension for each type of desired analysis (e.g., period-to-period, same-period-last-year, year-to-date). The result is a flexible cube that has a small set of calculated member definitions.
For example, look at the pivot table in Figure 1.

Figure 1: Pivot table from a cube in which a time utility dimension was manually added (click to enlarge)

This pivot table is tracking two economic indicators: unemployment and consumer price index (CPI). A Time dimension hierarchy is displayed along the rows of the pivot table, and a Time utility dimension is displayed along the columns. The two columns underneath the Current member display the actual measure values for a given date, while the PriorPeriod and YearAgo columns represent calculated members.
The clever part of this approach is that the Current, PriorPeriod, and YearAgo members are generic (i.e., not explicitly defined against any particular measure) and defined only once. Measure values can be switched in and out of this pivot table. When you do so, the Current, PriorPeriod, and YearAgo values will update accordingly.
Let's walk through an implementation of a time utility dimension built directly into SSAS: the Business Intelligence Wizard's "time intelligence" option. (The Business Intelligence Wizard was first introduced in SSAS 2005.)
If you'd like to follow along, you can download the files in Chessman_TimeAnalysis_CodeSamples.zip by clicking the 103355.zip hotlink near the top of the page. The underlying data, which comes from the Organisation for Economic Co-operation and Development, compares economic trends across different countries.
Start by restoring the SQL Server 2008 relational database from the DebtAnalysis.bak backup file. Next, open the OLAP_GovtData.dwproj project in Business Intelligence Development Studio (BIDS). Edit the project's target server property to match the location of your SSAS instance. Finally, after editing the DebtAnalysis.ds data source to reflect the location of the DebtAnalysis relational database, process the entire database using the Database menu's Process option.
With the OLAP_GovtData project open in BIDS, make a copy of the GovtDebtAnalysis cube. (Alternatively, you can see the output in the GovtDebtAnalysis_BIW.cube file in Chessman_TimeAnalysis_CodeSamples.zip.) Open the copied GovtDebtAnalysis cube in the BIDS designer. On the Calculations tab, select the Add Business Intelligence option from the Cube menu to start the Business Intelligence Wizard. After navigating past the Welcome window, select Define time intelligence in the Choose Enhancement window, as Figure 2 shows.

Figure 2: Selecting the Define time intelligence option (click to enlarge)

Next, choose the [H-Year] hierarchy and select the following check boxes in the Available time calculations section:

• Year Over Year Growth
• Quarter Over Quarter Growth
• Month Over Month Growth

Finally, in the Define Scope of Calculations window, select the applicable measures to be included in the calculations. In my example, I selected all the measures in the wizard. (Note that in the cube's calculations script, I manually removed the measures for the Fact OECDGDP measure group from the quarterly and monthly growth calculations because these measures are defined at an annual grain—i.e., detail at the annual level.) Click Next to review the changes the wizard will implement, then click Finish to implement them. The major changes implemented by the Business Intelligence Wizard include the following:

• A new named calculation (H-Year DimDate Calculations 1) is added to the dsv_DebtAnalysis data source view with a static value of [Current DimDate]. Likewise, an attribute (also named H-Year DimDate Calculations) is added to the DimDate dimension.
• A calculated member is created for each time calculation you selected in the wizard. Each member is initially defined with a value of NA.
• Each calculated member is associated to specific measures and cube cells (specifically, along the attributes in the DimDate dimension) through assignments.

Figure 3 shows a pivot table from the resulting cube. The time utility dimension and unemployment rates are displayed in the columns. The H-Year hierarchy is displayed in the rows.

Figure 3: Pivot table from a cube in which a time utility dimension was added using BIDS (click to enlarge)

The Shortcomings
In versions earlier than SSAS 2005, time dimensions were relatively easy to work with. SSAS 2005 and its introduction of the Unified Dimensional Model (UDM) changed everything. Today, dimension attributes, multiple dimension hierarchies, and role-playing dimensions make it difficult to implement flexible and comprehensive time-based calculations.
Most of the time-based calculation examples I've encountered ignore these issues. Examples in which time utility dimensions are manually added are typically limited to a single dimension hierarchy. The built-in Business Intelligence Wizard doesn't effectively address these challenges either because it's limited to building calculations for either a single-attribute or single-dimension hierarchy. Furthermore, the Business Intelligence Wizard has relatively weak error-handling capabilities to account for missing data along a time dimension.
A New Approach
My approach to time-based calculations involves manually creating a time utility dimension. It's based on concepts from David Shroyer's white paper "A Different Approach to Implementing Time Calculations in SSAS", ideas from Mosha Pasumansky's blog "Time Calculations in UDM: Parallel Period", and the results of my own research. At a high level, I used Shroyer's framework for creating a time utility dimension. I integrated performance optimizations from Pasumansky's blog and used an explicit attribute-based method of scoping the calculations across the time dimension (and any role playing dimensions based on the time dimension).
To walk through this new approach with me, make a copy of the GovtDebtAnalysis cube in the OLAP_GovtData project. (Alternatively, the GovtDebtAnalysis_TUD.cube file in Chessman_TimeAnalysis_CodeSamples.zip contains a completed implementation.) Then follow these steps:

1. Add a new dimension attribute to the existing time dimension.
2. Create a new dimension.
3. Add the new dimension to the cube.
4. Add the time calculations to the cube.
5. Test the cube.

Step 1. Add a New Dimension Attribute
The first task in adding a new dimension attribute to the existing time dimension is to add a new named calculation to the DimDate table in the dsv_DebtAnalysis data source view. Name it TimeCalcs and assign it the static expression value of N'Current'. Next, add TimeCalcs as an attribute to the DimDate dimension, setting the AttributeHierarchyVisible property to False. This attribute isn't used in the DimDate dimension, but it's needed for configuring a referenced dimension relationship in Step 3.
Step 2. Create a New Dimension
Now you need to create a new shared dimension. Right-click Dimensions in Solution Explorer, and select the New Dimension menu item to start the Dimension Wizard. As you work through the Dimension Wizard, choose the Use an existing table option, select DimDate as the main table, and select the named calculation (i.e., TimeCalcs) as both the Key column and Name column. Name your dimension DimTimeCalcs.
The DimTimeCalcs dimension will be used for calculations, not aggregations. Therefore, we don't want an All level in the dimension. With your DimTimeCalcs dimension open in the editor, select the TimeCalcs attribute. Change the IsAggregatable property to False and change the DefaultMember to [Current], as Figure 4 shows. Save the changes and close the dimension designer.

Figure 4: Changing the IsAggregatable and DefaultMember properties (click to enlarge)

Step 3. Add the New Dimension to the Cube
The next step is to add the new DimTimeCalcs dimension to the cube. In Solution Explorer, double-click the cube to open it. Right-click anywhere on the Dimension Usage tab and select Add Cube Dimension. For each measure group, set the relationship type to Referenced, as seen in Figure 5.

Figure 5: Configuring the relationship type of each measure group (click to enlarge)

After you're done configuring the measure groups' relationship type, highlight the new dimension in the Dimensions Usage tab and set the HierarchyUniqueNameStyle property to ExcludeDimensionName. Because this dimension doesn't contain user-defined hierarchies, you can simplify any MDX reference to include just the attribute name.
Step 4. Add the Time Calculations to the Cube
You're now ready to add the time calculations to the cube. Select the Calculations tab and switch to the Script view. First, you need to add and define the new calculations. For example, the following code adds the PriorPeriod and YearAgo calculations to the [TimeCalcs] dimension attribute of the DimTimeCalcs dimension, and defines their default value as Null:

CREATE MEMBER CURRENTCUBE.
  [TimeCalcs].[PriorPeriod] AS Null;
CREATE MEMBER CURRENTCUBE.
  [TimeCalcs].[YearAgo] AS Null;

(Although these lines wrap here, you'd enter each CREATE MEMBER statement on one line.)
Next, you need to limit, or scope, the calculations to each desired dimension attribute in the DimDate dimension, as Listing 1 shows.

Listing 1: Code That Scopes the PriorPeriod Calculation

Notice that this code defines only a PriorPeriod calculation and not a YearAgo calculation for the Century dimension attribute. Including a YearAgo calculation doesn't make sense given that this dimension attribute's context is century. However, for many of the other attributes, it does make sense, so you need to define both the PriorPeriod and YearAgo calculations, as shown in Listing 2.

Listing 2: Code That Scopes the PriorPeriod and YearAgo Calculations

To scope the YearAgo calculation for the Calendar Qtr dimension attribute, you can simply use MDX's Lag function with an index of 4, as callout A in Listing 2 shows. I prefer using the Lag function rather than MDX's ParallelPeriod function because ParallelPeriod requires a hierarchy level as an input.
In case you're unfamiliar with the Lag function, it returns the member that's the specified number of positions before the specified member. In this case, the specified member is the current quarter ([DimDate].[Calendar Qtr].CurrentMember) and the index is 4, so the lag function returns the quarter that was four quarters ago (i.e., a year ago). Alternatively, you could use the Lag function with the Month dimension attribute in code such as

[DimDate].[Month].CurrentMember.Lag(12)

to return a year-ago value. However, you shouldn't use the Lag function with the Date dimension attribute to obtain a year-ago value. Code such as

[DimDate].3/17/2010 11:42:31 AM.CurrentMember.Lag(365)

typically doesn't work due to leap years. In this instance, you'd need to use the ParallelPeriod function. None of the measure groups in my cube have a daily grain, so I didn't use the ParallelPeriod function.
You can find all the code needed for step 4 in the MDX script in the GovtDebtAnalysis.cube file in Chessman_TimeAnalysis_CodeSamples.zip. After you've finished scoping the time calculations for each desired attribute, you need to process the cube.
Note: There's a shortcut you can take to scope all the attributes participating in a dimension hierarchy. I included an example in the comments of the MDX script in the GovtDebtAnalysis_TUD cube, and you can see the same approach in the MDX code generated by the Business Intelligence Wizard. I decided to define calculations one attribute at a time because, for large cubes, it can be difficult to keep track of which attributes do and do not participate in a hierarchy.
Also, for some types of time-based calculations (e.g., PeriodsToDate), you need to explicitly define and scope multiple calculations for an attribute participating in multiple hierarchies. For example, a PriorPeriod or YearAgo calculation should be the same regardless of hierarchy, but a year-to-date calculation will be different when querying a Calendar versus Fiscal hierarchy.
Step 5. Test the Cube
Before you let end users query the cube, you should test the cube by using the browser in BIDS to simulate an end user or by creating a connection to the cube from an application such as Microsoft Excel. If you use BIDS, you might encounter an "unknown error" on certain navigation paths because of a bug in the BIDS browser.
These errors don't show up in Excel. If you're using Excel, make sure you go into the PivotTable options and select Show calculated members from OLAP server on the Display tab.
Figure 6 contains a sample pivot table that shows historical unemployment rates in the United States.

Figure 6: Pivot table from a cube in which a time utility dimension was added using the new approach

The TimeCalcs dimension is displayed in the columns. The PriorPeriod and YearAgo calculations are properly scoped at yearly, quarterly, and monthly levels. If you have Excel 2007, you can open the pivot table spreadsheet (Excel_PivotTables.xlsx) and explore the various measures, date attributes, and date hierarchies.
Note that the GovtDebtAnalysis cube you just created doesn't contain role-playing dimensions. If you have time-based role-playing dimensions (e.g., OrderDate, ShipDate) in a cube, you can copy and paste all the scoped calculation definitions, then replace the dimension name with the role-playing dimension name. Because my approach uses a referenced time utility dimension rather than using the [TimeCalcs] attribute directly from the date dimension, the calculations will work across all the role-playing dimensions.
Benefits of the New Approach
So what can your end users gain from all this effort? Consider the original goal of providing end users with flexibility, simplicity, and good performance:

• Flexibility. Regardless of the measures, date attributes, or date hierarchies being queried, the [Current], [PriorPeriod], and [YearAgo] calculations are automatically computed and appropriately displayed. Other approaches to time utility dimensions don't do this.
• Simplicity. Only two calculated members were added to provide time-based analysis across the entire cube, avoiding the problem of calculated measure explosion inherit in traditional approaches. (For an example of calculated measure explosion, see "The Concepts Behind Time Calculations in SSAS".)
• Good performance. The MDX script contains no runtime-dynamic statements (e.g., CASE, IIF). Instead, the script uses static scoping to avoid potential performance issues. It also efficiently eliminates erroneous calculations at the start and tail end of the date dimension. For example, in Figure 6, notice that years without unemployment rates (1790-1989) don't appear in the pivot table and the PriorPeriod and YearAgo results for the first year containing data (1990) correctly show a null value.

Give It a Try
I encourage you to try this new time-calculation approach in your existing cubes and any new cubes you need to create. Your end users will be more productive and independent. Although the implementation involves a bit of up-front effort, it should reduce the amount of overall time spent on custom calculations.