How to setup Data Warehouse to prepare business intelligence solution? How to prepare Data Model, OLAP cube is SSAS (SQl server analysis services) and preparing report? For quick development and Testing with preview report for analytics process. Here is the steps in RynData to develop solution in less than 1 hour.

Here is 12 steps to setup business intelligence, develop data model, setup Data pipeline and ETL and report generation.

Step 1: Set SQL database server name and database as target data warehouse system.

Step 2: BI Modeling 

  • Add Dimension list to the Dimension list.
  • Dimension Attribute. Its is similar to a column of database table.
  • Dimension Attribute Mapping. 
  • For Example: When a retail system has Product dimension and Product is defined with Name, Department, category, gender etc. Product will be dimension and Department, category, gender will be consider as attribute.
Dimension Table preparation

Step 3 :     Setup Fact (Measure) Tables and Formula .

  • Go to Fact List Menu Item. Create measure/Fact name.
  • As per fact definition assign dimensions using Add Fact Domain Mapping Menu.
  • For example: Fact Sale has definition at Product, Location, Date. Fact name will be Sale and Product, Location, Date dimensions will be assigned as granularity level of fact.
Measure/ Fact Table preparation

Step 4:   OLAP Cube Setup

  • Add Cube List and setup Cube.
  • When BI system need one or more cubes need to design based on certain criteria. All cubes name are added here. According business process respective measure assign to hold them and all dependent dimensions will be assigned accordingly. Data relation will be automatically determined by RynBI framework.
OLAP cube setup

Step 5:    Data ETL Setup

  • Setup Data warehouse and ETL (Extraction transformation and loading operation) execution and control system.
  • ETL job for refreshing data in DW are setup using RynBI ETL tool.
Data ETL and external application connectivity

  Run : Run SQL:

  • DW Query and Staging Query.
  • As per design system model data warehouse schema will be generated by system. For data safety  data engineer interaction with DW system will be exposed staging database objects. Here It engineer can adjust data with external applications.

ELT Data load

Data warehouse generation and Staging Database objects
  • Staging to DW Data load query.

Step 6:   System Design and configuration         

    Go to System Properties: Select DW Infra and override system default properties.

Add : Server name, OLAP server, user name, password.

This image has an empty alt attribute; its file name is Screenshot-25-1024x458.png

Step 7:    DW and OLAP Infrastructure settings update

  • Change properties of DW Server Default.

Step 8:   Quick Data preparation, adjustment and control using Ryn Data EXCEL Addins.

  • Select Ryndata Tab connect with Ryn web server.
  • Click on Manage ETL Menu, RynData ETL Manager will be open.

Manage ETL (Select Data Source)

  • Load Table ( Select respective table in Drop-down list ).
  • Download Data and fill the record.
  • Save. Respective DW table in database will be loaded with data in excel.
  • Refresh DW and Cube. OLAP cube will loaded with values and processed.

And Check SQL Server Database table in data filled and verify schema.

Step 9:  Deploy Cube Schema.

Step 10:   Check SQL Server Cube generation. Verify that new cube has been created successfully

Step 11: In Excel Connect Oalp server and verify loaded data. Make sure loaded data from source using ETL is same in Cube.

  • Data.
  • From other sources.
  • Select Analysis server.
  • Server name.

 Select cube

Step 12: Prepare Excel pivot and publish workbook to central server. Open workbook and make sure report structure did not lost.      

 

Leave a Reply