Ssas Essentials

SQL Server 2005 Analysis Services Essentials 1. What is SSAS? Microsoft SQL Server 2005 Analysis Services (SSAS) delivers online analytical processing (OLAP) and data mining functionality for business intelligence applications. Analysis Services supports OLAP by letting you design, create, and manage multidimensional structures that contain data aggregated from other data sources, such as relational databases. For data mining applications, Analysis Services lets you design, create, and visualize data mining models that are constructed from other data sources by using a wide variety of industry-standard data mining algorithms.

2. Unified data Model: The role of a Unified Dimensional Model (UDM) is to provide a bridge between the user and the data sources. A UDM is constructed over one or more physical data sources, and then the end user issues queries against the UDM using one of a variety of client tools, such as Microsoft Excel. At a minimum, when the UDM is constructed merely as a thin layer over the data source, the advantages to the end user are a simpler, more readily understood model of the data, isolation from heterogeneous backend data sources, and improved performance for summary type queries. In some scenarios a simple UDM like this is constructed totally automatically. With greater investment in the construction of the UDM, additional benefits accrue from the richness of metadata that the model can provide. The UDM provides the following benefits: •

Allows the user model to be greatly enriched.

•

Provides high performance queries supporting interactive analysis, even over huge data volumes.

•

Allows business rules to be captured in the model to support richer analysis.

•

Supports ‘closing the loop’, where the user acts upon the data they see.

3. Data source: A data source in Microsoft SQL Server 2005 Analysis Services (SSAS) represents a connection to a data source and contains the connection string that defines how Analysis Services will connect to a physical data store using a managed Microsoft .NET Framework or native OLE DB provider. The connection string contains server name, database, security, timeout, and other connection related information. You manage access to information stored in a data source by using roles, and the granularity of such access is limited. Analysis Services stores the metadata for a data source in an XML file, in Analysis Services Scripting Language (ASSL) format.

4. Data Source Views: A data source view is the metadata definition of the schema elements used by the UDM and by mining structures stored in an XML format. A data source view contains the logical model of the schema used by database objects—namely cubes, dimensions, and mining structures. A data source view: • Contains the metadata that represents selected objects from one or more underlying data sources, or the metadata that will be used to generate an underlying relational data store •

Can be built over one or more data sources, allowing a cube and dimension to be defined that integrate data from multiple sources

•

Can contain relationships, calculated columns, and queries that are not present in the underlying data source and which exist separate from the underlying data sources

• Is not visible to client applications 5. Cube: A cube is defined by its measures and dimensions. The measures and dimensions in a cube are derived from tables and views in the data source view on which the cube is based. A cube consists of measures based on one or more fact tables and dimensions based on one or more dimension tables. Dimensions are based on attributes, which are mapped to one or columns in the dimension tables

or views in the data source view, and then hierarchies are defined from these attributes. 6. Types of relationships that can be created in the data source view designer •

A relationship from one table to another table in the same data source.

•

A relationship from one table to itself, as in a parent-child relationship.

•

A relationship from one table in a data source to another table in a different data source.

7. What is a named calculation? You can use the Create Named Calculation dialog box in Data Source View Designer to add a named calculation to a table in a data source view. A named calculation is a SQL expression represented as a calculated column. This expression appears and behaves as a column in the table. A named calculation lets you extend the relational schema of existing tables in a data source view without modifying the table in the underlying data source.

8. What is a named query? A named query is a SQL expression represented as a table. In a named query, you can specify an SQL expression to select rows and columns returned from one or more tables in one or more data sources. A named query is like any other table in a data source view with rows and relationships, except that the named query is based on an expression. The expression appears and behaves as a table in the data source view. When you create a named query, you specify a name, the SQL query returning the columns and data for the table, and optionally, a description of the named query. The SQL expression can refer to other tables in the data source view. named query lets you extend the relational schema of existing tables in a data source view without modifying the underlying data source. For example, a series of named queries can be used to split up a complex dimension table into smaller, simpler dimension tables for use in database dimensions. 9. What are the roles of a 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. 10.What are the roles in SSAS? •

The server role, a fixed role that provides administrator access to an instance of Analysis Services.

•

Database roles, roles defined by administrators to control access to objects and data for non-administrator users.

Server role: The Analysis Services server role defines administrative access of Windows users and groups to an instance of Analysis Services. Members of this role have access to all Analysis Services databases and objects on an instance of Analysis Services, and can perform the following tasks: •

Perform server-level administrative functions using SQL Server Management Studio or Business Intelligence Development Studio, including creating databases and setting server-level properties.

•

Perform administrative functions programmatically with Analysis Management Objects (AMO).

•

Maintain Analysis Services database roles.

•

Start traces (other than for processing events, which can be performed by a database role with Process access).

Database role: An Analysis Services database role defines user access to objects and data in an Analysis Services database. A database role is created as a separate object in an Analysis Services database, and applies only to the database in which that role is created. Windows users and groups are included in the role by an administrator, who also defines permissions within the role. The permissions allow members to access and administer the database, in addition to the objects and data within the database. Each permission has one or more access rights associated with it, which in turn give the permission finer control over access to a particular object in the database. For more information about the permissions and access rights available to database roles.

10.Explain the SSAS architecture? Microsoft SQL Server 2005 Analysis Services (SSAS) uses both server and client components to supply online analytical processing (OLAP) and data mining functionality for business intelligence applications: •

The server component of Analysis Services is implemented as a Microsoft Windows service. SQL Server 2005 Analysis Services supports multiple instances on the same computer, with each instance of Analysis Services implemented as a separate instance of the Windows service.

•

Clients communicate with Analysis Services using the public standard XMLfor Analysis (XMLA), a SOAP-based protocol for issuing commands and receiving responses, exposed as a web service. In addition, client object models are provided over XMLA, including both a managed provider (ADOMD.Net) and a native OLE DB provider.

Query commands can be issued using: SQL; Multidimensional Expressions (MDX), an industry standard query language orientated towards analysis; or Data Mining Extensions (DMX), an industry standard query language oriented toward data mining. Analysis Services Scripting Language (ASSL) can also be used to manage Analysis Services database objects.

Server Architecture: The server component of Analysis Services is the msmdsvr.exe application, which normally runs as a Windows service. This application consists of an XML/A listener component and numerous internal components that perform the following functions: •

Parse statements received from clients

•

Management meta data

•

Handle transactions

•

Process calculations

•

Store dimension and cell data

•

Create aggregations

•

Schedule queries

•

Caching objects

•

Manage server resources

The XMLA listener component handles all XMLA communications between Analysis Services and its clients. The Analysis Services Port configuration setting can be used to specify a port on which an Analysis Services instance listens. Unless otherwise specified, Analysis Services uses the following default TCP ports: Port Description 2725 Existing Microsoft SQL Server 2000 Analysis Services instances. 2383 Microsoft SQL Server 2005 Analysis Services default instance. 2382 Redirector for other Microsoft SQL Server 2005 Analysis Services instances. Client Architecture: 11.What are role-playing dimensions? A role-playing dimension when there are multiple columns in a fact table that each have foreign keys to the same dimension table. 12. What is a linked dimension in SSAS? A linked dimension is based on a dimension that is stored in a separate Analysis Services Database which may or may not be on the same server. You can create and maintain a dimension in just one database and then reuse that dimension by creating linked dimensions for use in multiple databases. Users will not notice any difference between a regular dimension and a linked dimension. As a developer, you will be able to browse the members of a linked dimension in the Dimension Designer, but you can only modify the dimension within its original Analysis Services database.

13.What is a server time dimension?

A server dimension in SSAS is a dimension which does not come from a dimension table in your data warehouse, but is generated by Analysis Services and stored in a proprietary file structure on the server. You simply specify the beginning date and end date of the dimension, select the time periods to include such as year, quarter, month, or date, and choose the special calendars, if any, to add to the dimension. When you create a Server Time dimension, no such table is created. The data available to this dimension type is maintained solely by Analysis Services. To use this dimension with a fact table, you will need to have a date/time column instead of a dimension key in the fact table. Analysis Services will use the date in this column to join the fact table with the Server Time dimension. 14.What is a semi additive measure? A semi additive measure is different from the additive measures because it can be summed along some, but not all, dimensions in a cube. A common use of a semi additive measure is a value for a fixed point in time (also known as a snapshot), such as inventory quantity. Inventory quantity can be summed up along the Product dimension to find out how many bikes are in stock on a given day, but you can’t sum this measure across the Time dimension. For example, the number of bikes you had in stock at the end of the first quarter can’t be added to the number of bikes you had in stock at the end of the second quarter (at least, not if you want the correct result!).

15.What is a partition? A partition in Analysis Services is the physical location of stored cube data. Every cube has at least one partition by default. Each time you create a measure group, another partition is created. 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. Queries can run even faster when the partition also stores aggregations, the pre calculated totals for additive measures. Partitions are a powerful and flexible means of managing

cubes, especially large cubes.

16.What is the Auto build feature? Auto Build is a feature that reviews the database metadata and structure to makes recommendations about how to structure a cube or a dimension. When using the Dimension Wizard with Auto Build enabled, you have the option for the wizard to suggest attributes only or to suggest both attributes and user hierarchies. Similarly, the Cube Wizard can examine the DSV, and identify the fact and dimension tables and suggest new dimensions.

17.What is Multi Dimensional Expressions (MDX)? Multidimensional Expressions (MDX) is a fully-featured, statement-based scripting language used to define, work with, and retrieve data from

multidimensional objects in Microsoft SQL Server 2005 Analysis Services (SSAS). MDX provides the following language features: •

Data definition language (DDL) statements used to create, drop, and work with multidimensional objects.

•

Data manipulation language (DML) statements for retrieving manipulating data from multidimensional objects.

•

Scripting language statements for managing scope, context, and control of flow within MDX scripts.

•

A robust set of operators and functions for the manipulation of data retrieved from multidimensional objects.

•

The ability to extend MDX with user-defined functions.

18.Examples of a basic MDX select statement? SELECT { [Measures].[Sales Amount], [Measures].[Tax Amount] } ON COLUMNS, { [Date].[Fiscal Time].[Fiscal Year].&[2002], [Date].[Fiscal Time].[Fiscal Year].&[2003] } ON ROWS FROM [Adventure Works] WHERE ( [Sales Territory].[Southwest] )

19.What are perspectives? Cubes can be very complex objects for users to navigate in Microsoft SQL Server 2005 Analysis Services. A single cube can represent the contents of an entire data warehouse, with multiple measure groups in a cube representing multiple fact tables and multiple dimensions based on multiple dimension tables. This can result in a very complex and powerful cube, but the prospect can be daunting to users who often only need to interact with a small portion of a cube in order to satisfy their business intelligence and reporting requirements.In Microsoft SQL Server 2005 Analysis Services (SSAS), you can use a perspective to reduce the perceived complexity of a cube in Analysis Services by defining viewable subsets of the cube that provide focused, business-specific or application-specific viewpoints on a cube. The perspective controls the visibility of objects contained by a cube, including measure groups, measures, dimensions, hierarchies, attributes, KPIs, actions, and calculations, to a business intelligence application.

20.What is metadata? Information about how the data is stored and structured, and perhaps more importantly, what the data means, is called metadata. For example, when you create an OLAP cube, you define not only what the measures are, but also how they should be aggregated, what the caption should be, and even how the number should best be formatted. Likewise, in an OLAP cube, when you create a

dimension with many attributes, you define which attributes are group able, and whether any of the group able attributes should be linked together into a hierarchy. 21.What is a measure group? A measure group is simply the group of measures that share the same grain. When you go to build your data warehouse, you would create a separate fact table for each measure group. Conversely, if you already have a data warehouse with several fact tables, you simply create a measure group for each fact table. 22.What does processing in Analysis service means? Processing is the step or series of steps that turn data into information for business analysis. Processing is different depending on the type of object, but processing is always part of turning data into information. Before you can process a local object or project, you must deploy it by sending it to the server. During deployment, a script is sent to the Analysis Services instance to re-create the project and automatically run it. By default, deployment includes processing all of the objects in the database. The Analysis Services objects that you have to keep up-to-date through processing are measure groups, partitions, dimensions, cubes, mining models, mining structures, and databases. When an object contains one or more objects, processing the highest-level object causes a cascade of processing all the lower-level objects. For example, a cube typically contains dimensions and measure groups, each of which contains partitions. Processing any cube causes processing of all the constituent dimensions and measure groups within the cube. 23.What are the three dimension that can be created using SSAS? • • •

Standard dimension Time dimension Parent-child dimension

24. What is a ragged hierarchy? Under normal circumstances, each level in a hierarchy in Microsoft SQL Server 2005 Analysis Services (SSAS) has the same number of members above it as any other member at the same level. In a ragged hierarchy, the logical parent member of at least one member is not in the level immediately above the member. When this occurs, the hierarchy descends to different levels for different drilldown paths. Expanding through every level for every drilldown path is then unnecessarily complicated. 25.What are the two ways we can use MDX? MDX is used in two different ways within Analysis Services. First, it is a query language —the tool for retrieving reports from an OLAP cube. In other words, MDX is the tool used by client applications to retrieve values. Second, MDX is an expression language— the tool used to calculate single values. For example, MDX is what you use to add

calculations to a cube. values. For example, MDX is what you use to add calculations to a cube. 26.What is the difference between a derived measure and a calculated measure? The difference between a derived measure and a calculated measure is when the calculation is performed. A derived measure is calculated before aggregations are created, and the values of the derived measure are stored in the cube. A calculated measure is calculated after aggregations are created, and the values of a calculated measure aren’t stored in the cube. The primary criterion for choosing between a derived measure and a calculated measure is not efficiency, but accuracy. 27. While creating a new calculated member called non-empty behavior?

in a cube what is the use of property

Nonempty behavior is an important property for ratio calculations. If the denominator Is empty, an MDX expression will return an error just as it would if the denominator Were equal to zero. By selecting one or more measures for the Non-Empty Behavior property, you are establishing a requirement that each selected measure first be evaluated before the calculation expression is evaluated. If each selected measure is empty, then The expression is also treated as empty and no error is returned.