SSAS Cube Creation

To implement an Analysis Services database, you need to take, at a minimum, the following steps:

1.     Create a data source.

2.     Create a data source view.

3.     Create one or more dimensions.

4.     Create a cube.

5.     Deploy the database.

Data Source:

Your data warehouse is likely made up of disparate data sources such as Microsoft SQL Server, Oracle, DB2, Teradata, and so forth. Analysis Services can easily deal with retrieving relational data from various relational databases. Data source objects contain information needed to connect to a data source such as server name, catalog or database name, and login credentials. You establish connections to relational servers by creating a data source for each one.

1. Now open Visual Studio 2010.

2. Then go to File –> New –> Project.

3. A New Project Window opens. In that select Business Intelligence from Installed Templates. Then select Analysis Services Multidimensional and Data Mining Project. Also give Name of project i.e. Start SSAS Multidimensional.

Then click on OK button.

4. Now your Project’s Solution Explorer looks like below.

5. Now right click on Data Sources and Select New Data Source.

6. A Data Source Wizard opens, In that select checkbox of Don’t show this page again. Then click on Next Button

7. In next step, click on New Button.

8. A Connection Manager dialog-box opens. In that Enter the Server Name and provide necessary credentials. Then select AdventureWorksDW Database.

Then click on OK button.

9. Now you will see screen like below.

Now click on Next Button.

10. In next step, Impersonation Information page appears. On this page, you can configure the Windows security credentials.

·      Use a Specific Windows User name and Password option lets you enter user name & password of a Windows user account.

·         Use the Service Account option will have Analysis Services use its service logon user ID to connect to the data source.

·   Use the Credentials of the Current User option is only used for some specialized circumstances. It is important to note that when you use this option, Analysis Services will not use the Windows user name and password of the current user for most processing and query tasks.

·      Inherit option causes this data source to use the impersonation information contained in the Analysis Services Data Source Impersonation Information database property.

The most commonly selected options are Use the service account or Use a specific windows user name and password.

Here we select Use the service Account option and then click on Next button.

11. Leave AWDW as the data source name and click Finish to complete the wizard

12. Now you will see newly created data source in your Solution Explorer.

Data Source View:

When working with a large operational data store, you don’t always want to use all the tables in the database. With Data Source Views (DSVs), you can limit the number of visible tables by including only the tables that are relevant to your analysis. DSVs enable you to create a logical data model upon which you build your multidimensional database. A DSV can contain tables from one or more data sources.

1. First of all open Visual Studio 2010 and open any SSAS project.

2. In solution explorer, right click on Data Source Views and select New Data Source View.

3. A Data Source View Wizard window opens. In that check don’t show this page again and click on Next button.

4. In next step, select existing data source if you’re already created it otherwise create New Data Source. Here we select existing Data Source. Then click on Next Button.

5. Now Select Tables and Views page appears. It will show lists of Available Objects and Included Objects. Now Double-Click on DimProduct to add it to the Included Objects list. Now Select Tables and Views page should look like the following:

6. Now Select the DimProduct table and then click on Add Related Tables. Then click on Next button.

7. In next step, you will see the summary of Data Source View. Also give name of data source view. Here we give AWDW View as name.

Then click on Finish Button.

8. Now your data source view design should look like following screen show.

Cube and Dimensions:

Cubes: Cubes are the foundation for analysis. A collection of measure groups (discussed later in this chapter) and a collection of dimensions form a cube. Each measure group is composed of a set of measures. Cubes can have more than three dimensions; they are mathematical constructs and not necessarily the three-dimensional objects their name suggests.

Dimensions: Dimensions are the categories by which you slice your data to view specific quantities of interest. Each dimension contains one or more hierarchies. Two types of hierarchies exist: attribute hierarchies and user hierarchies. In this book, attribute hierarchies are referred to as attributes, and user or multilevel hierarchies are referred to as hierarchies. Attributes correspond to columns in a dimension table, and hierarchies are formed by grouping several related attributes.

1.  In the Solution Explorer, make a right click on the Cubes folder and select "New Cube..."

2. On the Welcome to the Cube Wizard page, please click on Next.

3.  On the "Select Creation Method" page, please select the option "Use existing tables", and then click next.

4.  On the "Select Measure Group Tables" page, after making sure the data source view we created before is selected, make a click on "Suggest" to have the Wizard suggest you which tables to use on the Cube. Then click on Next to go to the next step.

5.  On the "Select Measures" page, please verify all check boxes are checked and click on Next.

6.  On the "Select New Dimensions" page, make sure each check box is checked and then click on Next.

7.  On the "Completing the Wizard" page, change the content of the "Cube name:" text box for the text "AWDW Cube" and click Finish.

Deploy and Process Cube:

1. In Solution Explorer, right click on Project Name as SSAS Multidimensional -- > Click Process.

2. After clicking on process it first asks “Would you like to build and deploy the project first?” so if this is a first time to deploying or processing a cube then click yes otherwise no.

3. Now the process has started. Here it is showing that deployment completed successfully. This means in your analysis service database, the structure of database is created, but you can’t access your cube, because the process is not complete. It shows the next wizard.

4. Click on Run button to process the Cube.

5. Once processing is complete, you can see Status as Process Failed àClick Close

6. So for removing this error go to solution explorer. Double click on .ds file. Now here go to Impersonation Information tab and select “use a specific windows user name and password”. And put your username and password here. Now click OK

7. Again select the database name or project name from solution explorer, right click and then click on process. Here is also see deployment completed successfully.

8. Now click on run, this time it is showing process succeeded.

Cube browsing:

1. So the cube is ready, you can access cube either from BIDS or from SSMS by connecting the analysis service.

2. Here we can drag and drop column based on requirements.

Roles:

Roles are objects in a database that control access permissions to the database objects (read, write, read/write, and process).

1. Right-click the Roles folder in the Solution Explorer of the sample Adventure Works DW

Multidimensional project, and choose New Role.

2. On the General tab, you can give members you assign to this role full control of the database, or you can limit their activities to processing the database or simply reading the database definitions

3. Click the Membership tab. On the Membership tab, you can add the list of users for whom

You want the specific access you have selected.

4. Analysis Services accepts all domain users or machine users in this dialog.

5. In Data Sources tab, which you use to restrict users from accessing the cube’s data sources.

6. Enabling the Read Definition option allows the users to retrieve information such as database name, tables, views, and so on of the data source.

7. Here you have the option to grant access to specific cubes. The Process option enables you to grant users the ability to process selected cubes. Select Read/Write access to the AWDW Cube.

8. You can also see there is the option of Local Cube/Drillthrough Access. These options enable you to specifically grant users the ability to create local cubes or drillthrough to more detailed data.

9. On the Dimensions tab, you have the option to control which database dimensions or cube dimensions a user can access if they’ve already been granted access to the cube. 

Mining Structure:

Data mining is the process of analyzing raw data using algorithms that help discover interesting patterns not typically found by ad-hoc analysis. Mining Structures are objects that hold information about a data set. A collection of mining models forms a mining structure. Each mining model is built using a specific data mining Algorithm and can be used for analyzing patterns in existing data or predicting new data values. Knowing these patterns can help companies make their business processes more powerful.

1. To create a relational mining model, right-click the Mining Structures folder in the Solution Explorer, and select New Mining Structure, to launch the Data Mining Wizard that helps you to create data mining structures and models.

2. The welcome page provides information about the Data Mining Wizard. Click the Next button.

3. You can now see the Select the Definition Method page, which enables you to create a mining model from a relational data source or from a cube. Select the From Existing Relational Database or Data Warehouse radio button

4. On the Create the Data Mining Structure page, you can select the data mining technique to use for modeling. If you click the drop-down list box, you can see all the available algorithms. Select Microsoft Decision Trees, and click next.

5. On the Select Data Source View page, select AWDW View and click next.

6. The Specify Table Types page enables you to select the tables upon which you create a mining model. The Specify Table Types page shows two selections: Case (the Primary table) and Nested.

7. On the Specify the Training Data page of the wizard, you select the columns from the source tables that are to be used in creation of mining models. In addition, you need to specify whether a specific column should be used as a key column, input column, or predictable column.

8. The selected columns along with their content types and data types are shown in the Specify Columns’ Content and Data Type page. The relational data type of a column is mapped to the corresponding data type used within Analysis Services 2012 by the Data Mining Wizard.

9. In SSAS 2012, the ability to specify some percentage of your data for testing the model is built into the product. The Create Testing Set page shown in Figure 12-10 enables you to specify the percentage of data to hold back and also the maximum number of cases in the testing data set.

10. All mining models are contained within a mining structure; Analysis Services automatically creates a mining structure with the same name as the mining model. However, you can use different names for the mining structure and the mining model in the completion page and click Finish to create the mining model.

11. The mining structure object can be seen in the mining structure editor. The mining structure editor contains five views: Mining Structure, Mining Models, Mining Model Viewer, Mining Accuracy Chart, and Mining Model Prediction. By default, you will be in the Mining Structure tab.

12. The Mining Models view shows the mining models in the current mining structure. You can have one or more mining models within each mining structure. These four usages are represented as Input; Predict Only, Predict, and Ignore, respectively, in the Mining Models view. These usages can be selected from the drop-down list box corresponding to a column in a mining model.

13. Right-click the project name DM2012Tutorial from Solution Explorer, changes the Deployment Server in Property Pages, and click OK. Press the F5 button to deploy the mining model.

14. SQL Server Data Tools (SSDT) sends the definition of the entire project you created to the server along with a process request. When the database is processed, SSDT switches the view to the Mining Model Viewer, as shown in Figures.