Sunday, 15 January 2012

SQL Server Analysis Services (SSAS) - Analysis Service Project

Step 1: How to Start SQL Server Business Intelligence Development Studio ( BIDS)
  1.   Start > SQL Server 200X >  SQL Server Business Intelligence Development Studio (Please launch it by "Run As Administrator" else it might retrist access in deployment process. ) 
  2.  Then click File Menu > New > New Project
  3.   Select "Business Intelligence Project" in Project Types column on left
  4.   Select "Analysis Service Project" in templates column on right.
  5.   Provide Name for the project for e.g "MyAnalysisServiceProject", set location/path for the project solution. and click OK.
Step 2 : How to Add New Data Source
  1.   In solution explorer, under "MyAnalysisServiceProject" (Project) node, there will be a director "Data Sources", right click on this directory & click "New Data Source...".
  2.   Read first screen for your information, and then click Next.
  3.  Select option "Create a data source based on an existing connection or new connection"
  4.   If you are adding the data source for the first time, click "New...", it will popup window with title "Connection Manager".
  5.  Choose Provider, by default "SQL Server Native Client" is selected.
  6.  Provide Server Name where your Database resides, Provide credentials either with "Windows Authentication" or "SQL Server Authentication", choose Database which you want to use in the Analysis. and then Click Next.
  7. Choose "Use the service account" and click next.
  8. Provide name for the datasource anc click "Finish".
You will see that new datasource with the name you provided in last step wil appear under the folder Data Sources in the solution exlorer.

Step 3: How to Add New Data Source View
  1. In solution explorer, under "MyAnalysisServiceProject" (Project) node, there will be a director "Data Source View", right click on this directory & click "New Data Source View...".
  2. Read first screen for your information, and then click Next.
  3. In next screen, we will have data source which we created in step 2, click Next again.
  4. In next screen, we will have all the tables and views listed which exist in the data source we selected. We will have to select the tables which we want to use for the data analysis. Select the desired tables and click ">" , and if you want to add the related tables to those you have selected, click "Add related tables". After we have finished our selection process click Finish.
  5. BIDS will automatically display the schema of the new data source view.
Now we have Data source from which we want to fetch data and we have created a view schema structure.

Step 4 : How to structure a Cube
  1. Right-click on the Cubes folder in Solution Explorer and select New Cube.
  2. Read first screen for your information, and then click Next
  3. 3. Select the option to Use Existing Tables.
  4. Click Next.
  5. The data source view which we created in Step 3 should be selected in the drop-down list at the top.
    Place a checkmark next to the tables which you want to designate it as a measure
    group table, such as SalesOrder table having column Amount. then click Next.
  6. Remove the check mark for the fields which are not indicating that as measures we wish to summarize, and click Next.
  7. Leave all other tables selected as dimension tables, and click Next.
  8. Name the new cube MyCube and click Finish.
Step 5 : How to define dimensions

           Cube wizard has already defines the dimensions, but the wizard will not add the attributes with the dimensions, it will have only the keys, we will have to add required columns like ProductName , Country Name  by our selves.
  1.  In BIDS, double click on any one of the Dimension under Dimensions director in the Solution Explore. 
  2. Drag the listed columns from the right-hand panel (named Data Source View) and drop them in the left-hand panel (named Attributes) to include them in the dimension.
Step 6 : How to define Hierarchy
  1.  In BIDS, double-click on DimDate in the solution explorer
  2.  Create a new hierarchy by dragging the field from the left-hand pane (called Attributes) and drop it in the    middle pane (called Hierarchies.)
  3. Add a new level by dragging the next level field from the left-hand panel and drop it on the <new level> spot in the new hierarchy in the middlepanel. 
  4. Add a third level by dragging the field to the <new level> spot in the hierarchy.
  5. Right-click on the hierarchy and rename it to according to your need.
 Step 7: How to Deploy and Process Cube
             At this point, you’ve defined the structure of the new cube - but there’s still more
             work to be done. You still need to deploy this structure to an Analysis Services
             server and then process the cube to create the aggregates that make querying fast
             and easy
             To deploy and process your cube, follow these steps:
            1. In BIDS, select Project from the menu system.
            2. Choose the Deployment category of properties in the upper left-hand corner of
                the project properties dialog box.
            3. Verify that the Server property lists your server name. If not, provide it your server name.
        After checking all these, right click on project node in the solution explorer and click deploy. It will         show   deployment processes in the Deployment Progress window. If there will be no problem occured, it will give message "Deployment Completed Successfully". It might ask you for the credentials, before the deployment process starts, provide it the data source (database) credentials.
          It might raise an error like :
                "Error    9    Either the 'Server' user does not have permission to create a new object in 'Server', or the object does not exist.

Don't worry , relax : )......just save the solution....and again Launch the BIDS by right click on the icon and "Run As Administrator".

After the deployment is successful, you can browse the data cube you just generated. Right click on "MyCube" and click browse. It will open the cube browser, now you can play with it as you wish, drag the measure group items in the "Data/Detail Fields" section, and column and row fields in the respective sections labeled on the center area, as we do in Tabular reports.

So, thats all for this post. In next article I will show you "How to create reports" based on this Analysis Service Project. : ) ...
Thanks for your time.

No comments:

Post a Comment