Home » Posts filed under Analysis Services
Showing posts with label Analysis Services. Show all posts
Showing posts with label Analysis Services. Show all posts

SSAS 2012 Interview Questions with Answers

Posted by 20Fingers2Brains on Tags: , ,

In this tutorial we will discuss most frequently asked SSAS 2012 (SQL Server Analsysis Services ) Interview Questions with Answers

This interview questions and answers for SSAS 2008/2012 will be helpful in cracking the interview and may get you a good job

1.What is Analysis Services
1.Microsoft SQL Server 2012 Analysis Services (SSAS) delivers online analytical processing (OLAP) and data mining functionality for business intelligence applications. 
2.Analysis Services supports OLAP by letting we design, create, and manage multidimensional structures that contain data aggregated from other data sources, such as relational databases. 
3.For data mining applications, Analysis Services lets we design, create, and visualize data mining models that are constructed from other data sources by using a wide variety of industry-standar data mining algorithms
2.What are the features of Analysis Services
1.Ease of use with a lot of wizards and designers.
2.Flexible data model creation and management
3.Scalable architecture to handle OLAP
4.Provides integration of administration tools, data sources, security, caching,    and reporting etc.
5.Provides extensive support for custom applications
3.What is the need for SSAS 2012 component?
1.Analysis Services is the only component in SQL Server using which we can perform Analysis and Forecast operations
2.Faster Analysis and Troubleshooting.
3.Ability to create and manage Data warehouses
4.Apply efficient Security Principles
4. What languages are used in SSAS 2012
1.Structured Query Language (SQL)
2.Multidimensional Expressions (MDX) - an industry standard query language orientated towards analysis
3.Data Mining Extensions (DMX) - an industry standard query language oriented toward data mining.
4.Analysis Services Scripting Language (ASSL) - used to manage Analysis Services database objects

5.What is the difference between a derived measure and a calculated measure? 
  1. The difference between a derived measure and a calculated measure is when the calculation is performed. 
  2. A derived measure is calculated before aggregations are created, and the values of the derived measure are stored in the cube. 
  3. A calculated measure is calculated after aggregations are created, and the values of a calculated measure aren’t stored in the cube. 
  4. The primary criterion for choosing between a derived measure and a calculated measure is not efficiency, but accuracy
6. What is FASMI ?
A database is called a OLAP Database if the database satisfies the FASMI rules 
  1. Fast Analysis– is defined in the OLAP scenario in five seconds or less.
  2. Shared – Must support access  to data by many users in  the factors of Sensitivity and Write Backs.
  3. Multidimensional – The data inside the OLAP Database must be multidimensional in structure.  
  4. Information – The OLAP database Must support large volumes of data.
    7.What is a partition? 
  1.     A  partition in Analysis Services is the physical location of stored cube data. 
  2.    Every cube has at least one partition by default. Each time we create a measure group, another partition is created. 
  3.   Queries run faster against a partitioned cube because Analysis Services only needs to read data from the partitions that contain the answers to the queries. 
  4.    Queries run even faster when partition also stores aggregations, the pre calculated totals for additive measures. Partitions are a powerful and flexible means of managing cubes, especially large cubes
      8. What are the roles of an Analysis Services Information Worker?
      The role of an Analysis Services information worker is the traditional "domain expert" role in business intelligence (BI) someone who understands the data employed by a solution and is able to translate the data into business information.The role of an Analysis Services information worker often has one of the following job titles: Business Analyst (Report Consumer), Manager (Report Consumer), Technical Trainer, Help Desk/Operation, or Network Administrator

   9.Explain the TWO-Tier Architecture of SSAS?  
  1.   SSAS uses both server and client components to supply OLAP and data mining functionality BI Applications.
  2.   The server component is implemented as a Microsoft Windows service. Each instance of Analysis Services implemented as a separate instance of the Windows service.
  3.   Clients communicate with Analysis Services using the standard the XMLA (XML For Analysis) , protocol for issuing commands and receiving responses, exposed as a web service  
    10. How many types of dimension are possible in SSAS?
  1.    Bill of Materials
  2.    Currency
  3.    Channel
  4.    Customer
  5.    Geography
  6.    Organizations
  7.    Products
  8.    promotion
  9.    Regular
  10.    Scenario
  11.    Time
  12.     Unary
  13.    Accounts

SSAS 2012 Introduction to SQL Server Analysis Services

Posted by 20Fingers2Brains on Tags: , , , , ,


SSAS 2012 2008 Introduction to SQL Server Analysis Services for beginners and scenarios to use SSAS 

  1. SSAS 2012 is the technology that is provided by SQL Server to create cubes. 
  2. Organizations use cubes to manage large volumes of data so that complex queries can be retrieved in a more efficient manner than if the queries were to be executed against a relational database. 
  3. With cubes, there is also the ability to preaggregate data and store them in the cube for faster retrieval of summary information.
  4. The most underused aspect of SSAS is data mining that enables a business to perform trend analysis and identify relationships between data that may not be immediately apparent to end users when looking at the data. 
  5. Analysis Services is a fantastic tool that can host the data in a central point as information
  6. SQL Server Analysis Services provides the ability to efficiently query multidimensional data. It also provides the capability to use data mining to extrapolate trends and patterns within data. 
  7. There are core components that you should be aware of to create a cube.
  8. At its core, data sources must be defined to provide connection information to the source data for the cube. You can then define data source views to define the specific tables on which the cube is based. This will typically involve dimension tables and fact tables from a data warehouse.
  9. You can then define dimensions within the cube against dimension tables within the data warehouse. The dimensions will provide contextual information and act as the axis for the cube. For improved readability, you can also define hierarchies to allow the user to drill down into the dimension data.
  10. On completing the creation of the dimensions, we can then run the cube wizard to create the cube. The wizard allows you to create a cube using existing dimensions or from data from a table. 
  11. You can also create an empty cube if you do not have the data available. Once the cube is created, you can configure cube partitions. This enables you to spread the data over multiple physical disks to improve the performance of cube processing and querying data in the cube. 
  12. SQL Server Analysis Services also provides additional OLAP objects to extend the functionality of the cube, including calculated members, KPIs, actions and translations.
  13. For data mining, you can use new or existing data sources and data source views to point to the source data. The source data for data mining can be an existing cube or data from a table or a view. 
  14. You can then create a data mining structure that integrates the source data with a data mining algorithm, known as a data mining model in order to find patterns in the data using the data mining model. On completing this, you have the ability to view and validate the results that are returned back to the data mining viewer.

SSAS 2012 Scenarios for Using SQL Server Analysis Services

  1. Analysis Services enables you to analyse large quantities of data. With it, you can design, create and manage multidimensional structures that contain detail and aggregated data from multiple data sources.
  2. Scenarios that SSAS can help with include:
  • The analysis of large volumes of data efficiently.
  • The ability to define server level KPIs that can be created and maintained centrally.
  • Add value to cubes by creating calculations by using multidimensional expressions.



SSAS 2012 2008 SQL Server Analysis Services Components Such as Cubes

Posted by 20Fingers2Brains on Tags: , , , , , ,


SSAS 2012 2008 SQL Server Analysis Services Components

  1. There are core components that are used to create an Analysis Services solution within Business Intelligence Development Studio. 
  2. Understanding these components will help you to start multidimensional and data mining solutions within your BI solution.
The Main SSAS Components are stated below:
  1. Data sources
  2. Data source views
  3. Cubes
  4. Dimensions
  5. Measures
  6. Slice and dice
  7. Data mining

 Let us now explain each one of this in detail
Data sources in SSAS 2012
  1. Data sources provide the connection information for the source system on which the Analysis Services solution is based.
  2. Typical configuration for data sources involve specifying the name of the server on which the data is stored. You also specify the credentials required to connect to the server and the name of the database to access. You should also consider that additional settings may be required between different data sources.
  3. The data sources that are supported by SSAS  2012 2008 include:
  • SQL Server 7.0 using the SQL OLE DB Provider or the .NET native OLE DB provider (x86, x64 and ia64).
  • SQL Server 2000 using the SQL OLE DB Provider or the .NET native OLE DB provider (x86, x64 and ia64).
  • SQL Server 2005 using the SQL Server Native Client OLE DB provider or the SqlClient.NET managed provider (x86, x64 and ia64).
  • SQL Server 2008 using the SQL Server Native Client OLE DB provider or the SqlClient.NET managed provider (x86, x64 and ia64).
  • Oracle 9.0 using the Microsoft OLE DB Provider for Oracle or the .NET native OLE DB provider (x86 only).
  • IBM DB2 8.1 using Microsoft OLE DB Provider for DB2 (x86, x64, ia64)—only available for Microsoft SQL Server 2005 Enterprise Edition or Microsoft SQL Server 2005 Developer Edition.
  • Access with Microsoft Jet 4.0 OLE DB provider (x86 only).
  • Teradata v2R6 with OLE DB 1.3 provider from NCR (x86 only).
  • Teradata managed provider (x86, x64 and ia64).  

Data source views in SSAS 2012

  1. Data source views are objects based on a data source that provide an abstraction of a subset of tables, columns and relationships from the data source that you require as part of your Analysis Services solution.
  2. This abstraction layer holds metadata about the objects and allows you to create an Analysis Services solution without a permanent connection to the data source.
  3. To run the cube wizard or the data mining wizard, a data source view must be created.
  4. Annotations can be added to provide friendly names for the tables and columns to improve the readability for the user.
  5. The type of information that is defined within a data source view includes:
  • A data source view name.
  • A definition of any subset of the schema retrieved from one or more data sources including:
    • Tables
    • Columns
    • Relationships

Cubes in SSAS 2012
  1. An OLAP database can hold one or more cubes. A cube is a fundamental unit of multidimensional analysis.
  2. A cube consists of dimensions and measures. 
  3. The dimensions form the axis of the cube and provide contextual information for the data that resides in the cube.
  4. This information is known as measures.

 Dimensions in SSAS 2012
  1. Dimensions provide the contextual information for the data that resides in a cube. 
  2. The dimensions in a cube typically map to a dimension table within the data warehouse.
  3. As dimensions are used to describe a business entity, dimensions consist of attributes that provide specific information about the business entity. 
  4. These attributes typically map to a specific column within the dimension table in the data warehouse.
  5. To improve readability, hierarchies can be introduced within the dimension. 
  6. For example, within a time dimension, a hierarchy named CalendarYear can be created containing three levels—Year, Quarter and Month. 
  7. These levels allow the user to easily drill down into specific time periods

 Measures in SSAS 2012
  1. Measures provide the numeric information that resides inside the cube. 
  2. They typically map to the measures columns in the fact table of a data warehouse.
  3. As well as including the measures information from the fact table, SSAS also creates preaggregated versions of the measures.
  4. For example, a fact table may consist of a measure named OrderQuantity. As well as including the detail information that is provided by the fact table, Analysis Service creates preaggregated OrderQuantity totals for yearly, quarterly or monthly totals. By creating this preaggregated data, queries for this information is retrieved more efficiently.
Slice and dice in SSAS 2012 
  1. Slicing and dicing is the technique that can be used to interrogate the data from within a cube.
  2. This technique is implemented in products such as Excel 2007/10/13. This allows you to use the pivot table feature to slice and dice the data to retrieve specific data.
  3. Business Intelligence Development Studio provides a browser window that allows you to slice and dice the data to test the results that is returned before the cube is deployed into a production environment.
  4. Furthermore, you can also employ this technique within Reporting Services as the basis for providing data for the reports that are deployed through SSRS.
Data mining in SSAS 2012 
  1. Data mining is a feature in SSAS that enables you to extrapolate trends and patterns in the data.
  2. You can create data mining structures to explore the data against algorithms that are provided
  3. There is also a Data Mining Add-in for Excel 2007 2010 2013 that allows you to explore the results that are returned by a data mining structure. You can also use the add-in to create your own data structures

SSRS 2012-2008 Introduction to SQL Server Reporting Services

Posted by 20Fingers2Brains on Tags: , , , , , , , ,

SSRS 2012 Introduction to SQL Server Reporting Services

  1. In this article i will be explaining the scenarios for using SQL Server Reporting SSRS 2012 and a small introduction to reporting services 
  2. SSRS provides the ability to present information from both Analysis Services cubes and relational data sources as reports. 
  3. With flexible report designs and a vast improvement on the visualisation of reports, you have the ability to create compelling reports. 
  4. Furthermore, interactivity can be added to allow the end user to become involved with the reports. This can be as simple as adding interaction in the form of parameter lists or drill through.
  5.  You can also empower end users to create their own reports by using Report Builder. All of this can be managed with a simplified, Web-based interface that allows you to host and deliver reports when the user needs it.
  6. SSRS provides a complete set of tools and a streamlined server architecture that is designed to support the reporting requirements across the enterprise, from a wide variety of data sources at a reduced total cost of ownership.
  7. Extensive enhancements have been introduced to the authoring capabilities within Reporting Services 2008 2012, providing the ability to create comprehensive, richly formatted reports quickly and easily through a variety of reporting tools. 
  8. The reports created are stored within a Report Definition Language (RDL) file.
  9. Reporting Services can also be tightly integrated with MOSS and provide highly customised applications within Microsoft Visual Studio 2008 2012 through the use of report viewer controls.
  10. A restructuring of the server architecture has reduced the configuration and maintenance tasks required to manage Reporting Services.
  11. For example, the dependency on IIS has now been removed, reducing the need to configure multiple service accounts.
  12.  A redesigned report and rendering engine delivers reports of any scale more efficiently.  
  13. Microsoft Word 2007 has been introduced as a new, out-of-the-box rendering format. 
  14. The delivery of reports can also be managed through subscriptions that can be controlled by the user or the administrator.
  15. With SSRS 2008, you can create, manage and deploy reports much more efficiently.

Scenarios for Using SQL Server Reporting Services 2012

  1. SQL Server Reporting Services 2008 is installed as part of the SQL Server setup. 
  2. When Reporting Services is selected in the features selection screen, the Reporting Services Configuration screen provides three options that can be selected to determine how Reporting Services can be accessed. This can be useful if Reporting Services is to be used by internal users, external users or if you wish to integrate Reporting Services with Microsoft Office SharePoint Server.
  3. By selecting the Install in Native Mode default configuration option, Reporting Services is installed with all of the default options and is operational once the installation is complete. 
  4. This is an ideal choice if the Reporting Services solution is to be used by internal users only and you do not wish to change the default settings.
  5. Users will access the reports by typing the URL in Internet Explorer that goes to http://ServerName/reports.
  6. The installation allows you to integrate Reporting Services with Microsoft Office SharePoint Server. Assuming the Reporting Services pack is already installed on the SharePoint Server, Reporting Services will be installed in SharePoint integrated mode whereby the user will access the reports from the SharePoint site rather than the Reporting Services home page. Reporting Services can also use the features available to SharePoint Server as well.
  7. For external access, it is best practice to select the Install, but do not configure the report server option in the Reporting Services Configuration screen. 
  8. In this scenario, you will have to use Reporting Services Configuration Manager to manually set the values for the home page of the Report Server Web site, Report Manager Web site and determine how the Report Server databases are created. 
  9. Furthermore, you will have to modify configuration files to secure the traffic that is sent between the Report Server and the external clients.

SSRS 2012 2008 Reporting Tools Report Builder Manager Designer

Posted by 20Fingers2Brains on Tags: , , , , , , , ,


SSRS 2012 2008 SQL Server Reporting Services Tools
In this article i will  describes the various tools that you can use to create reports in SSRS.
The SSRS 2012 Reporting tools are 
  1. Reporting Services Configuration Manager
  2. Report Designer
  3. Report Builder
  4. Report Manager
  5. SQL Server Management Studio
  6. Command prompt utilities
  7. Use the Report Wizard
Lets explain one by one the reporting tools in SSRS 2012 and 2008 
Reporting Services Configuration Manager
  1. For post-installation configuration, the Reporting Services Configuration tool provides easier management, particularly for those moving from SQL Server 2000 Reporting Services.
  2. Introduced in SQL Server 2005 2008 Reporting Services, the notable change to this version, apart from the look, is the fact that there is only one service account that needs to be configured now that the dependency on IIS is removed. 
  3. Previous versions required a Windows service account and a Web service account to be configured. Below is a summary of options:
  • Server Node. Provides the ability to stop and start the Reporting Services.
  • Service Account. Provides the ability to change the security context under which the Reporting Services run.
  • Web Service URL. Configures the URL access to the report server by creating a URL reservation. A URL reservation defines the virtual directory, IP address, TCP port and optionally a SSL certificate. HTTP.sys uses the URL reservation to direct users requests and allow access to the URL.
  • Database. Provides the ability to change the location of the report server database and to change the credentials for connecting to the report server database.
  • Report Manager. Creates an URL reservation to access the Report Manager Web site.
  • E-mail Settings. Provides the ability to integrate with e-mail by defining a server address and an e-mail address that has the ability to send e-mail messages from the report server.
  • Execution Account. Provides the ability to specify an account to use against report data sources that do not require credentials or to access remote servers.
  • Encryption Keys. Allows the management of the keys that are used to encrypt the sensitive data that is stored in the Reporting Services database. There is the ability to back up and restore the encryption keys, change encryption keys and delete the encrypted content within the database.
  • Scale-out Deployment. Provides the ability to manage multiple instances of Reporting Services that share the same ReportServer database such as a clustered instance. SQL Server 2005 Reporting Services referred to scale-out deployment as Initialization.
Report Designer
  1. SSRS has introduced a new designer that allows business users to connect to data sources and produce richly formatted reports that provide highly visual information in a user-friendly interface. 
  2. The report designer is found within Business Intelligence Development Studio and consists of the following core elements:
  • Data pane. Provides the ability to define data sources and data sets. Items from the Data pane can be dragged onto the designer canvass.
  • Toolbox. Provides the ability to add data regions and other report items to the designer canvass.
  • Report canvass designer. Located in the centre of the Report Designer application, it allows you to develop the report in its entirety.
  • Grouping pane. A grouping pane is located at the bottom of the Report Designer. This provides a quick way of managing groups of data in the canvas designer, without the need for entering into dialog boxes.
  • Properties pane. A context sensitive pane that changes as items on the designer canvass are selected, providing rich formatting and report body options. The Properties pane provides a vast array of options than can vastly improve the visualisation of the reports
Report Builder
  1. Report Builder is an application that empowers power users to create their own ad-hoc reports. 
  2. Report Builder is available in two versions:
  • Report Builder version 1 is a ClickOnce .NET application that allows the creation of unplanned reports. For power users to use the Report Builder version 1, a report model must be created that provides an abstraction layer to the underlying data in a data source. It is also a prerequisite to be able to use the Report Builder version 1. As a result, the report model has a separate project template in Business Intelligence Development Studio.
  • Report Builder version 2 has now been introduced and is not constrained to the requirements of a report model. Report Builder version 2 has the same layout as the report designer that is available in Business Intelligence Development Studio. The application is encased within an Office 2007 application with ribbons to provide shortcut to common tasks. With Report Builder version 2, you can make use of data sources directly, providing a more flexible tool than its previous version.
Report Manager
  1. Report Manager is the Web site that is used to host the reports that are deployed to the report server. 
  2. End users will browse to a predefined URL that is the home page of Reporting Services.
  3. From here, users can browse and export reports.
  4. If they have the permission, they may also be able to subscribe to reports so that they can be delivered via e-mail.
  5. Report Manager also acts as an administrative Web site for the reports.
  6.  Report Server administrators have the same capabilities as the end users, however they can manage report execution, report history and security as well.
SQL Server Management Studio
  1. SQL Server Management Studio provides administrative capabilities for the report server. 
  2. Within the Reporting Services server type, a report server administrator can create shared schedules, create system and item-level security roles and view the jobs that are running on the report server.
  3. You can also manage the report server databases within the database engine, including backing up the database.
Command prompt utilities
There are command prompt utilities that can be used to manage Reporting Services:
  • RSConfig.exe. can be used to perform post installation tasks on the report server. The options available within RSConfig.exe are available within the Reporting Services Configuration Manager.
  • RSKeymgmt.exe. allows you to back up and restore the report server encryption keys. This feature is also available within the Reporting Services Configuration Manager. Encryption keys are used to protect the sensitive data that is stored in the report server, such as data source credentials and passwords. This uses a symmetric key to encrypt and decrypt the sensitive data. The report server service is used to create and unlock the key. If you change the identity of the Report Server service or if you migrate the report server to a new computer, the private key of the Report Server service will no longer be able to unlock the symmetric key. To restore access to the symmetric key, the symmetric key must be re-encrypted by using the private key of the new Report Server service identity. Restoring the symmetric key is the process by which the re-encryption occurs.  
  • RS Utility. The RS Utility is a script host that you can use to perform scripted operations. Use this tool to run Microsoft Visual Basic scripts that copy data between report server databases, publish reports, create items in a report server database and more.
Report Wizard
  1. Reporting Services 2008/2012 provides a wizard that will enable you to create simple reports. 
  2. On completing the wizard, the report is readily available to be deployed to the report server.
  3. Alternatively, you can use the report output from the wizard as the basis to edit the report to provide additional functionality that may not be available in the wizard.
  4. The wizard will present you with options to define a data source and data set. You can select either the table or the matrix data region within the wizard. 
  5. Therefore, if you wish to create a chart or gauge report, the Report Wizard is not appropriate.
  6. Beyond these initial options, you then specify the columns to be included within the data region and define the formatting of the report that is similar to the Report Wizard in Microsoft Access.

SSRS 2012 2008 Data sources Data sets Data regions

Posted by 20Fingers2Brains on Tags: , , , , , , , ,



Data sources - Data sets -Data regions  in SSRS 2012 2008 Reporting Services
  1. Data sources - Data sets -Data regions Core SQL Server Reporting Services Components exist that are important for creating reports in SSRS 2012 and 2008. 
  2. Here we have described this three main components for making better reports
Data sources
  1. Data sources provide the connection information that Reporting Services require to access the source data.
  2. Typical configuration for data sources involve specifying the name of the server on which the data is stored. You also specify the credentials that are required to connect to the server and the name of the database to access. You should also consider that additional settings may be required between different types of data sources.
  3. Multiple data sources can be defined and used within a single report.
  4. The following data sources are supported by Reporting Services out-of-the-box:
  • SQL Server relational databases
  • Analysis Services multidimensional databases
  • XML data sources
  • Report Server Models
  • SAP multidimensional databases
  • Hyperion Essbase
  • Oracle databases
  • Teradata relational databases
  • DB2 databases
  • OLE DB data source
  • ODBC data source
Data sets
  1. Data sets are used to return specific data from a defined data source.
  2. The data that is returned in the data set can then be used in the report.
  3. When retrieving datasets from Analysis Services or XML, the data from these data sources are flattened so that they may be used within a report.
  4. Multiple data sets can be defined and used within a single report
  5. When creating a data set against a SQL Server data source, there are three ways that the data set can be generated:
  • From a specific query
  • From a stored procedure
  • From a specific table or view
Data regions
  1. The data region is the area of the report that holds the data that is generated from a data set. 
  2. A new type of data region that has been introduced is the Tablix data region. 
  3. Tablix combines the benefits of both a table and matrix data region, which gives greater flexibility in the design of the report. 
  4. The Tablix data region is not explicitly found in any of the report authoring tools.
  5. This functionality has been added to the table and matrix data region properties in Report Designer
  6. SSRS provides the following data regions:
  • Table
  • Matrix
  • List
  • Chart
  • Gauge


SSAS 2012 2008 Creating Database Roles

Posted by 20Fingers2Brains on Tags: , , , ,




How to Create Database Roles in SSAS analysis services 2008 and 2012 
  1. Analysis Services allows you to create roles in both Business Intelligence Development Studio and SQL Server Management Studio.
  2. Creating roles in SQL Server Management Studio means that the changes are performed on the deployed system and will take effect the next time the user connects to the Analysis Services instance. 
  3. Note that the change that is made through SQL Server Management Studio is not reflected within the Business Intelligence Development Studio project. 
  4. This results in the roles being overwritten or removed, should the deployment of the project define that the roles members are dropped and created from the project.
  5. This is why using Business Intelligence Development Studio is a better alternative for creating roles if you need to keep a record of the roles existence within the project file. 
  6. Roles that are created within Business Intelligence Development Studio need to be deployed but a record of the role will be kept in the project file.
  7. To create roles, you must be a member of the Server role. 

To create a role in SQL Server Management Studio, perform the following steps:

  1. Connect to an instance of Analysis Services.
  2. In Object Explorer, expand the Analysis Services database, right-click Roles, and then click New Role.
  3. In the New Role dialog box, in the Name box, type a name for the role.
  4. To add members, click the Membership tab, and then click Add.
To create a role in Business Intelligence Development Studio, perform the following steps:
  1. Open an Analysis Services solution in Business Intelligence Development Studio.
  2. In Solution Explorer, expand the Analysis Services project, right-click Roles, and then click New Role.
  3. In the New Role dialog box, in the Name box, type a name for the role.
  4. To add members, click the Membership tab, and then click Add.
The Role Designers are the same functionally. After the role has been named and the members added, you set the permissions the same way on both Role Designers

SSAS Synchronize Databases by Using the Database Wizard in Analysis services 2008 2012

Posted by 20Fingers2Brains on Tags: , , , , ,


SSAS How to Synchronize Databases by Using the Database Wizard in Analysis services 2008 2012



  1. The Synchronize Database Wizard synchronises one way to a database on a separate instance of Analysis Services. 
  2. This can be useful when maintaining the same Analysis Services database on two separate versions of SQL Server and can help with the migration from Analysis Services 2005 to Analysis Services 2008 and . 
  3. If the database does not exist on the instance, it will create the database automatically.
  4. To access the Synchronize Database Wizard, open SQL Server Management Studio, in Object Explorer, right-click the database, and then click Synchronize.
  5. The wizard prompts you to perform the following steps:
  • Select the Analysis Services source instance and database from which to synchronise.
  • Select storage locations for local partitions on the destination instance.
  • Select storage locations for remote partitions on other destination instances.
  • Select the level of security and membership information to copy from the source instance and database to the destination instance.
  • Select whether to synchronise immediately or to save the XMLASynchronize command generated by the Synchronize Database Wizard to a script file for later synchronisation.
After following the above steps the Synchronize Databases task in ssas 2008 and 2012 will be completed 

SSAS Deploying Analysis Services in SQL Server 2008 and 2012

Posted by 20Fingers2Brains on Tags: , , , , ,


In this tutorial example i will explain the following deployment methods in SSAS 2008 2012 analysis services 
  1. Business Intelligence Development Studio
  2. Deployment Wizard
  3. XMLA Scripts
  4. Synchronize Database Wizard
  5. Backup and Restore
  6. Analysis Management Objects (AMO)
SSAS 2008 2012 Analysis Services Deployment Introduction
  1. Deploying Analysis Services can be performed through many methods. 
  2. Understanding the capability of each method will help you to determine the most effective deployment method to employ within your organization.
Analysis Services provides a number of methods to deploy Analysis Services objects. The following table describes the different methods available to perform a deployment.

1.Business Intelligence Development Studio
  1. Business Intelligence Development Studio is one method that can be used to deploy an Analysis Services project. 
  2. This method will prove useful as you develop your Analysis Services solutions to test the functionality of the solution. 
  3. This method builds a complete set of XML files in the output folder containing all of the necessary commands required to build all of the Analysis Services database objects in the project. 
  4. It will also validate the objects that are deployed.
  5. Prior to deploying the solution, you can configure a number of deployment options within the properties of the Analysis Services solution specific to the deployment.
  6.  These options can be accessed by right-clicking an Analysis Services project in Solution Explorer, and then clicking Properties. 
  7. Within the project Properties, click the Deployment page and configure the following settings:
  • Process Options. Determines whether the cube is process as it is deployed.
  • Transactional Deployment. Determines if the deployment is transactional and rolls back the deployment should it fail.
  • Deployment Mode. Specifies if all of the Analysis Services objects are deployed or only the changes are deployed.
  • Server. The name of the server to which the Analysis Services solution is deployed.
  • Database. The name of the database in which the Analysis Services solution is deployed.
2. Deployment Wizard
  1. The Deployment Wizard is a very useful feature for those database professionals who do not work within Business Intelligence Development Studio but need to deploy the Analysis Services solution. 
  2. This can include situations where a company’s infrastructure team is responsible for the deployment of the Analysis Services solution to the production SQL Server.
  3. The Deployment Wizard uses the XML files in the Output folder that has been created from within Business Intelligence Development Studio as the basis for the deployment.
  4. At the end of the wizard, you also have the option to generate an XMLA script of the Analysis Services objects and the deployment options that have been defined in the Deployment Wizard. 
  5. The Deployment Wizard can be run both interactively and at the command prompt.
  6. To access the Deployment Wizard interactively, click Start, point to All Programs, click Microsoft SQL Server 2008, point to Analysis Services, and then click Deployment Wizard.
The wizard consists of the following screens:
  • The wizard will first require the location of the *.asdatabase file, which, by default, is located in the bin folder of the Analysis Services project.
  • You are then prompted to specify the Server and database name of the Analysis Services database.
  • The third screen allows you to define any partitioning and security options that will be set as the cube is deployed.
  • You can then define properties for the components of the cube including configuration and optimisation settings.
  • The next screen allows you to specify the processing mode and whether the deployment is transactional.
  • The penultimate screen allows you to specify if you wish to generate a deployment script.
  • The final screen will start and complete the deployment.
Files used in the deployment of Analysis Services
When you build an Analysis Services project, Business Intelligence Development Studio generates XML files for the project. Business Intelligence Development Studio puts these XML files in the Output folder of the Analysis Services project. By default, output is out in the \bin folder. The following table lists the XML files that Business Intelligence Development Studio creates:
XMLA file

Description
<project name>.asdatabase
Contains the declarative definitions for all the Analysis Services objects in the project.
<project name>.deploymenttargets
Contains the name of the Analysis Services instance and database in which the Analysis Services objects will be created.
<project name>.configsettings
Contains environment specific settings, such as data source connection information and object storage locations. Settings in this file override settings in the <project name>.asdatabase file.
<project name>.deploymentoptions
Contains deployment options, such as whether deployment is transactional and whether deployed objects should be processed after deployment.

3. XMLA Scripts
  1. XMLA scripts can be generated by using the Deployment Wizard that allows you to execute the script in SQL Server Management Studio, to recreate the database objects, which are defined within the script file.
  2. The XMLA script consists of settings that are used to create the Analysis Services objects. It also consists of the settings required to process the Analysis Services database and the objects found in the script.
  3. You can edit the XMLA script to add custom object through the XMLA language. This can be performed in any editor.
  4.  However, SQL Server Management Studio is a useful platform for making such changes, because the script is colour-coded and can be executed from within it to create the objects.
  5. After you have the XMLA script in a saved file, you can easily run the script according to a schedule, or embed the script in an application that connects directly to an instance of Analysis Services. 
  6. Furthermore, after running the script and deploying the database, the newly created database must be processed before users can browse it.
  7. The XMLA script does not contain a password. If a password is specified in either the connection string for a data source or for impersonation purposes, you must add the password manually to the script before it executes or add it after the script executes.
4.Synchronize Database Wizard
  1. The Synchronize Database Wizard helps you to synchronize an Analysis Services database to another instance of SQL Server.
  2. This synchronization is one-way and cannot be performed on the same instance of Analysis Services.
  3. While the wizard synchronizes the data between the two databases, users can continue to query the destination database. 
  4. After synchronization finishes, Analysis Services automatically switches the users to the newly copied data and metadata, and drops the old data from the destination database.
5.Backup and Restore
  1. Analysis Services contains its own backup and restore utility.
  2. Using this method, you can back up an Analysis Services database and restore the database to a separate instance of Analysis Services.
6.Analysis Management Objects (AMO)
  1. AMO provides a programmatic interface to the complete command set for Analysis Services available to the developer. 
  2. Therefore, AMO can be used for deployment and also for the many administrative commands it otherwise supports.




Copyright © 2014 20Fingers2Brains. All rights reserved. New Super Seo modified by CB Blogger. Powered by Blogger