-
When roles are assigned for cubes the user has access or no access to the cube. When roles are assigned to a dimension, the user gains only partial access to parts of the cube. The simplest method of restricting access to a dimension is to prevent access to all levels except the top level of the dimension. In the Cube role dialog box all users were given complete access to the sales cube. Let us restrict the access of users by permitting them to see only values broken out by fiscal years. In the console tree right click the sales...
-
Cubes are made up of a fact table and several dimensions. Optimizing cube processing therefore, involves optimizing dimensions also. Let us look at two scenarios. One in which the dimensions are not optimized and the cube is processed and another in which the dimensions are optimized and the cube is processed.
MSAS
Microsoft Analysis Services is a collection of Online Analytical Processing (OLAP) and Data Mining services supplied in Microsoft SQL Server. ‘Data warehouse’ and ‘OLAP’ are often used interchangeably to describe two different elements of a decision support system.
Advances in data storage, transmission, database management tools and computerizing business processes allow for constant technological development in data manipulation and analysis. Analysis Services provides managers the possibility to explore a cache of collected and current data, define business trends and patterns and mine data to make discerning business decisions.
An administrator operating the Microsoft SQL Server Analysis Services section of a data warehouse will face operational issues in the field. Analysis Services and its appropriately configured environment is applied and utilized in all aspects, from the development to the production environment. An Analysis Services administrator managing databases will have to operate and maintain Analysis Services within a preexisting IT and database infrastructure. Change control, operational issues, problem resolution, automation and tracking techniques will need to be understood and employed, in order to test for changes to an existing environment and database structure. An Analysis Systems administrator must actively anticipate capacity issues, problem solve and ensure availability of Analysis Services cubes.
Data Warehouse Interview Questions
Abinitio Interview Questions
MSAS Interview Questions
Data Warehousing Basic Questions
BO Designer Interview Questions
Business Intelligence Interview Questions
Business Objects Interview Questions
Cognos Interview Questions
Data Warehousing Concepts
Data Integration Interview Questions
DataStage Interview Questions
ETL Interview Questions
Impromptu Interview Questions
Informatica Interview Questions
MicroStrategy Interview Questions
Reportnet Interview Questions
Data Warehouse FAQ’s
Abinitio Faqs
Informatica Faqs
Data Warehousing FAQs
DataStage Faqs
Microsoft Analysis Services Training We are glad to let you know that we are starting with Microsoft Analysis Services Training in the following topics. We will be publishing the tutorials as we complete. Please use discussion board if you have any questions and would like to discuss any. We will be providing step by step screen shots and the sample code. 1: Introduction to Data Warehousing and OLAP Introducing Data Warehousing Defining OLAP Solutions Understanding Data Warehouse Design Understanding OLAP Models Applying OLAP Cubes 2: Introducing Analysis Manager Wizards Defining Terms Previewing Analysis Manager Preparing to Create a…
February 25, 2005
-
- Comments:
This tutorial starts with the introduction to Data Warehousing, Definition of OLAP, difference between Data warehouse and the OLTP Database, Objectives of data warehousing and data flow. Computerization of business processes; technological advances in transmission and storage of data; and powerful database management tools have opened up new possibilities of data manipulation and analysis. Business managers are eager to explore the repositories of current and historical data to identify trends and patterns in the wrap and hoof of business. They hope to mine data and use them for taking intelligent business decisions. In this context, industries are increasingly focusing on…
February 25, 2005
-
- Comments:
In this tutorial we will learn about the differences between Data Warehouse database and OLTP database and the objectives of a Data warehouse and Data flow. The data warehouse and the OLTP data base are both relational databases. However, the objectives of both these databases are different. The OLTP database records transactions in real time and aims to automate clerical data entry processes of a business entity. Addition, modification and deletion of data in the OLTP database is essential and the semantics of the application used in the front end impact on the organization of the data in the database….
February 25, 2005
-
- Comments:
This tutorial covers OLAP solutions used by Data warehouses and understanding Data Warehouse design. The enterprise needs to ask itself certain fundamental questions before actually launching on the process of designing the data warehouse. It must begin with a conviction that a data warehouse would really help its business and the return on investment will make it worth it.Defining OLAP Solutions The data warehouse offloads data from a multitude of sources. The cleaned, validated and loaded data is voluminous and daunting. This data needs to be organized, categorized and arranged in meaningful order for analytical purposes. OLAP solutions are specifically…
February 27, 2005
-
- Comments:
This tutorial covers the basic design concepts, The top down approach, The Bottom-Up Approach , Hybrid Approach and Federated approach.Ralph Kimball and Inmon, the co-founders of the data warehouse, significantly had their own differences in the design and architecture of the data warehouse. Inmon advocated a “dependent data mart structure” whereas Kimball advocated the “data warehouse bus structure”. The Dependent Data Mart structure:The top down approachBill Inmon saw a need to transfer data from diverse OLTP systems into a centralized place where the data could be used for analysis. He insisted that data should be organized into subject oriented, integrated,…
February 28, 2005
-
- Comments:
This tutorial covers Designing the Dimensional Model, Dimensional Model schemas like Star Schema, Snowflake Schema, Optimizing star schema and Design of the Relational Database, OLAP Cubes and Data mining tools, Security considerations, metadata and backup and recovery plans.Preparing the data for OLAP The global issues out of the way, the enterprise must begin to focus on the granular design issues. The data in the data warehouse must be prepared for the application of Online Analytical Processing (OLAP) solutions and such preparations will be driven by the business needs of the enterprise. Designing the Dimensional Model User requirements and data realities…
March 1, 2005
-
- Comments:
This tutorial covers the different types of OLAP models like Relational Online Analytical Processing mode( ROLAP), Multidimensional Online Analytical processing mode(MOLAP) and Hybrid Online Analytical Processing mode or HOLAP. Cubes in a data warehouse are stored in three different modes. A relational storage model is called Relational Online Analytical Processing mode or ROLAP, while a Multidimensional Online Analytical processing mode is called MOLAP. When dimensions are stored in a combination of the two modes then it is known as Hybrid Online Analytical Processing mode or HOLAP.MOLAPThis is the traditional mode in OLAP analysis. In MOLAP data is stored in form…
March 2, 2005
-
- Comments:
This tutorial covers the types of changes that impact on cubes and Synchronization OLAP and data warehouse data. Multidimensional cubes are created out of the data stored in the data warehouse. Numerical measures are also summarized into pre-aggregated values when cubes are constructed. These cubes are then stored in multidimensional structures that are designed for rapid query. The pre-aggregated information is combined with raw fact data to respond to a wide variety of queries. Types of changes that impact on cubesSince cubes contain summarized data from the data warehouse, any changes to the data in the warehouse also affects the…
March 3, 2005
-
- Comments:
This tutorial covers the download and step by step installation instructions along with the screen shots which helps with your PC setup to contine learning MSAS. Download or order the SQL Server 2000 120-day Trial Software. SQL Server is a complete database and data analysis package that opens the door to a new generation of enterprise-class business applications. SQL Server CE is the compact database for rapidly developing applications that extends enterprise data management to mobile and embedded devices. This fully functional trial software will be automatically turned off after four months. You can obtain the fully purchased version at any…
March 5, 2005
-
- Comments:
This tutorial covers brief introduction to Analysis Manager Wizards, how to start, Previewing and Defining terms which helps to understand the navigation along with the screen shots.Introducing Analysis Manager WizardsThe Analysis Manager is a console application in Microsoft SQL Server 2000. It provides an interface for accessing Analysis servers and their metadata repositories. The Analysis manager console can be used to administer servers, create databases, cubes, data mining objects, or for specifying storage options and optimizing query performance. The console also allows the user browse data sources, shared dimensions and security roles. It snaps into the Microsoft Management console, which…
This tutorial explains the steps to create the cube and the preparation like settip the datasource.Preparing to Create the CubeA cube is the fundamental unit of data storage and retrieval in the Analysis Server. A cube can be created with either the Cube Wizard or the Cube Editor. The cube wizard provides very limited options as against the options available in the Cube Editor. In this section of the tutorial we propose to concentrate on the Cube Wizard and learn how to create a cube, process a cube and browse the results using the Wizard.Setting up the Data source NameEvery…
This tutorial explains Setting up the Database in Analysis Server and how to connect and test the connectivity.Setting up the Database in Analysis ServerThe first step in creating a cube is the setting up of the Database. Double click on the server and connect to it. Once the connection is established, the user can proceed to create the database. Right click on the server node and select ‘New Database’. On clicking on “New Database” a tiny window opens prompting the user to enter the name of the database and a description of it. The database has been named Sample and…
This three part tutorial explains about cubes, Dimensions and how to create them using the wizards. You need to understand and learn how to create the cubes since it’s very critical step.Tutorial 12: MSAS : Building the Cube Part #1A cube is a multidimensional structure consisting of dimensions and measures. Therefore, we need to know what dimensions we intend to use and what measures will be captured. In short, we need to know what quantitative values we want to analyze or monitor from our database. Usually organizations capture actual measures such as revenues, sales in context of expenses, costs along…
This is part 2 in MSAS : Building the Cube series, If you have not completed reading part1, please do so before continuing with this tutorial.Tutorial 13: MSAS : Building the Cube Part #2Click ‘New Dimension…’ button to launch the Dimension Wizard. The welcome dialog box of the Dimension wizard appears. The Check box option is to be checked if we want to skip this screen in future. For the present we leave it as it is and click the Next button.The next requirement is to select the kind of schema we want for our cube. On clicking Next we are taken…
This is part 3 in MSAS : Building the Cube series, If you have not completed reading part 1 and 2 , please do so before continuing with this tutorial. Tutorial 14: MSAS : Building the Cube Part #3The next dimension that we need to create is the Time dimension. This is the most common dimension and inevitably forms a dimension of every cube. However, there are certain specific conditions that arise when we create the time dimension. In the Select Dimensions dialog box of the Cube Wizard we will chose time_by_day dimension table. The time dimension table is usually…
This tutorial covers the storage modes for the cube before it can be processed. The kind of storage selected will determine the query performance and enhance the cube efficiency.Tutorial 15: MSAS : Designing Storage and Processing the CubeWe have already discussed in detail the type of storage options that are available in OLAP. Analysis Services offers the end user all the different storage options of the OLAP. Multidimensional OLAP (MOLAP), Relational OLAP(ROLAP) and Hybrid OLAP(HOLAP) are available in Microsoft Analysis services. Now that the design of the cube has been completed the user will have to designate one of the…
This tutorial explains about the Cube Browser, it’s a tool provided within the Analysis Services to display the results of the cube process without the need to add external software.Tutorial 16: MSAS : Browsing the CubeThe processed cube can now be analyzed. For the purpose of this tutorial we will use ‘slice and dice’ and drill down capabilities of the Cube Browser to browse the cube. The Cube Browser is a tool provided within the Analysis Services to display the results of the cube process without the need to add external software. It is a relatively useful data analysis tool that can…
This tutorial explains various functions available and the tools available for building and managing data warehouses. MSAS : Microsoft Data Warehousing OverviewModern day enterprises believe that mission critical decisions should be information based. Vast information repositories and historical data available with them need to be analyzed and emerging patterns examined before any decisions are taken. Data warehousing and business intelligence solutions were looked upon as means of achieving this purpose. This need triggered off a proliferation of data warehousing and business intelligence solutions in the market. Initially these solutions were scattered, disparate and focused on specific areas of Data warehousing such…
The Data warehousing framework is a set of components and API’s that implement the data warehousing features of the SQL server 2000. The common interface of the server known as the Enterprise Manager can be used by various components to build and use the data warehouse or a data mart.The Data warehousing framework of SQL Server 2000The following illustration details the Microsoft SQL Server 2000 data warehousing overview.The relational database engine of the SQL server is a modern, highly scalable and reliable engine for storing data. The database stores data in tables and each table represents objects that are of…
This is part 2 of MSAS : The Data warehousing framework of SQL Server 2000. It’s very important that you understand the concepts if you are really trying to get job in Data Warehousing field.MSAS : The Data warehousing framework of SQL Server 2000 – Part 2The Online Analytical Processing (OLAP) tools offered by Microsoft are impressive, considering the fact that Microsoft entered the OLAP market only in 1998. A review of the growth of Microsoft tools in this area, clearly indicates that they have grown from being a mere entrant to being a market leader in the field. What…
Analysis services is the middle tier server component of the MS SQL 2000. It manages multidimensional OLAP cubes of data and ease of access to such data to end users. It also enables user to create data mining models from the data in the cubes or in the relational databases. The Analysis Manager and Analysis ServerThe Analysis Manager: The MS SQL Server 2000 Analysis services, includes the Analysis manager, which is a console application. This application provides user interface for accessing the Analysis servers and the meta data repositories associated with them. The Analysis Manager is represented as a folder…
The primary aim of Microsoft was to ease the process of building and using data warehouses. A large number of wizards, editors and tools have been built into the Analysis services to fulfill this objective. The wizards available for use are: The Cube wizard that helps the user build all the structures necessary to create an OLAP cube. It walks the user through the entire cube design and implementation process. The user can map the data sources, create dimensions and define measures using this wizard. The Cube Editor is useful in editing cube structures or in creating new cubes. The…
Analysis services provides various tools that can be programmatically used to extend its functionality. Analysis services supports OLE DB to meet the OLAP specific requirements and is also designed to meet the requirements for Data mining specifications. The Analysis services is made compatible with the ActiveX Data Objects(ADO) and its multidimensional counterpart ADO MD. User defined functions for creating Component Object Model(COM) libraries enable the Analysis services to extend its capabilities of using automation languages such as Microsoft Visual Basic or Microsoft Visual C++. These libraries can be registered and the functions can be used in calculated member definitions and…
Metadata is a description of data. It provides a consistent way to describe data structures. It is used to describe data as it is being transformed and gives a clear explanation of the meaning of different fields, measures, levels and dimensions. All releases of Microsoft development and data management tools support and allow access to the Repository. The storage of data about data in a central location has some advantages.Reusability: With the storage at a central location the data can be used and reused. Dependency tracking: the repository stores information about different objects and the relationships between stored objects. Hence…
In the first tutorial of this series “Introduction to Data Warehousing and OLAP” we briefly touched upon storage options that are used in data warehousing. In the second chapter “ Introducing Analysis Manager Wizards” we learnt how to use the cube Storage wizard to set storage options for the cube we had created. In this section of the tutorial we will be going into a little more detail on the various kinds of storage options available in Analysis Services and will be touching on the pros and cons of the different storage modes. To recapitulate, a cube is a multidimensional…
The PivotTable Service:The interfaces used by client applications to access OLAP data and data mining data on the server are provided by the PivotTable Service. The PivotTable service is a set of tools that enable the transfer of OLAP cubes to client applications from the OLAP server. Two programming interfaces for querying data are available to the developer from the PivotTable Services:–The OLE DB for OLAP and the ActiveX Data objects Multidimensional (ADO MD). Tools such as those available in VisualStudio.Net are harnessed to create applications that query multi dimensional data sources. The figure below illustrates the relationship scenario.Using the…
PivotTable component in Office 2000 is the PivotTable report feature. It allows the user select and cross tabulate numerical values in ways that are similar to the processes of a cube. The earlier versions of this report in Excel could only extract values from relational data sources. The version under reference can present data directly form an OLAP cube.There is another PivotTable in Office 2000 which is a PivotTable list feature. This is a part of the Office Web Components. This component has the same functionalities as the PivotTable report, but includes capabilities that aren’t available in Excel component. It…
The next 6 tutorials explains Building Dimensions using Dimensions Editor. Dimensional modeling is the conceptualization and visualization of numerical data models as a set of measures that are derived from the common parameters used in a business. It summarizes and rearranges data and presents views of data to support data analysis. Dimensional modeling focuses on data such as counts, weights, balances and occurrences. Understanding Dimensions BasicsDimensional modeling is the conceptualization and visualization of numerical data models as a set of measures that are derived from the common parameters used in a business. It summarizes and rearranges data and presents views…
This tutorial explains how to create Shared dimension using Dimension Editor , Private dimension using Dimension Editor and the differences between them.Creating Shared Dimension using Dimension Editor 1. In the Analysis Manager tree pane, expand the database in which you want to create the shared dimension.2. Right-click the Shared Dimensions folder, point to New Dimension, and then click Editor3. In the Choose a Dimension Table dialog box, expand a data source, click the dimension table for the dimension, and then click OK.Dimension Editor appears with the dimension table showing in the Schema tab.4. To add more tables to the dimension,…
Dimensions are created, based on dimension table columns, member properties, or from the structure of OLAP data mining models. When a dimension is defined, there are a number of possible approaches. Each approach produces a different dimension variety. Standard Dimensions are regular dimensions. They can be of two types. The standard star schema dimension and the standard snowflake dimension.Within the Dimension editor a new standard dimension can be created in two ways:-Using the Dimension Wizard or multiple dimension tablesOpen the Dimension editor and select <new> from the dimension drop down list box on the tool bar. The Editor displays the…
A level is an element of a dimension hierarchy that describes the hierarchy from the highest level to the lowest level of data. Levels exist within dimensions and are based on columns in the dimension table or member properties in the dimension. They specify the contents and structure of the dimension’s hierarchy and determine the members that are included in the hierarchy and their positions relative to one another within the hierarchy.Levels get created when a dimension is created using the Dimension Wizard, Editor or the Cube Editor. The levels can then be maintained and their properties can be set….
Parent child dimensions when viewed from within a cube reveal some interesting features. We will add an private employee parent child dimension to the sales cube and study the features thereof.In the cube Editor right click the Dimensions folder and click New Dimension to launch the Dimension wizardSelect the parent child option and click Next. Select the Employee dimension table and click Next. Select the Employee_ID in the member key list, Supervisor_ID in the Parent Key List, and Full_Name in the Member Name list. Then click Next. Click Next to skip the Advanced options screen. Type Employee as the dimension…
Dimensions are defined as structural attributes of a cube made up of levels arranged in hierarchies. A level is a set of members of a dimension organized such that all members of the set are at an equal distance from the root of the hierarchy. A hierarchy is the set of members in a dimension and their positions relative to one another.Working with Levels and hierarchiesThe basic facts relating to levels was discussed in “Building Dimensions using Dimension Editor”. In this tutorial we will go a little deeper into how to work with levels and hierarchies. Before proceeding with the…
Dimensions are stored in the Multidimensional OLAP or Relational OLAP. The storage mode determines the location and form of the dimensions data. While MOLAP stores data in a multidimensional structure on the Analysis server, ROLAP stores the data in the relational tables. The storage mode can be set using the Dimension or cube editor.When a Dimension’s storage mode is set to ROLAP the following settings have to be made to the levels in the dimension. The lowest level’s Member Keys Unique property is to be set to TRUE.The dimension must not contain member groups.If the dimension is a private one,…
A hierarchy defines the relative positions of members in a dimension. Hierarchies are sometimes represented as pyramidal structures. The members in this structure are arranged in an expansive order—from the most summarized to the most detailed. For instance in a geography dimension the country may the most summarized and the individual cities and localities may be the most detailed members of the hierarchy.Analysis Services supports three kinds of hierarchies. Balanced, unbalanced and ragged. A balanced hierarchy is a hierarchy in which all branches descend to the same level and each member’s logical parent is the level immediately above the member….
Time dimensions are part and parcel of OLAP cubes. At the lowest level of detail a time dimension may contain a month, minute or even a second. At the most summarized level it may contain a year, a decade or a century. The repetitive nature of time encourages users to view data in terms of a time dimension. How much sales of x product occurred during the month of March or April in the year 2000 compared to the year 2001? This would be a query on a sales cube with a time dimension.A frequent issue that arises while dealing…
To enable proper aggregation of values along a dimension each member of the dimension needs its own aggregation rule. These rules are provided by custom roll up. Custom rollup operators provide a simple way of controlling the process of rolling up a member to its parents values. Custom rollup operators assigned to a column during the process of creating a dimension. The rollup then, uses the contents of the column as custom rollup operator for each member and is used to evaluate the value of the member’s parents.Custom rollups are enabled when the Unary Operators property of the level is…
Member properties are attributes associated with members. They contain some additional information about a member but cannot be used to create a level in the dimension by themselves. For example each member of the Month level has an associated Boolean number property called Bonus month.If records the bonus given during the month, if any. Since the frequency of bonus is irregular, all months may not have Bonus awarded during the month. This attribute of the month therefore may be 0 or 1 and provides additional information about the month. It does not qualify to be a level by itself. The…
A logical dimension created out of the columns of a physical dimension is a virtual dimension. The contents of a virtual dimension are member properties of the physical dimension or columns and tables of a physical dimension. For instance the Store name level of the Store dimension has a member property named Store Sqft. This member property identifies the area of the store in square feet. This member property can be used to create a virtual dimension and this can be added to any cube that contains the Store Dimension.The virtual dimension can be used like any other dimension by…
The main object in Online Analytical processing is the cube. A cube is defined as a subset of the data in the data warehouse, organized and summarized into multidimensional structure that consists of dimensions and measures. Introduction to Cubes The purpose of creating cubes is to provide an easy to use mechanism for data access and quick and uniform response time for queries. User interface controls are inbuilt into Analysis Services to enable end users to connect to the server for queries and manipulate the cube. Aggregations built into the cube at the time of creation of the cube, enable…
The quantitative and numerical columns from a fact table of a cube are the measures of the cube. When the cube is processed the data in the measures get aggregated across the dimensions of the cube. These measures are of primary interest to the end user and are the central values that get analyzed in a cube.Introduction to Measures The quantitative and numerical columns from a fact table of a cube are the measures of the cube. When the cube is processed the data in the measures get aggregated across the dimensions of the cube. These measures are of primary…
Each object in the properties pane of the Cube Editor has its own set of properties which can be used to modify the settings of the object. The properties of a cube defined in the properties pane are as tabulated below. In this section we shall use these properties to set the properties of our cubes. Aggregation Prefix: This is a prefix appended to aggregation name for the cube’s partitions, provided that the partition’s aggregation prefix begins with a plus sign (+). In this case, this property’s value is appended to the beginning of the partition’s aggregation prefix. If the…
Online Analytical Processing (OLAP) is essentially data presented as Cubes, dimensions, hierarchies and measures. Users can navigate a complex set of data intuitively using these objects. In this context, consistent response times for each view or slice of data become important. Therefore modes of storing and retrieving data became the key tenet of storage design. In the early days OLAP technology focused upon specialized, non-relational storage models as the only possible mode for OLAP. They called this technology Multidimensional OLAP(MOLAP). Later vendors discovered that the use of database structures(Star and snowflake schemas) helped in indexing, and storage of aggregates, and…
The Storage Design Wizard helps the user set the storage options and design the aggregations for a cube. While choosing from the three available storage modes the user has to keep certain factors in mind. ROLAP stores aggregations in a relational database and hence it does not make sense to select ROLAP as a storage mode for cubes if the aim is to have speed of performance in analysis. Aggregations in Relational databases are slow and bulky and it defeats the very purpose of creating the aggregations. However, if the user wants to look at the aggregations and understand how…
Pre-calculated summaries of data that improve query response time are called aggregations. OLAP technology organizes data into the multidimensional structures of cubes. The dimensions and their hierarchies define the queries that can be asked of the cubes.Cells store the value at the intersection of dimension coordinates. Whenever a query is made on the data, the results have to be fetched from the various locations of cells on the multidimensional structure, affecting response time. Aggregations consist of all the possible combinations of one level from each dimension in the cube. This makes query response time optimal. However storage and processing time…
This tutorial explains the usage of the Usage Based Optimization Wizard, The Server Cache , Query Usage and Manipulating the query log. Using the Usage Based Optimization Wizard. 1. When a set of queries have accumulated in the Usage log, the Usage based Optimization wizard can be pressed into service. 2. The task on hand is to select the Logged queries that should be applied and then design aggregations based on them 3. Let us assume that a user has browsed the sales cube and accumulated log entries by manipulating data. 4. Now Right click the Sales cube, click the Usage Based…
SQL Server 2000 Analysis services comes with certain features that optimize performance without the intervention of the Administrator. The Storage Engine is optimized by incorporating the following features;Record size and Data types should be kept as short as possible in the Fact tables and should only include fields for measures and indexed key columns. The Measure fields should use the smallest data type consistent with the measure data but the data type should be large enough to contain summarized values and prevent overflow when aggregations are calculated. Even saving two bytes per row can result in smaller fact tables in…
The SQL Server Query Analyzer is a feature rich Query Analyzer. The tool can provide a graphical representation of a query and helps the user mould queries into more efficient shapes. It also helps identify faulty queries. +To Tune the index created the user will have to open the SQL query analyzer and open a query or enter one. On the Query menu click on Index Tuning Wizard and follow the steps of the wizard. On the Specify workload screen select SQL Query Analyzer Selection and click Next. Select the tables and their indexes View the recommendations made by the…
The Analysis server is like a black box. Everything that happens in the server is recorded, but the end user sees a multidimensional cube created out of the data in the data warehouse and has very little taste of what really goes on behind the scenes. In this section we will be examining this black box to understand how the Analysis server processes Dimensions and cubes. We will also learn how to optimize cube processing and how to troubleshoot cube processing.Processing Dimensions: Introduction Dimensions are building blocks on which the cubes are constructed. When a dimension is processed the Analysis…
As stated earlier, maps are created when a dimension is processed. However, existing maps of a dimension are destroyed when it is processed and new maps are created. Consequently all cubes accessing the dimension will find it inaccessible and the cube will be invalid. The dimension will become accessible only when the cube is reprocessed.When cubes contain a large number of dimensions and some of the dimensions undergo a change, reprocessing a cube can become a daunting task. However, Analysis server has a solution to this problem. It is possible to Incremental update a dimension. This process does not destroy…
Cubes are made up of a fact table and several dimensions. Optimizing cube processing therefore, involves optimizing dimensions also. Let us look at two scenarios. One in which the dimensions are not optimized and the cube is processed and another in which the dimensions are optimized and the cube is processed.
Microsoft SQL Server 2000 Analysis Services provides the user with many ways of tracking and solving errors. A large number of error logs built into the service help the user pinpoint the exact nature of errors and find solutions for them. Microsoft Tech Net also provides the user online support to troubleshoot problems. This requires paid membership. FAQs of different kinds are available for the reference of users. SQL Server Books Online FAQ SQL Server Enterprise Manager FAQ Administration Tools FAQ Failover Clustering FAQ Multiple Instance FAQ Programming FAQ Replication FAQ Server FAQ Setup and Installation FAQ Upgrading to SQL…
The cube is a storage container that contains data and aggregations of data. Every cube must have at least one partition which must be stored in the Analysis server in which the cube is defined. Partitions are stored as separate files in the Partitions folder under the cube. If a cube contains multiple partitions, some of them can be stored in different physical locations. Partitions of a cube can also have different data sources. The aggregations of the data in the partitions can also be stored in different locations. The end user sees the cube as a single unit and…
Cubes with multiple partitions can be extremely confusing. Users may like to organize the cubes into a cube with fewer partitions by merging some of the partitions together. The preconditions to the merger of cubes is that the Storage and aggregation design should be the same in the partitions being merged. To merge partitions with different storage designs, the partitions being merged must be edited to have the same storage design. The cubes must then be reprocessed before the merger is attempted. We will merge the Sales 97 partition back into the sales. Right click the Sales 97 partition and…
In this tutorial we will be learning about Calculated members, Creating Calculated Members, Using Function Libraries to Build Calculated Members and Calculated Member Builder.Understanding Calculated members Calculated members are members of a dimension whose value is calculated at run time using Multidimensional expressions. The values of a calculated member may be derived from other member’s values. For instance Profit is determined by subtracting the value of member costs from value of member sales. This is unlike an input member whose value is directly loaded from the data base. The expressions for calculated members are defined when the member is defined. Calculated…
In this part 2 of Implementing Calculations Using MDX, we will be learning about Renaming Calculated members, Creating Non Measure Calculated members, Using Functions in Calculated Members, Setting calculations at Member level and Setting a Calculation for a subcube or a selection of cells.Renaming Calculated members 1. In the Analysis Manager tree pane, under the database that contains the calculated member, expand the Cubes folder. 2. Right-click the cube that contains the calculated member, and then click Edit. 3. In the Cube Editor tree pane, right-click the calculated member, and then click Rename. 4. In the box next to the calculated member icon,…
Before understanding what is solve order it is essential to understand the concept of Pass order. Solve order and pass order together determine the manner in which a cube is resolved in Analysis services.Pass order Every cube calculated as the result of a multidimensional Expression Query goes through a number of stages of calculations. Analysis server makes a complete pass of the calculations applicable for a stage and the stage is referred to as a calculation pass. The number of passes required to compute fully all the cells of a cube are referred to as the calculation pass depth of…
This tutorial explains about Defining Virtual cubes, Benefits of using virtual cubes, Working with Virtual Cubes and Obtaining logical results.Defining Virtual cubes Virtual Cubes can be defined as a combination of multiple cubes into one logical cube. Virtual cubes resemble relational database views in so far as they combine other views and tables. A virtual cube is created by selecting measures and dimensions from a consolidated set of dimensions and measures underlying component cubes. End users will see the virtual cube as a single cube. Virtual cubes can also be created out a single cube with an aim to expose…
In the above section we studied some of the reasons why we would want to create virtual cubes and how logical results can be obtained by making such cubes. We also saw that a virtual cube can be built quickly and easily by using the Virtual Cube Wizard.Building a Virtual Cube The Wizard takes the user through a series of steps that prompt him to specify the cubes, measures and dimensions to be used for building the virtual cube. The virtual cube then creates a combined view of the selected cubes by including the measures and dimensions selected. Please note…
Calculated members can be imported into virtual cubes from their component cubes. New calculated members also can be created directly into virtual cubes. The Calculated member Builder can be used to build the members or calculated members created in component cubes can be imported and then the same can be edited. The virtual cube editor is used to maintain the calculated members in virtual cubes. The Virtual cube Editor provides a single click access to Calculated Member Builder and the Import Calculated Members dialog box, which can be used to import calculated members into the virtual cube. Creating a Calculated…
This tutorial explains about defining Auctions, Creating Auctions, Creating Actions in Regular Cubes, Creating and Maintaining Actions in Virtual Cubes, Importing an Action into a Virtual Cube and Editing an Action in a Virtual Cube.Defining Actions End users can define certain operations to be performed or cubes or portions of a cube. These user defined operations are called Actions. The end user can use an action as a parameter for starting an application or for retrieving information. He can go beyond traditional analysis and initiate solutions to discovered problems and deficiencies by using actions. It enables them to send data…
Actions used by end users to select a single cube cell and retrieve a result set from the source data of the cell are known as Drillthrough actions. Drillthrough has to be enabled if administrators want to drillthrough in a Cube Browser and the Cube Editor data tab. The DrillThrough operations are extremely useful when the user wants to look at the underlying causes for some abnormally high or low outputs in specific fields. For example, let us say a store is doing extremely well and the sales in that store is showing increase in a particular month. The Administrator…
Dimensions can be modified so that the contents of the dimension changes. The impact of these changes can be viewed by end users browsing cubes. End user’s get an added analytical option due to this process.(This option is only available in the Enterprise Edition of Microsoft SQL Server 2000).Write-Enabled Dimensions Dimensions can be modified so that the contents of the dimension changes. The impact of these changes can be viewed by end users browsing cubes. End user’s get an added analytical option due to this process.(This option is only available in the Enterprise Edition of Microsoft SQL Server 2000). Dimensions…
Security of data is of paramount importance to most industries and organizations. All organizations want to restrict access to data hierarchically or define role based access. Analysis Services provides for this need. The number of Administrators and users having access to Analysis services data can be restricted by setting the role definitions in Analysis manager. End users who have access to data through client applications can be restricted in various ways. Security levels can be set for various objects within the database such as cubes, dimensions and cells. The Microsoft Windows NT or Windows 2000 Administrator role is used to…
The enforcement of security defined by the roles must be preceded by user authentication. When the user connects to the Analysis server, the first thing that happens is authentication. The user login is validated and the user is given access to the data on the server in accordance with the roles. If authentication is unsuccessful, he will not be able to access the data on the server. End user security can be enforced at multiple levels. The levels are as under: 1. Server level : This controls whether an end user can connect to the Analysis server with a client…
When a new database is created a default role is created for all OLAP Administrators to have access to the Analysis Manager. However, other users have to be given rights to access the database and its objects. Users can be given rights to Browse the cubes and objects but not to use Analysis Manager. If the user has to be given a right to use both the database objects and the Analysis Manager, specific rights have to be assigned. To assign database roles to users, right click the database and select manage roles… option. The advantage of creating database roles…
In the Analysis Manager console tree, expand the cubes folder and right click the Sales cube and click Manage roles The Cube Role Manager Dialog box appears. The dialog box shows all the users whose roles have been already assigned in the database. It also allows us to create a new role. On clicking the New button, new roles can be added to the Cube role dialog box. The create a cube role dialog box appears. Type in the name for the cube role and click Add button. The Add users and Groups dialog box appears. This shows all the…
When roles are assigned for cubes the user has access or no access to the cube. When roles are assigned to a dimension, the user gains only partial access to parts of the cube. The simplest method of restricting access to a dimension is to prevent access to all levels except the top level of the dimension. In the Cube role dialog box all users were given complete access to the sales cube. Let us restrict the access of users by permitting them to see only values broken out by fiscal years. In the console tree right click the sales…
The process of probing into a set of information for descriptive and predictive purposes is called data mining. The purpose is to identify those trends and patterns which indicate the direction of effort to achieve desired outcomes. SQL Server 2000 and Analysis Services, has inbuilt powerful data mining capabilities including algorithms for Clustering and for Decision Trees.Before actually studying the data mining capabilities of Analysis Services, let us briefly look at some terminology generally used while discussing data mining. Understanding Terms used in Data Mining A case is the term used for the facts being studied. The data used to…
Let us assume FoodMart wants the members with Golden Membership to be studied. It wants to focus on broadening the membership of the Gold Card. This can be done now using the Decision Tree Model. The Decision Tree model can be created from the relational data contained in the FoodMart 2000 Access database. 1. In the Analysis Manager tree pane right click the Mining model folder and select New mining model… 2. In the Wizard Welcome screen click Next to proceed. 3. In the Select the source type screen select ROLAP model. 4. Next select the source table for defining…
The predictive model of the decision tree helps users determine whether similar cases will have a similar behavior. The attributes of the tree can be examined for impact analysis by using the Dependency Network Browser. This can be opened by right clicking on a decision tree data mining model in the Analysis services explorer tree control. The Dependency Network Browser window contains a slider on the left and a display window on the right. It contains a Close and help button at the bottom. When the slider is moved up and down the weaker links get shaded grey and…
