Analysis Services Projects - SQL Server 2008

In order to create Analysis Services objects (cubes, dimensions, measures, and so on), a developer could code natively in ASSL, the XML-based language used by client applications to communicate with Analysis Services. However, writing raw XML (regardless of how well designed) is tedious and error-prone.

The BIDS integrated development environment (IDE) supports Analysis Services projects. This project type is ASSL under the covers (the source files of the project are ASSL XML), but the BIDS IDE streamlines development by generating ASSL while developers get to work with a sophisticated user interface.

Starting a New Analysis Services Project

The cube diagram in Figure included the dimensions of DimProduct, DimCurrency, and DimCustomer. This cube also contains a fact table, FactInternetSales. We can implement this cube using an Analysis Services project in BIDS, in order to show off BIDS capabilities in the Analysis Services arena. Let’s modify the cube in Figure to add a fourth dimension, DimTime.

To create a new Analysis Services project, open BIDS (Microsoft SQL Server 2008 ➤Business Intelligence Development Studio) and select File ➤New. You will see the New Project dialog box, as shown in Figure. Navigate it as follows: Other Languages ➤Business Intelligence Projects (this sequence is slightly different on a host with both SQL Server and Visual Studio 2008 installed on it). In the Templates pane, select Analysis Services Project.

Creating a new Analysis Services project

Creating a new Analysis Services project

By default, the name of the project and solution will contain a generic name such as Analysis Services Project1. To identify the project, enter Internet Sales in the Name text box. Then click OK to close the New Project dialog box.

The model used by BIDS is that of a solution containing one or more projects. For this scenario, there is one solution and one Analysis Services project. It would be possible within the same solution to create additional projects such as a Reporting Services project and an Integration Services project.

Defining Data Sources

Once you create an Analysis Services project, you must create a data source to do useful work. This data source refers to the database from which your project will access data. This data source may be associated with SQL Server or could be any flavor of data source accessible as a managed provider, OLE DB, or ODBC.

In order to create a data source, within Solution Explorer (View ➤Solution Explorer), rightclick the Data Sources folder within the Internet Sales project and select New Data Source from the context menu, as shown in Figure.

Choosing to create a new data source

Choosing to create a new data source

Selecting the New Data Source option starts the Data Source Wizard. Click Next to move past the welcome screen. The second screen of the Data Source Wizard, shown in Figure, enables you to create the data source.

Defining the data source

Defining the data source

A data source is associated with a data connection (data provider, host, database, security credentials, and so on). To create a data connection, thus enabling a data source to be created, click the New button. You will see the Connection Manager dialog box, as shown in Figure.

Creating a new data connection

Creating a new data connection

When the Connection Manager dialog box initially displays, it selects the Native OLE DBSQL Native Client by default. OLE DB is the data access technology that predates .NET and managed providers. In order to access SQL Server most efficiently, select the .Net ProvidersSqlClient Data Provider from the Provider combo box. Under Server name, a drop-down list will appear of all available hosts running SQL Server. You can enter localhost (or a period, for shorthand) to indicate that the machine on which BIDS is presently running contains the instance of SQL Server to which you wish to connect.

At this point, you should select the type of security required. You can choose either Windows authentication or SQL Server authentication. Microsoft advocates using Windows authentication. SQL Server authentication has one minor advantage over Windows authentication: developers do not need to submit a formal request with their information technology department to get their Windows accounts associated with specific SQL Server roles/permissions. However, the consistency and security of Windows authentication make this the preferred choice. The connection name displayed in the dialog box will take the following form if you create it using Windows authentication: Hostname.Databasename. If you create the connection using SQL Server authentication, the connection will be in the form Hostname.Databasename.Username.

One clue that your security is set up correctly is that when you select the database name from the drop-down list, it will display all accessible databases on the server for the specific user credentials. From the drop-down list, select the database AdventureWorksDW. As a check, click the Test Connection button in order to verify that the server, security credential, and database all combine to make a valid database connection. Once the connection validates, click OK to create a data connection.

After the data connection is created, click Next in the wizard. You’ll see the Impersonation Information screen, as shown in Figure. This is where you specify the credentials that are used by Analysis Services to connect to the specific data source.

Providing impersonation information

Providing impersonation information

The Impersonation Information screen is important because the Analysis Services server, and not the user currently working with BIDS, ultimately connects to the data source. It is best not to select the Use the Credentials of the Current User option. Under certain circumstances (such as when a data source is used for training data-mining structures/modules), it is not permissible according to the documentation to use this impersonation mode. For the purposes of this exercise, choose the Use the Service Account option. Selecting this option assumes that the account under which Analysis Services runs (the service account) has access to the data source.

There is no mandate that the data source point to a SQL Server 2008 database. The purpose of the Analysis Services UDM is to enable access to heterogeneous data sources in a manner seamless to the end user. For the sake of convenience, we use AdventureWorksDW, a SQL Server 2008 database, for this example. The data source could have easily been a SQL Server 2000 database such as Northwind or a Jet database named Pubs. The data source could have been DB2, Oracle, Excel, or any heterogeneous data source supported by a managed provider, OLE DB, or ODBC.

Editing a Data Source

After you’ve created a data source, you can double-click it in Solution Explorer to launch the Data Source Designer, shown in Figure. Using this designer, you can edit the data source.

Editing a data source with the Data Source Designer

Editing a data source with the Data Source Designer

Using the Data Source Designer, you can change the name of the data source, alter the connection string, or modify other properties associated with the data source. In fact, the designer has access to features not exposed by the wizard. The Edit button is useful if the underlying host, data, or security credentials change for the data source. Each top-level object within BIDS (DSVs, cubes, and dimensions) has a wizard and a designer associated with it.

Defining Data Source Views

A core premise of Analysis Services for many developers is that if they can get the metadata correct, then the data will fall into place for them. In keeping with this philosophy, the DSV is a very important requirement within an Analysis Services project. A DSV is an offline version of the metadata associated with tables or views used by the Analysis Services project. The idea is to enable development to proceed by enabling access to the required metadata without having to maintain a persistent connection to the data source.

To create a DSV, right-click the Data Source Views folder for the Analysis Services project within Solution Explorer and choose New Data Source View, as shown in Figure. This starts the Data Source View Wizard. Click Next to move past the welcome screen.

Choosing to create a new DSV

Choosing to create a new DSV

The second screen of the wizard allows you to select a data source. For this exercise, select the data source previously created, AdventureWorksDW. Then click the Next button to bring up the Select Tables and Views screen, as shown in Figure. Here, you select the tables and views for your DSV. For this example, double-click the DimProductCategory, DimProductSubCategory, DimProduct, DimCustomer, DimCurrency, DimTime, and FactInternetSales tables, to move them to the Included Objects list. Although not used in this example, the Add Related Tables feature can prove quite useful. Clicking this button moves tables from the Available objects list that have a foreign key relationship with the tables and views of the Included Objects list.

Selecting tables and views for a DSV

Selecting tables and views for a DSV

Once you have selected the tables for the DSV, click Next. When the final screen of the wizard appears, change the default name of the Data Source View from Adventure Works DW to Adventure Works Internet Sales, and then click Finish to create the DSV. The purpose of the view is to isolate a pertinent subset of the information. In our case, this was a set of tables from the AdventureWorksDW database related to sales generated via the Internet.

A data diagram appears in the design view associated with the newly created DSV, as shown in Figure.

Data Source View Designer

Data Source View Designer

In the Data Source View Designer, you can select a table. You can change the properties of a table (such as FactInternetSales) in the Properties window. For example, you might associate a friendly name with an otherwise cryptically named table/view. It is also possible to drag one table/view on top of another to create a relationship between these entities.

Right-clicking the design window displays a context menu that enables additional manipulation of the DSV:

  • Add or remove tables and views: As an example, if we had forgotten the DimProduct table when creating the new DSV, a significant piece of the model would be missing. You could correct this oversight using the context menu’s Add/Remove Tables option.
  • Specify a named query for the data source: Recall that a named query is a query associate with a specific data access command (a SELECT in SQL terminology). This named query would show up in the designer as a table. A named query does not reside in the underlying data source and therefore does not require CREATE VIEW permission on the data source. It also could be the case that the underlying data source is not a relational database, and therefore does not even support the concept of a view. The named query resides in the local metadata associated with the DSV.
  • Create new relationships: This might prove useful if the foreign key relationships do not exist in the underlying data source, or if the underlying data source does not provide access to its metadata, preventing the BIDS wizard from automatically setting up the table relations. This latter scenario is quite common where a user has access to data but not to metadata definitions for constructs such as indexes, foreign keys, and view definitions. Creating a new relationship is a necessity when using a named query. A named query has no underlying metadata defining its relationship to the other tables in the data source. Creating a new relationship provides this missing critical information.
  • Toggle between design and code view: Code for any object in an Analysis Services project object is just the XML representation of the object. This XML is the ASSL, as discussed earlier in the chapter. The code view is handy for globally changing names within an Analysis Services project object. For example, if a developer mistakenly used Sails as the friendly name for each sales-related object, a global replace operation in the code can easily remedy the mistake. For developers who need to work directly with ASSL, the various source files of an Analysis Services project can serve as a tutorial.

Defining Cubes

There are many changes to the Cube Wizard in SQL Server 2008, but it is still remarkably easy to use. Creating a new cube is a matter of right-clicking the project’s Cubes folder within Solution Explorer and selecting New Cube from the context menu. Cubes are highly configurable, but the new user will typically just choose the default on the Select Creation Method screen, Use Existing Tables, as shown in Figure.

Selecting a cube creation method

Selecting a cube creation method

Clicking Next takes you to the Select Measure Group Tables screen, as shown in Figure. Here, you can select several fact tables or just one. In this example, select the FactInternetSales table, which contains the potential measures (OrderQuantity, UnitPrice, SalesAmount, TaxAmount, Freight, and TotalProductCost). A fact table contains one or more measures.

Selecting measure group tables for a cube

Selecting measure group tables for a cube

After specifying the fact tables, click Next. You’ll see the screen used to select the measures associated with a cube, as shown in Figure. For this example, select Order Quantity, Sales Amount, Tax Amt, and Freight. (The Sales Amount column is the total cost of the goods sold, taking into account quantity, price, and discount.) All other columns should be unchecked. Click Next to continue.

Selecting measures for a cube

Selecting measures for a cube

The next screen enables you to select your dimensions, as shown in Figure. Here, we have selected the DimCurrency, DimCustomer, and DimTime tables to serve as dimensions. These do not contain a hierarchy, as their dimension contains just one table. The fourth dimension, which is hierarchical in nature, is composed of DimProductSubcategory, DimProductCategory, and DimProduct tables. The CubeWizard discovered this hierarchy for us. Also, note that we unchecked the FactInternetSales table, as we do not want that to play any dimensional role, although fact tables sometimes can serve as a dimension in special circumstances.

Selecting your dimensions

Selecting your dimensions

To complete the Cube Wizard, click Next, and then click Finish. The cube picks up the name of the DSV by default, so this cube is Adventure Works Internet Sales.cube, as can be seen in Solution Explorer, as shown in Figure.

Solution Explorer with the cube and dimensions

Solution Explorer with the cube and dimensions

Configuring Dimensions

Now that you have completed the Cube Wizard, it has created dimensions for you. The wizard has alsoadded the database dimensions to the new cube, creating cube dimensions. You must now configure your dimensions. You can do this using the Dimension Designer. The Dimension Designer enables you to configure properties, add dimension attributes, define new hierarchies, design attribute relationships, and add localized translations if needed.

To configure a dimension, right-click the dimension in Solution Explorer and select Open. For this example, open the DimTime dimension in the Dimension Designer, as shown in Figure.

The Dimension Designer

The Dimension Designer

The Dimension Designer has four tabs:

Dimension Structure: Here, you can select dimension attributes and arrange these attributes into hierarchies.

Attribute Relationships: In this tab, you specifically manage the relationships between attributes. These relationships help you navigate through your dimension hierarchies.

Translations: Use this tab to add and edit dimension metadata in multiple languages.

Browser: Use this tab to view a processed dimension.

When you first look at the Dimension Structure tab, you will see a blue squiggly line under the name of the Time dimension, Dim Time. This is a best practices warning, which we described earlier in the chapter. Move your mouse pointer over the line, and a tooltip will show you the warning “Create hierarchies in non-parent child dimensions.” Generally, you will want to heed these warnings, and you should work through your cube and dimension designs to eliminate as many as possible. They really do help you to build a better design.

Adding Attributes

In this example, before we can create useful hierarchies, we need to add more attributes. By default,the only attribute that the wizard adds to the dimension is the dimension key.

Add new attributes by dragging them from the Data Source View pane on the right side of the Dimension Designer to the Attributes pane on the left side. For now, drag over the following attributes: CalendarYear, Calendar Semester, CalendarQuarter, and EnglishMonthName. You can drag them over in any order, or you can multiple-select them in the Data Source View pane and drag them all at once.

You can rename the EnglishMonthName attribute to a more friendly form by right-clicking it in the Attributes pane and selecting Rename. For example, you may just want to call this attribute Month. While you are at it, you can also rename the dimension key from TimeKey to simply Date. These friendly names make it much easier for business users to browse the cube and to understand the data.

Creating a Hierarchy

Now that you have added the attributes to the dimension, it is time to create a hierarchy. As you can see in Figure, the Hierarchies pane tells you to drag attributes there to create these objects. As soon as you drag an attribute to the Hierarchies pane, a new Hierarchy object appears, named Hierarchy by default. You can drag more attributes above or below the first one you added in order to create levels of the hierarchy. It is a good idea to pay attention to the order at this stage, although you can rearrange levels later by dragging and dropping them, if you like.

Drag all the attributes over, one by one, to create the hierarchy shown in Figure. You can also right-click in the header space of the new Hierarchy object, select Rename, and give it a friendly name, such as Calendar.

Your first hierarchy

Your first hierarchy

Creating Attribute Relationships

You will notice that your new hierarchy has some warning signs: a yellow triangle and a blue squiggly line. Again, these are best practices warnings. You can mouse over them to see what the advice is. In this case, the warning is “Attribute relationships do not exist between one or more levels of this hierarchy. This may result in decreased query performance.” Good advice! Let’s navigate to the Attribute Relationships tab, shown in Figure, and start to create these.

The Attribute Relationships tab in the Dimension Designer

The Attribute Relationships tab in the Dimension Designer

In this figure, you can see that all the attributes relate to the dimension key, Date, but none of them relate to each other. This would make the dimension difficult to navigate. You could drill down from CalendarYear to Date, but not from CalendarYear to CalendarSemester. To create relationships to improve navigation, simply drag and drop attributes in this view onto the attribute that you want to be the parent. So, drag CalendarSemester onto CalendarYear, CalendarQuarter onto CalendarSemester, and Month onto CalendarQuarter. Now your hierarchy should look like Figure.

Attribute relationships

Attribute relationships

We are not quite finished here yet. To see why, navigate back to the Dimension Structure tab for a moment. You will see that there is still a blue squiggly line under the name DimTime in the Attributes pane. The warning is “Define attribute relationships as ‘Rigid’ where appropriate.” This tells us that all our attribute relationships are flexible, but we expect that some should be rigid. A rigid attribute relationship is one that we do not expect to change. For example, in our Time dimension, the month January will always be part of CalendarQuarter 1.

Return to the Attribute Relationships tab, where you can modify these relationships. At the bottom right of the Attribute Relationships tab, you will see the Attribute Relationships pane, as shown in Figure.

Listed attribute relationships

Listed attribute relationships

You can multiple-select all the relationships here, right-click, and then select Properties. In the Properties window, change the RelationshipType property from Flexible to Rigid. All the attribute relationships here should be set as Rigid.

Making Attribute Hierarchies Invisible

We have nearly completed our dimension configuration. All that remains is to return to the Dimension Structure tab. Here, you will still see the blue squiggly line under the dimension name, but this is the last one—promise! The warning is “Avoid visible attribute hierarchies for attributes used as levels in user-defined hierarchies.” This warning is because users may be confused if dimension attributes are visible in different ways. However, sometimes this is acceptable. For example, business users may like to look at all sales in November or December, across all years, to evaluate holiday season sales. Yet they may also like to browse by December 2004, December 2005, and so on. To enable this scenario, we will make all the attribute hierarchies invisible, except for Month.

To do this, multiple-select all the attributes in the Attributes pane, except Month. Right-click and select Properties. In the Properties window, set the value of the AttributeValueVisible property to False.

Dismissing Best Practices Warnings

Are you still worried that there is a warning under the dimension name? Fortunately, it is possible to dismiss warnings, both globally and for a specific instance. In this case, we want to dismiss only the warning for the Month attribute. This is quite easy to do.

Select Build ➤Build Solution in BIDS. When the build has completed (you can see “Build succeeded”

in the status bar at the bottom of the screen), open the Error List window by selecting View ➤ Error List. As you can see in Figure, there are several errors relating to other dimensions. You should fix these later,

in the same way as we have done for the Time dimension. For now, let’s clear up our immediate concerns.

The Error List window showing best practices warnings

The Error List window showing best practices warnings

Right-click beside the error that reads “Dimension [Dim Time]: Avoid visible attribute hierarchies for attributes used as levels in user-defined hierarchies” and select Dismiss. In the Dismiss Warning dialog box, you can dismiss this particular instance of the warning by clicking OK. However, before doing so, it is useful to add a note about why you are dismissing the warning—for your own reference or for others who may review the cube design. Add a short note, such as “Allowing multiple views of Month,” and then click OK to dismiss the warning. You will notice that the blue warning line under the dimension name (in the Attributes pane of the Dimension Structure tab) disappears as soon as the object model validates the cube metadata.

Look again at Figure. You may also have been surprised to see the warning “The database has no Time dimension. Consider creating one.” Surely, we have been creating a Time dimension? Indeed, we have, but Analysis Services does not know that. To mark our dimension as a Time dimension, right-click the dimension name (DimTime) in the Attributes pane of the Dimension Structure tab and select Properties. In the Properties window, find the Type property and change it from Regular to Time. To see that this has been effective, you can select Build ➤Build Solution and look at the Error List window again. The warning has disappeared, and our dimension is now in good shape. Remember to configure your other dimensions in the same way.

Deploying Projects

We must deploy our sample project in order to demonstrate some advanced features of cube development.

It is important to recognize that a project is a local metadata representation of the objects that Analysis Services will manage. Deploying a project makes constructs such as cubes become visible to the Analysis Services server.

Configuring a Project for Deployment

In a real-world situation, developers might deploy to their local machine or a training/development host in order to debug their project. Once a project was behaving correctly, developers may then deploy it to a staging or production server. To change the deployment host machine, select Project ➤Properties. In the Property Pages dialog box, select Configuration Properties, then Deployment. In the Target section, specify the server to which the project should be deployed, as shown in the example in Figure.

Internet Sales Property Pages dialog box

Internet Sales Property Pages dialog box

In this dialog box, you will see a button labeled Configuration Manager. C# and VB .NET developers will recognize what this button does. Clicking Configuration Manager enables a BIDS developer to create a production configuration (for example) in addition to the standard development configuration associated with the project. The development configuration could utilize localhost, while the production configuration could access BigMainCorporate host.

The standard toolbar of BIDS has a drop-down list that displays the current configuration, which is set to Development by default, as shown in Figure. You can select an alternate configuration from this drop-down list, which allows you to deploy an Analysis Services project to an alternate host without needing to make the change through the Property Pages dialog box for each alternate deployment.

BIDS standard toolbar with configuration drop-down list

BIDS standard toolbar with configuration drop-down list

Deploying a Project

You can deploy a project by selecting Build ➤Deploy Solution of Build project name—Build Internet Sales for our example. Alternatively, you can right-click the project within Solution Explorer and choose Deploy from the context menu.

Deployment is not an instantaneous process. You can watch the status of the deployment using the BIDS Output window (View ➤ Other Windows ➤ Output). The process of deployment could involve more than just the Analysis Services project. When you select Build ➤Deploy Solution, all deployable projects within the solution will be deployed. This could include Analysis Services projects, Reporting Services projects, Integration Services projects, and CLR projects such as a C# or VB .NET DLL that contains user-defined functions used by the Analysis Services project. In the case of a CLR project, deployment will be to the SQL Server on which the user-defined function is to reside. In the case of other project types (Analysis, Reporting, and Integration Services), deployment is to their respective servers.

Processing a Project

The BIDS Build menu and context menu accessed by right-clicking a project within Solution Explorer include options to either deploy or process a cube. Thus far, we have discussed deployment, which is the process of pushing the project and its objects (data sources, dimensions, cubes, and so on) to a specific Analysis Services server. Simply deploying does not make the underlying data associated with the cube visible. For example, the data associated with a cube will not be browsable until it is processed. When an object such as a cube is processed, Analysis Services copies data from the underlying data source or data sources into the cube objects.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

SQL Server 2008 Topics