Ease of navigation between different sections.

Ease of

navigation

between

different

sections.

Research Database Sets and evaluation Importing Files into SQL External Application research SSIS ICA SECTION: Derived Columns Importing CSV Files To SQL Outputting Split Column Conditions SSAS ICA SECTION: Importing SQL Tables into SSAS Calculated Columns EXTRA MATIREAL: Variables Hierarches KPI’S Perspectives Roles Error Handling SSRS ICA

SECTION PowerBi _Section Relationship View

Databases and

Business

Intelligence

Data Warehouse and Business Intelligence Portfolio using MS SSIS, SSAS, SSRS and Power BI Tools.

By James Blake Reeves T7070476

07/11/2019

Researching a suitable site to review Database sets that I may intend to use to fulfil my ICA requirement's

KAGGLE WEBSITE

Selected Website choice.

Researching and

Finding Suitable

Searching For Suitable

Database and checking

table Viability for future

steps

Searching for Suitable Data Set.

Find a suitable dataset that I can then manipulate and use for various parts of the I.C.A requirements.

Checking Context Viability.

Make sure the Datasets within are suitable for required manipulation and other aspects later on.

Searching for Database

Downloading and

Extracting Database

Files

Downloading Database Files

Make Sure the database set has downloaded correctly in the right location

Extracting downloaded Files.

So I can use the dataset first I have to extract it into a new folder for importing into various programs.

Downloading Database Files

Reviewing Database

Files

Reviewing Database Files to

check for continuity

Once Extracted make sure all

the files are in a .CSV format

as this will be useful for future

steps and make things more

efficient.

Preparing SQL

Management Studio

Logging into SQL Management studio

Using Assigned server name

created when first installing SQLMS to login to my Server instance

Loading SQL Management Studio Once logged in successfully I get presented with my own Server instance to work from and

manipulate data in various forms with out restrictions.

Logging into SQL Management Studio

Loading SQL Server Management Studio

Setting Up Database

Holder

Creating a new Database Section

As to I.C.A requirements renaming it with my TUSC card details. This will hold all tables I created within.

This can only be done on personal devices otherwise you have to create a schema and attach tables of it.

Database List

Once named it should create a database to add tables to in the Object explorer window.

Object Explorer window

Selecting which import option to use I'm importing CSV file so decided to use the Import Flat File Option for this task.

For all other file types I could use the Import Data as it selects more options of file types

Selecting the correct

import file option

depending on desired

outcome

Importing Database

Tables

How to import the database file

Selecting Flat file source as the

required Data source locate the

required CSV file and check your

columns are correct.

Second Screen

Select destination as your SQL

registered server name and

newly created Database

Selecting Data source and file Selecting SQL destination

Reviewing the source

and view and running

Package

Review the Tables and Views:

If the source and destination look correct click preview to check the look of the table then hit next.

Last Step

Check Run immediately and save SSIS package.

Review Source tables and views Save and Run Package

Execution of Table

Execute Table

If Successful should all be

green ticks and rows should

be transferred click close.

New Table

Under your original Database

should be a newly imported

table

Execution of tables

New Table view

External Potential

Programs Researched

MongoDB Online Application: MongoDB Compass Community: MongoDB is a cross-platform document-oriented database program. Classified as a NoSQL database program, MongoDB uses JSON-like documents with schema.

R Studio

Is an integrated

development

environment (IDE)

for R, a programming

language for statistical

computing and

graphics.

R studio application is running a very simple _{script that generates a table creation with}

assigned Variables and data and then displays them in a list format.

Extra Tasks

Assigned

Cisco University Challenge

2019

Practical Material reviewed

and helped altered

MS SSIS ICA

SECTION

Aims and objectives.

One aim is to be able to demonstrate manipulate and import data such as CSV files and SQL files within the SSIS Program.

Another aim is to be able to demonstrate the creation and manipulation of either a snowflake or star diagram within both Visual Studios and SQL Server Database

Finally to give a reflection summary of what I've

achieved for this section and what grade I would justify giving myself in accordance with university marking for the SSIS project overall

Error Handling

Creating Error Handling

•

From the previous steps add

a data conversion option

•

Select Data within to how

ever you want to convert

the data

•

Attach a Flat file source and

execute.

Error Handling

Creating a new SQL

Connection

SQL Connection in VS

1) Right click Connection Manager selecting new

connection manager and been presented with this connection Manager window.

2) Select your SQL Server name you used to login from previous slides

3) Then select your database name from previous slides

4) Then hit the OK Button 5) If created correctly at the bottom of the VS window under connection Managers should be your newly formed connection

Connection Manager Window

New connection manager view

Creating a New Flat

File Source in VS

Making A New Flat File Source:

1) Choosing the Flat file

Connection Manager in VS window

2) Name the Flat File in the Description Box

3) Locate the CSV in your

previous downloaded folder

for the file required 4) Check Columns are all

showing from your original CSV File

5) Then hit ok and it should take you back to the main VS window Flat File Connection Manager Editor Checking Flat File Columns

Creating Simple

Dataflow Task and

OLE DBS Source

Implementing Data Flow Task

1) From the SSIS Toolbox select Dataflow Task and drag it to the Design

Window

2) Double Click Dataflow task will take you to the Data flow window

3) In the Dataflow window select OLE DBS Source and drag to the Data Flow

Window

Data Flow Task

OLE DB Source

Flat file Source

Connection to OLE DB

Source

Importing CSV File to SQL Databases

1) Select Flat File Source from the SSIS Toolbox and place it on the Design view window

2) Connect it to OLE DB Destination 3) Double click Flat file Source and

double check the connection from this slide

4) Double click the OLE DB Destination and check the SQL Table location from this slide

5) If there's no errors in the connection hit Start and should run the task. 6) In SQL it should display the newly

imported CSV file into SQL

Flat File Source and OLE DB Destination Connection Flat File Source in SSIS Toolbox

Imported CSV file to

SQL table View

Reviewing SQL Tables

1) Open up SQL and login to your SQL SERVER

2) Click on your SQL Database and select tables

3) Newly Created Table from running the SSIS package should be display in the Object Explorer

4) Right Click select top 1000 rows and it should show all data imported by CSV file

Reviewing newly created Database

Checking to see if table has been created

Setting up Derived

Columns in Data Flow

Derived Column Setup

1) Select Derived Column from the SSIS toolbox.

2) Place it between Flat File Source and OLE DB Destination

3) Connect Flat File Source to Derived column and Derived column to OLE DB Destination

Derived Column Option

Derived Column SSIS Toolbox

Derived Column

Formula Creation

Setting Up Derived Column Formula

1) Double Click the Derived Column from the design view and a Editor window should pop up.

2) Name The new Derived Column Name something Practical based on the outcome required

3) Design your Expression using Excel based formula in my example I have added all the columns together to give a total. 4) Check Data Type is correct for

the type of Data formulated

Running Derived

Column Task.

Running Package

1) Going back to the Data flow

view hit start to start

deploying the package

2) If Successfully deployed all

the tasks should have a

green tick next to them and

showing amount of rows

changed

Dataflow View

Successful Deployment

Newly Added

Columns in SQL Table

New column added in SQL Table

1) From the derived column

formula creation a new table

should be created in SQL

2) When you open the New

table a Column Should be

added that displays your

formula inside

New Table New Table Column

Minimum age to buy cigarettes changed from 16

to 18 on the 01/10/2017

Setting Up

Conditional

Split

SSIS Tool Box Conditional Split

Placing Conditional Split into Design Window

Placing Conditional Split 1) Select Conditional Split

From SSIS Toolbox

Adding OLE DB Source

Combining OLE DB Source to Conditional split

1) Select OLE DB Source from the SSIS

2) Drag it to the Design window and connect using the Blue Arrow to Conditional Split from previous slide

SSIS Tool Box OLE DB Source

OLE DB

Source Design View

Assigning Conditional

Split Conditions

Conditional Split Formulation

1) Double Click on

Conditional Format in the

design window

2) Define each output name

3) Create conditions for each

outcome you want from

your original table

4) Hit Ok

Conditional Split

Assigning Flat File

Connections for each

Outcome

Flat File Csv Export

1) For each outcome Select a

new

Flat file source from

the SSIS Tool Box

2) Assign a new connection

to each one of the

conditions and assign a

newly create CSV file

Assigning Flat file outcomes

Flat File Connections

Reviewing Conditional

Split Outcome

Outputting Conditional Splits

to CSV Files

1) Hit Run to check for errors

and all should be green

2) In your assigned folder

once package has ran a

newly created CSV Files

with your defined

Module

Section

Review

Design mark overall: 1

st

_degree

classification

Implementation mark overall: 1

st

_degree

classification

Requirement’s mark overall: 2:1 degree

classification

Other mark overall: 1

st

_degree

classification

MS SSAS ICA

SECTION

Star Scheme and Normalised Snowflake Schema

Tabular Database with relationships, Calculated Columns, Measures, KPIs,Perspectives,

Hierarchies, Partition's and Administrative roles

Deployed SSAS Tabular model database.

Analytical solution showing multidimensional, trending and projecting data

1

st

_{and 2}

nd

Normalised data

form.

•

1

st

_{Normalised form is the}

two tables I first started

with a lot of irrelevant fields

and complicated date

•

2

nd

_{Normalised form is}

removing data fields and

adding it to another table if

relevant

2nd normalised form 1st_{normalised form}

Starting SSAS Project.

How to start a new SSAS Project

1) Following

slides 3-7

find

new dataset for this section

and import tables into SQL

2) Select Create a new Project

option when loading Visual

Studios.

New Data Tables

Selecting a new project

Selecting Tabular

Project

Selecting and naming the new

project

1) Typing Analyses Server into

the search box

2) Click on Analysis Service

Tabular project

3) Name the Project

something appropriate to

the task required

Selecting the correct project

Selecting Tabular

Server connection

and main View

Selecting Tabular model and reviewing Model.bim Window

1) Select your SQL Server name from

previous slideand enter it into workspace Server textbox

2) Select Test connection to make sure the connection works.

3) Hit Ok and wait for it to initialize and display the main Model.Bim window Tabular model designer Model..bim visual studio Window

Selecting the Data

Source Function and

Data connection

1) In the toolbar Select

Model and then import

from Data Source

2) In the New window Select

the Microsoft SQL Server

Option as your

connection type

3) Hit Next to move to the

next scren

Import From Data source Selecting Data source

Selecting Desired

Imported Tables and

Impersonation

1) Select all tables you wish to use for your Tabular project and review the selected tables.

2) Using your Windows user name and password for the impersonation security aspect. Table selection Impersonation Selection options

Imported Tables and

review

1) The Import Wizard will start to import your selected tables into the Tabular View

2) Once done your tables should be displayed in the Model.Bin View to review and edit further

Importing Success

Tabular Data View

Viewing SSAS

Relationships

1) Clicking the button circled

displays the table

relationship View

2) If the relationship created

in SQLMS has been

established should auto

detect when you

imported the tables

Calculated Column

•

Right click on column near

to where you want to add a

calculated section and a

new column will be created

•

Rename to something

practical to your

requirements

•

Make sure the column is

highlighted by clicking it.

•

Input your desired DAX

formula you want for the

specific functions.

Variables

•

Clicking on the boxes below

brings up a new option to

add a DAX formula creation

text box

•

Depending on the desired

result input your variable

function and it should

display the desired result

Hierarches

• Select the Relationship View highlighted to be take to the section to create Hierarches • At the top of the table click

on new hierarches.

• Then drag and drop required Column headers associated to the requirements

Hierarches view

KPI’S Creation

• Right Click on one of your variables you created

• Click on absolute value and determined the

percentage for the Key performance indicator • Depending on the

performance of the metric required change the

percentages to reflect decision

KPI menu

Perspectives

• Following the Screenshot click on Create and Manage

perspective

• Press New Perspective and create a new view for

associated users to be able to see

• Click ok columns that you want each user to be able to see

• When you hit ok you will see depending on the user each table will change accordingly

Selecting Perspective views based on user

Selecting the Perspective option

Roles

Role Manager

Selecting Role Manager Option

• Select the option for Role in the screenshot. • Assign a new name for

the respected User and set desired permissions • Hit ok and change the

perspective section and it should change which tables can be viewed by what user and how they can interact with the tables

SSAS Section

Review

Design mark overall: 1

st

_degree

classification

Implementation mark overall: 1

st

_degree

classification

Requirement’s mark overall: 2:1 degree

classification

Other mark overall: 1

st

_degree

classification

SSRS SECTION

Create Reports based on

Datasets and Desired

requirements

Reflection Summary Based on

this section

Data Source

Connections

• To Create the Data Connection in the solution Explorer Right click Shared data source then new data source and input your SQL details • For the Report Data Connection

right click Data sources and select data connection using the same credentials as the first connection

Solution

Explorer View

Data Source View

Report Data View

Spatial Maps

• To Create a Map view option in SSRS click and drag the Map option to the design Window

• Then run the wizard and depending on your Dataset location you can choose the USA if applicable • If not and you have Spatial Data

within your database select the SQL server option

• I took the ESRI option and imported a shapefile from an online website and ran through the wizard

Report Map Option New Map Layer

SSRS Bar Graph

Addition

• For a Bar graph selection double click chart in the toolbox menu • A whole host of different types of

Graphs pop up pick one suitable to the type of data your trying to demonstrate

• If not and you have Spatial Data within your database select the SQL server option

• I took the ESRI option and imported a shapefile from an online website and ran through the wizard

Bar Graph View

Choosing Bar Graph Type

SSRS Final Report

Views and Errors

• Due to technical errors with the

SSRS Software having issues trying to deploy the map feature it wont display correctly. In future a

potential work around is to input a spatial data table in place of the ERID file might work

• The Data type for the Bar Graph needs changing to a numerical value so wont display correctly • In the design view it works pretty

well

Final Report View Final

Design View

SSRS Section

Marks Review

Design mark overall: 3rd degree

classification

Implementation mark overall: 2.2

nd

degree classification

Requirement’s mark overall: 2

nd

_degree

classification

PowerBi

SECTION

Create Dashboard based on

Data requirements

Create a Bar Graph and Pie

Chart Views showing data views

Relationship View in

PowerBi

• Selecting the Database

relationship button should take you to a section which displays all the tables

• If the tables are connected correctly in SQL then PowerBi is smart enough to auto detect and connect the tables automatically

Creating a Bar Graph

In PowerBi Desktop and

Reviewing in a Web

Browser

• Selecting the bar graph option from the visualization’s area and drag onto the man window.

• Choose the desire data from your tables and add them as appropriate • You can also double click and ask

PowerBi a question and it will do it for you automatically

• Finally use the publish option and review it on the website PowerBi Website

Bar Graph View PowerBi Desktop

Bar Graph View

Creating a Pie Chart

In PowerBi Desktop and

Reviewing in a Web

Browser

• Finding the desired pie chart option from the visualization’s area and place it on the main screen

• Choose the desire you wish to

display in the pie chart and add it to the values section

• PowerBi should update accordingly based on the data inserted

• Finally use the publish option and review it on the website PowerBi Website

Pie Chart View PowerBi Desktop

Pie Chart Website

Using the PowerBi

Intellisense function

• Double clicking on the report window brings up an option to ask PowerBi a question about your data sets

• It will come back with a useful suggestions this is great to create reports quickly and easily

Intellisense Desktop view PowerBi Intellisense Website View

Using the PowerBi

Map function

• Using the Desktop Map function select an empty area on your report

• If you have spatial map data such as locations or coordinates drag it to the location section

• PowerBi will automatically create a map based on the data and display it PowerBi Map Function Desktop PowerBi Map Function Website

PowerBI

Section Marks

Review

Design mark overall: 2.2 degree

classification

Implementation mark overall: 2

nd

_degree

classification

Requirement’s mark overall: 1

st

_degree

classification