MSAS: Working with Parent-Child Dimensions

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… Read More

MSAS : Basics of Levels

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… Read More

MSAS : Working with Standard Dimensions

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… Read More

MSAS : Shared vs Private Dimensions

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… Read More

Understanding Dimension Basics

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… Read More

Oracle 9i Database Triggers

This Weeks tutorial explains about Oracle Triggers, Building a Trigger, Trigger Execution, Trigger Restrictions and Testing Mutating Tables.Database Triggers A database triggers is stored PL/SQL program unit associated with a specific database table or view. The code in the trigger defines the action the database needs to perform whenever some database manipulation (INSERT, UPDATE, DELETE) takes place. Unlike the stored procedure and functions, which have to be called explicitly, the database triggers are fires (executed) or called implicitly whenever the table is affected by any of the above said DML… Read More

ASP.NET Using the DataList and Repeater, Datagrid Controls

In this tutorial of Week 6 we will be learning about Overview of List-Bound Controls, Creating a Repeater Control, Creating a DataList Control, Introduction to the DataGrid, Setting Up the DataGrid , Using Advanced DataGrid Features and Adding Advanced Features. Overview of List-Bound Controls As we have seen in the previous tutorials that Asp.net ships with many controls. We already discussed different types of buttons, validation controls and the calendar controls. But the most power of Asp.net comes with the Databound controls. Databound controls are those controls which can be… Read More

MSAS : Office 2000 OLAP Components

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,… Read More

MSAS : Client Architecture

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… Read More

MSAS : Cube Storage options

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… Read More

MSAS : Meta data Repository

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… Read More

MSAS : Analysis services Tools for Extended Functionality

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… Read More

MSAS : The Wizards

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… Read More

Accessing Data with C#

This tutorial explains about Accessing Data with C# with an Overview of ADO.NET, Connecting to Data , Executing Commands , Working with Data and Choosing an ADO.NET Provider along with the Project files used in this.   ASP.NET: Accessing Data with C#  Introduction: When working with classic asp we had ADO, object model for communication with the database. Microsoft.NET has introduced ADO.NET components that lets the developer communicate with the database more efficiently and easily. In this article we will see how we can make use of the ADO.NET classes… Read More

SQL Unit Testing Tools

Please find the Open source SQL Unit Testing tools available. SQL Unit Testing Tools SPUnit SPUnit  Description: SPUnit is an application of the popular xUnit framework concept to enable unit testing of SQL Server stored procedures.  SPUnit is (loosely) based on the xUnit family of Unit Test Frameworks. It is intended for testing MS SQL Server stored procedures using actual stored procedures. The idea was to make it really painless to test the code in stored procedures, and facilitate test-first programming of stored procs. It is a port inspired by… Read More

Microsoft .NET Unit testing Tools

Here are the few Microsoft .NET Unit testing Tools available. Most of them are opensource and free.Microsoft .NET Unit testing Tools csUnit http://www.csunit.org/ Description: csUnit is a unit testing framework for the Microsoft .NET Framework. It targets test driven development using C#, Visual Basic .NET, and managed C++. Requirement : csUnit has been tested using the Microsoft .NET framework 1.0 Service Pack 2 runtime on an Intel-compatible platform. Unit Testing with C# Tutorial How do I make use of the Assert class? How do I implement simple test cases for… Read More

MSAS : The Analysis Manager and Analysis Server

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… Read More

MSAS : The Data warehousing framework of SQL Server 2000 – Part 2

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… Read More

SQL Server 2000: Using Views in SQL Server

This tutorial on Views in SQL Server covers the concept of views, Creating Simple View, deleting views and how to use them.Using Views in SQL ServerViews are nothing but saved SQL Statements, and are sometimes referred as “Virtual Tables”. Views cannot store data; rather they only refer to data present in tables. As the name implies, they are just used to view the contents of the tables by means of joins etc. They can be used to provide row- or column-level access to data, to wrap up complex joins, to… Read More

MSAS : The Data warehousing framework of SQL Server 2000 – Part 1

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… Read More

MSAS : Microsoft Data Warehousing Overview

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,… Read More

Oracle 9i Procedures and Functions

This tutorial covers Developing Procedures and Functions, Creating a Procedure, Executing a Procedure, Creating a Function, Executing a Function, Passing Parameters – IN Parameters, OUT Parameters, IN OUT Parameters, Purity of a User-Defined Function and Positional and Named Notations. Oracle 9i : Procedures and Functions PL/SQL subprograms A subprogram is a named block of PL/SQL. There are two types of subprograms in PL/SQL namely Procedures and Functions. Every subprogram will have a declarative part, an executable part or body, and an exception handling part, which is optional. Declarative part contains… Read More

SQL Server 2000: Using System and Extended Stored Procedures

This tutorial covers how to use System Stored Procedures which are stored in master and msdb databases and normally begin with ‘sp_’ and Extended Stored Procedures normally begin with ‘xp_’.Using System Stored Procedures Whenever you add a database, add a login, create a table or modify any object, the changes reflect in the system tables. System tables are used by the SQL server’s for its own reference but we can also view their contents. But the information stored in them is somewhat in binary or unreadable format, therefore Microsoft has… Read More

SQL Server 2000: Securing Your Stored Procedure

This tutorial explains how to secure your stored procedures if you ever need to.  Procedures resides on the database server in open fashion. It means that every body can easily access and view its code. Securing Your Stored ProcedureStored Procedures resides on the database server in open fashion. It means that every body can easily access and view its code. They are present in “syscomments” system table in each database and completely accessible. In order to secure it, we can encrypt them by using “WITH ENCRYPTION” keyword. Remember that encrypting… Read More

SQL Server 2000: Creating Stored Procedure with Input and Output Parameters

This tutorial explains how to create and use Stored Procedures with Input Parameters and output parameters along with the screen shots and sample code. Creating Stored Procedure with Input ParametersInput Parameters in Stored Procedures are placeholders for data that the user needs to send. Technically, input parameters are memory variables because they are stored in memory. For creating Stored Procedure with Input Parameters, just change the above code to look like CREATE PROCEDURE Show_Customer @City varchar(50) AS SELECT FirstName, LastName FROM Customer WHERE Location=@City ORDER BY FirstName Here the placeholder… Read More

SQL Server 2000:Creating and Using Stored Procedures

This tutorial series covers the basic concepts of Stored Procedures in SQL Server and hot create and execute a simple stored procedure.Using Stored ProceduresSome of the most important concerns to a database user are speed and efficiency. Faster data retrieval provides applications to responds and behaves quickly. SQL Server provides “Stored Procedures” for faster and quick data access. Stored Procedures is a query that is stored in a database on SQL Server rather than being stored in the front-end code on users machine. You may think that storing queries in… Read More

Using Rich Server Controls with C#

In the last article we saw some of the simple controls. Those included validation controls, TextBox, Label and other simple controls. Microsoft.net framework provides the developer with more advanced controls. Among those are the Calendar, AdRotator and the Xml Control. In this Tutorial we will see how we can make use of the rich controls provided by the framework.Using Rich Server Controls with C# Introduction: In the last article we saw some of the simple controls. Those included validation controls, TextBox, Label and other simple controls. Microsoft.net framework provides the… Read More

SQL Server 2000 Training Details

We are glad to let you know that we have started SQL Server 2000 training series. See the topics below which we will be providing the tutorials. Please let us know if there anything you would like to add to the topics.SQL Server 2000 Training1. Introduction to Microsoft SQL Server 2000 Overview of SQL Server 2000 | Components of SQL Server 2000 | Overview of SQL Server 2000 Architecture 2. Installing SQL Server 2000 Planning to Install SQL Server 2000 | Deciding SQL Server 2000 Setup Configuration Options | Running… Read More

MSAS : Browsing the Cube

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.… Read More

MSAS : Designing Storage and Processing the Cube

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… Read More

MSAS : Building the Cube Part #3

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… Read More

MSAS : Building the Cube Part #2

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… Read More

MSAS : Building the Cube Part #1

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… Read More

MSAS : Setting up the Database in Analysis Server

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… Read More

MSAS : Preparing to Create the Cube

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… Read More

MSAS : Introducing Analysis Manager Wizards

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… Read More

Oracle 9i PL/SQL Collections

This tutorial covers Defining and Using Collections – Declaring, Initializing, and Referencing PL/SQL Collections and Collection Methods – Using the Collection Methods. Oracle 9i: PL/SQL Collections Introduction Version 8.0 and higher versions of Oracle are referred to as ORDBMS (Object-Relational Database Management System). The traditional Oracle database management system is extended to include Object-Oriented Concepts and structures such as abstract data types, nested tables, varying arrays, object views and references. Abstract Datatypes Also called user-defined datatypes are those that consist one or more subtypes. Apart from using standard oracle datatypes… Read More

Microsoft Analysis Services Installation

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… Read More

ASP .NET: Validating User Input with C#

This tutorial covers Validating User Input with C# covers Overview of ASP.NET Validation Controls , Using the Simple Validators , Using the Complex Validators and Summarizing Results with the Validation Summary Control. In this tutorial we will see the validation controls. The purpose of the validation controls is to validate the user input. Asp.net provides the developer with different types of validation controls. One most important point to note is that the validation is done on the client side as well as on the server side. You can always turn… Read More

Oracle 9i Exception Handling

This week tutorial covers Oracle Exception Handling and the different types in it with sample SQL scripts along with the screen shots. Topics covered in this week, Introduction to Exception Handling – Propagation of Errors Types of Exceptions – Named System Exceptions; Unnamed System Exceptions; User-Defined Exceptions. Exceptions An Exception is an error situation, which arises during program execution. When an error occurs exception is raised, normal execution is stopped and control transfers to exception-handling part. Exception handlers are routines written to handle the exception. The exceptions can be internally… Read More

MSAS – Applying OLAP Cubes

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… Read More

Understanding OLAP Models

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… Read More

Designing the Dimensional Model and Preparing the data for OLAP

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… Read More