Data Warehouse Tutorial - Creating an OLAP cube - Data Warehouse for beginners (Lesson 3)
Creating an OLAP Cube: Part 1
Section Overview
This section covers the initial steps in creating an OLAP cube using SQL Server Business Intelligence Development Studio, including project setup and data source creation.
Project Setup
- Open SQL Server Business Intelligence Development Studio, navigate to the file menu, point to new, and select project.
- Choose the business intelligence project type and select the analysis services project template. Name your project; the solution name box updates automatically.
Creating a Data Source
- In Solution Explorer, right-click on the data source folder and select new data source. The data source wizard will appear.
- Click Next through the wizard, define connection settings by selecting Microsoft OLE DB Provider for SQL Server, entering server name, authentication details (login/password), and testing the connection.
Data Source View Creation
- Right-click on the data source views folder in Solution Explorer to create a new data source view. Select tables and add related tables as needed.
- Name your data source view and finish; this opens the datasource view designer displaying selected tables.
Creating Dimensions
Section Overview
This section discusses how to create various dimensions within your OLAP cube.
Cars Dimension
- Create a cars dimension by right-clicking on dimensions folder in Solution Explorer and selecting new dimension.
- Select attributes from available lists (e.g., car model), drag them into hierarchies pane to establish relationships.
Additional Dimensions
- Follow similar steps to create additional dimensions such as customers, payment methods, sales date, sales hours, salesman, and sales place dimensions. Each follows a consistent process of selection from existing tables.
Creating the OLAP Cube
Section Overview
The final part focuses on assembling all components into an OLAP cube structure ready for processing.
Cube Creation Process
- Right-click on cubes folder in Solution Explorer to initiate a new cube creation process.
- Verify that "use existing tables" is selected during setup; choose transactions facts table for inclusion in your cube design.
- After naming your cube appropriately, finish setup which opens up the cube designer showing its structure.
Processing the Cube
- Right-click on transactions cube in Solution Explorer to process it; confirm actions when prompted (click yes).