About the Series ...
This article is a member of the series Introduction to MSSQL Server Analysis Services. The series is designed to provide hands-on application of the fundamentals of MS SQL Server Analysis Services (Analysis Services), with each installment progressively presenting features and techniques designed to meet specific real-world needs. For more information on the series, please see my initial article, Creating Our First Cube. For the software components, samples and tools needed to complete the hands-on portions of this article, see Usage-Based Optimization in Analysis Services 2005, another article within this series.
Among many new capabilities that debut in Analysis Services 2005, our options for bringing together data from different data sources and extending structures that we have already built elsewhere within our integrated business intelligence solution - is further enhanced with linked objects. We can link a cube to a measure group in another cube that we maintain in the same Analysis Services 2005 database, a cube on the same server, or a cube in any other Analysis Services instance. We can also add a link within a link local cube to a dimension that we have created within another database, in like manner. Once we establish the link, we enable information consumers to query data and metadata in the linked measure group or dimension, just as they can do in a respective object that is native to the cube to which they have access.
In this article we will gain some hands-on exposure to linked objects in Analysis Services 2005. Our examination of the linked objects will include:
- A discussion surrounding the general concepts underpinning linked dimensions and measures, including how they can be useful within our support of analysis and reporting within the enterprise.
- Addition of a related linked dimension and measure group pair within a sample cube to demonstrate the ease with which we can add these linked objects to the cubes in our individual business environments.
- A discussion of other considerations that surround the selection, addition and use of linked objects.
Linked Objects in Analysis Services 2005
In Microsoft SQL Server 2005 Analysis Services (Analysis Services 2005), a linked object is based upon a dimension or measure group that is stored in another Analysis Services 2005 database. The source database for a linked object can be on the same server or on a different server. By using a linked object, we can create, store, and maintain a dimension or measure group on one Analysis Services 2005 database, and still make that dimension or measure group available to users of multiple databases. To users, a linked object appears like any other dimension or measure group (depending upon which said object it is based, of course).
Potential benefits of using identical dimension and measure group structures across databases can be substantial. The most obvious benefit is the extension of a single structure, built and maintained in one location, to multiple Analysis Services databases. This can mean enforced consistency and synchronicity in the application of the dimensional structures involved, assuming that use of a linked dimension or measure group is otherwise appropriate for the environments involved.
Limitations, such as non-support of dimension writeback, might be a consideration in some environments. Moreover, we must keep in mind that related measure groups and dimensions, which must come from the same source database, must have the relationships between them maintained in the respective source database, once we have added these linked dimensions and measure groups to our local cube. Finally, we must keep in mind the basics for defining and configuring dimension usage and dimension relationships with measure groups, for the most part, just as we would for the respective objects that are native to our cubes, in order to avoid unexpected results.
Adding Linked Objects within a Cube
Lets consider an example of a use for linked objects, within the context of a pair of sample Analysis Services 2005 databases with cubes that are available to anyone who has installed Analysis Services 2005. We will work with a stripped down version of another sample database, which will serve to contain the primary cube, and to whose basic dimensions and measure groups we will add a linked dimension / measure group pair. We will target a clone of the more sophisticated Adventure Works DW database, which ships with Analysis Services 2005, as a source for a linked dimension and measure group that is absent in our more basic primary cube.
Analysis Services 2005 makes available the Linked Object Wizard, within the Business Intelligence Development Studio, to assist us in the addition of various objects from other Analysis Services 2005 data sources (some can be linked, others can be imported the latter we discuss in other articles of this series). The other database can exist on the same server as the cube into which we are adding the linked objects, or it can exist on a remote server, once again. As we have also noted, once we add the links to the targeted dimensions and measure groups, those dimensions and measure groups are available to cube users, via the links, just as if they were non-linked objects.
Before we get started with the Linked Object Wizard, we will need to prepare our local environment for the practice session. We will take steps to accomplish this within the section that follows.
Preparation: Create and Modify Sample Databases and Enable Creation of Linked Objects
Create Sample Primary Database for the Practice Exercise
Before getting started with our practice session, we will need a basic sample Analysis Services database (with cube) with which to work. To quickly create a copy of a basic Analysis Services sample project, from which an Analysis Services database can be quickly deployed, please perform the steps of the following procedure, located in the References section of my articles index:
Once you have created the new sample project, you should ascertain connectivity to the underlying relational data source (particularly if you are running side-by-side installations of MSSQL Server 2000 and MSSQL Server 2005, but it is important to check even if not). You can do this by performing the steps of the following procedure, also located in the References section of my articles index:
Once you have created the new sample project, and ascertained connectivity, deploy it to the Analysis Server by performing the steps of the following procedure, located in the References section of my articles index (substitute object names for your own when following the procedure):
Conduct Further Sample Database Creation within Microsoft SQL Server Management Studio
We are now ready to ascertain that the new Analysis Services database is in place, as well as to move into further preparation. To do this, we will work from within the Microsoft SQL Server Management Studio.
1. Start Microsoft SQL Server Management Studio.
2. Select Analysis Services in the Server type selector of the Connect to Server dialog that appears.
3. Type / select the server name (server name / instance, if appropriate) in the Server name selector.
4. Supply authentication information, as required in your own environment.
The Connect to Server dialog appears similar to that depicted in Illustration 1.
Illustration 1: Connecting to the Server ...
5. Click the Connect button to connect with the specified Analysis Server.
The Microsoft SQL Server Management Studio opens.
6. In the Object Explorer pane (it appears by default on the left side of the Studio), expand the Databases folder (click the + sign to its immediate left), appearing underneath the Analysis Server within which we are working.
The Databases folder opens, exposing the detected Analysis Services database(s). Our new Analysis Services database, DBJ_Basic AS DB, should appear among the other databases.
7. Right-click the Adventure Works DW database, and select Backup ... from the context menu that appears next, as shown in Illustration 2.
Illustration 2: Back Up the Sample Database
NOTE: The sample databases do not come preinstalled. If the Adventure Works DW database does not appear within the Object Explorer pane, you will need to install the constituent samples to continue with the steps that follow. Please see the documentation surrounding installation of MSSQL Server 2005 samples, which can be found on the installation CDs, to some extent within the Books Online, on www.microsoft .com, and elsewhere for installation instructions.
The Backup Database Adventure Works DW dialog appears.
8. Using the Browse ... button to the right of the input box labeled Backup file, navigate to a convenient place to place database backups.
9. Name the backup as follows:
10. Ensure that, within the Options section of the dialog, all three checkboxes are cleared.
The Backup Database Adventure Works DW dialog appears, with our input, as depicted in Illustration 3.
Illustration 3: Backup Database Adventure Works DW Dialog
11. Click OK to begin the backup process.
Processing begins, and, once it completes, the Backup Database Adventure Works DW dialog disappears. We will next restore the backup, to provide a clone Analysis Services database to use as our target source for a linked dimension and measure group combination.
12. Right-click the Databases folder for your Analysis Server, and select Restore ... from the context menu that appears next, as shown in Illustration 4.
Illustration 4: Restoring the Sample Database Backup
The Restore Database dialog appears.
13. Type the following into the Restore Database selector box:
14. Using the Browse ... button to the right of the input box labeled From backup file, navigate to the location of the backup we created above.
15. Select backup ANSYS061_AWDW_DimSource.abf from the tree displayed within the Locate Database Files dialog, as depicted in Illustration 5.
Illustration 5: Selecting the Backup to Restore ...
16. Click OK to accept the selection, and to dismiss the Locate Database Files dialog.
The Restore Database dialog appears, with our input (and all other settings at default), similar to that shown in Illustration 6.
Illustration 6: Restore Database Dialog
17. Click OK to begin the restoration process.
18. Once restoration completes, right-click the Databases folder for your Analysis Server, as before.
19. Select Refresh from the context menu that appears next, as depicted in Illustration 7.
Illustration 7: Refreshing the Analysis Services Databases ...
Our two new Analysis Services databases, DBJ_Basic AS DB and ANSYS061_AWDW_DimSource, appear in the tree of the Object Explorer. We now have two Analysis Services databases (one containing more dimensions and measure groups than the other), with which we can complete our practice session.
Enable Creation of Linked Objects, If Necessary, on the Analysis Server
By default, Analysis Services 2005 disallows the creation of linked dimensions and linked measure groups. In order to enable the linking of these objects both instances (if linking is occurring between two Analysis Services instances) need to enable this feature. We will ascertain if linked objects are already enabled within our own environment in the steps that follow. We can both observe and modify the values of the Feature\ LinkFromOtherInstanceEnabled, Feature\ LinkInsideInstanceEnabled, and the Feature\ LinkToOtherInstanceEnabled server configuration properties, if necessary, within SQL Server Management Studio.
1. Within the Object Explorer pane, right-click the Analysis Server, and select Properties from the context menu that appears next, as shown in Illustration 8.
Illustration 8: Select Properties from the Context Menu
Since we intend to add linked objects between Analysis Services databases within the same Analysis Server instance, for purposes of our immediate needs, we will enable only linking inside that instance. (We can always return and enable other options as our needs change.)
2. Locate the Feature\ LinkInsideInstanceEnabled property, and change the Value setting to true (if not already indicating true), with the dropdown selector, as depicted in Illustration 9.
Illustration 9: Enabling Linked Objects within the Same Analysis Server Instance
Our setting allows us to add linked objects within our basic cube (housed within the basic Analysis Services database we have created in earlier steps as a part of preparation, objects that will be targeted for linking from within the more sophisticated cube that belongs to the Adventure Works DW clone database, which we have also created as a part of preparation. The two Analysis Services databases lie within the same Analysis Server instance, which makes this single property setting ideal for our current needs.
Once we have made the setting change (assuming it was not already enabled), it appears among the other basic Analysis Server Properties.
3. Click OK to accept modifications, and to dismiss the Analysis Server Properties settings page.
Having ensured that linked objects are enabled within the Analysis Server instance within which we wish to establish them, we are ready to continue to the procedural part of our practice session.