Data warehouse analysis (Datawarehouse) with OLAP

Tram Ho

After the first article, I introduced the difference between database and datawarehouse. True to the promise, this section I went into detail about the demo I did before with the software that seems a bit old done on window 7, but enough to use and just enough to ensure detailed reports of large assignments such as the Data warehouse subjects that some universities are teaching:

  • SQL Server 2008 R2
  • Microsoft visual studio 2008

Import the database template into SQL 2008 R2 through SQL Server Management Studio. This step you learn google yourself because it is quite easy

https://drive.google.com/file/d/1TWQOe7-1qfrVKgHwASOOHMUH3nJ77VzX/view?usp=sharing

Step 1: Start the OLAP Analysis Services – BIDS Environment working environment.

  • Click Start Menu -> Microsoft SQL Server 2008 R2 -> Click SQL Server Business Intelligence Development Studio .

Step 2: Start the Analysis Services Project

  • Click File -> New -> Project -> Business Intelligence Projects -> select Analysis Services Project -> Assign Project Name -> Click OK

Step 3: Create a new Data Source

3.1 Add data to be analyzed

  • In the Solution Explorer window with the project created, right-click Data Source -> Click New Data Source

3.2 Next, click “New”

3.3 Select database “Sales_DW”

This database was previously imported into SQL 2008 R2 so it can be used immediately

3.4 Select the “Inherit” and “Next” Options

Step 4: Create a new Data Source View.

Purpose to create an online data view for the OLAP project. From there it is possible to make an accurate decision to build the blocks serving the query.

4.1 In the Solution Explorer window right click “Data Source View” -> Click “New Data Source View”

4.2

  • Select FactProductSales -> move to the right side.
  • Select “Fact Table” on the right ( FactProductSales ) -> Click Add Related Tables

result:

4.5 We have now created a data relationship view between the actual tables

Step 5: Create a cube and set the “Dim” (Dim) based on the available table

5.1 Select “Use exissting tables” -> Click Next

5.2 Select the table to analyze the value attributes

Select ** Fact Table Name ** in Measure Group Tables (FactProductSales) -> Click Next .

5.3 Select “Measures” in the list to display in the cube later -> Click Next

5.4 Next we need to determine the “dimensions” (Dim) to display of the cube

Select all Dimensions related with Fact Table -> Click Next

5.5 Finish

5.6 Cube is ready and completes its “dimensional” settings and values

Step 6: Select the table properties to make “dimension” or to measure “measure”

Drag and drop dimension from Table in Data Source View and add Attribute Pane on the left

Similar to Dim Date , anything you want is even Dim Customer

Step 7: Deploy the Cube

After a series of settings select ” dimension ” (Dim) measure types depending on what data the report needs, we go to the cube creation step based on the setting.

7.1 Set the required cube properties in the Deployment Properties.

7.2 In “Solution Explorer”, right-click “Project Name” -> Click “Deploy”

7.3 Wait to finish deploying

Step 8: Launch the cube based on deployment deployment

8.1 Click “Run” to run “Cube”

Step 9: Analyze data via “Browser”

9.1 This step is the step of creating report tables and report data based on the blocks created.

Drag and drop measures into Detail fields , & Drag and drop Dimension Attributes into rows or columns.

For example: To statistic the number of products sold by day.

  1. Product Name Drag and drop into column. (Statistics by product name)
  2. Full Date UK Drag and drop to line (Statistics by day)
  3. FactProductSalesCount pull down the item: measure in Detail area (Main table to statistics the number of products sold by day)

Conclude

There are many combinations of Dim and data to measure . Aggregate report extraction of this type needs a lot in different big data systems, big enterprise companies need it very much. This demo is simply a combination of multiple databases. Hope readers have a little view of something about Datawarehouse.

Share the news now

Source : Viblo