MS SQL
SERVICES
Final Project
Presented By : Ramyashree Keshavamurthy
IDS 521 Adv Database Management | Spring 2024
OVERVIEW
SSIS (SQL Server Integration Services)
SSAS (SQL Server Analysis Services)
SSRS (SQL Server Reporting Services)
IDS 521 ADBMS | Overview
GENERAL INTRO
Microsoft SQL Server provides many services to manage and utilize data
The 3 services described in this presentation are –
SSIS (SQL Server Integration Services)
SSAS (SQL Server Analysis Services)
SSRS (SQL Server Reporting Services)
Data processing and migration are handled by SSIS. When constructing a data
warehouse or data mart, data must be taken from multiple transactional systems and
flat files, converted, and fed into a location where it can be analyzed and reported on.
SSAS is a Business Intelligence solution that performs online analytical processing
(OLAP), data mining, and reporting.
SSRS gives you the tools you need to build reports that help you understand your data
better.
1
SSIS
SSIS, or SQL Server Integration Services, serves as a data warehousing tool facilitating tasks like
data extraction, importing, and transformations such as cleansing, aggregating, and merging
data. It simplifies the process of transferring data between databases, including SQL Server,
Excel files, Oracle, DB2, and other sources.
SSIS provides graphical tools and wizards for various workflow tasks, including email sending,
FTP activities, and managing data sources and destinations. It's utilized for several purposes
such as:
Consolidating data from diverse sources
Automating administrative functions and loading data marts and data warehouses
Cleaning and standardizing data
Integrating business intelligence into the data transformation process.
IDS 521 ADBMS | SSIS
SSIS ARCHITECTURE
& FEATURES
The components and their respective applications are
mentioned below -
Control Flow (Stores containers and Tasks)
Data Flow (For Source, Destination & Transformations)
Event Handler (sending of messages, Emails)
Package Explorer (Offers a single view for all in package)
Parameters (User Interaction)
SSIS: HOW TO INSTALL & CONFIGURE SSIS
Step 1: Run the SQL Server 2014 setup again, and this time select Step 2: To verify if the Integrated services are
the integration services feature from the feature selection list installed, search for services and check that
the SQL integrated services are running.
IDS 521 ADBMS | SSIS
Step 3: Type the SQLServerManager <version>.msc
such as [Link] for SQL Server
When the SQL Server Configuration Manager
appears, Click on the SQL Server Services in the left
panel, Check the “State” of service.
HOW TO USE SSIS ?
SSIS in MS SQL Server
IDS 521 ADBMS | SSIS
Open SQL Server Management Studio and connect to the
SQL server on which we just installed SSIS.
Expand the dropdown
Right-click on 'integration services catalog’
Click on 'Create catalog' to open the catalog creation
wizard
The SSIS database, which was established as part of the
catalog generation procedure, stores the integration services
catalog.
In Microsoft Studio, we can see that the SSISDB database has
been established by expanding the Database and Integration
Services drop downs
CREATING SSIS PROJECT
Open Visual Studio SQL Data
IDS 521 ADBMS | SSIS
tools 2017. And click on a new
project.
Select the Integration
Services template under
business intelligence.
Select Integration Services
Project.
QUERIES IN SSIS
We are creating a table which will be reflected in MS SQL Management Studio
Once the query is done, click ‘F5’, and the query will be executed and shown by a green check mark.
In MS SQL Management Studio, we can see that the table has been added to our database.
Thus, the query has been successfully executed.
CONTROL AND DATA FLOW IN SSIS
Use the SSIS project Toolbox to create multiple blocks (as required)
Blocks can be inserted to control flow or data flow
Select the control flow tab to define control flows.
For a simple task, select the data flow task from favorites and drag it
into the control flow section.
To define data flow in each control flow, click on the created icon in the
control flow section.
The types of data sources and data destinations can be found in the
SSIS toolbox.
There are a number of functions that can be used to manipulate the
data between the sources and destinations.
Data sources for the SSIS project can be from various sources, e.g., BAK
files, excel files, and .csv files.
IDS 521 ADBMS | SSIS
USE CASE 1 - IMPORTING DATA INTO DB
Open the control flow created to open the
data flow
From the source list mentioned in the toolbox,
select the flat file source.
Edit the flat file source by right-clicking and
selecting edit.
Select connection manager and select new.
In the connection manager section, enter the
server name mentioned in SQL server
management studio.
Go to the columns section and verify that the
columns are formatted correctly.
Click on ok.
On SSDT tools, select the ADO NET
Destination and connect its input as the
output from the flat file source.
Right-click ADO NET Destination and edit it.
In the connection manager section, select
new.
Enter the server name in which the DB was
created on SQL server management studio.
In the enter database section, enter the
blank database created and click on ok.
Once the flow Is executed, verify it by
checking the records in the Database.
USE CASE 2 – PERFORM JOIN
SSIS TUTORIAL – DATA FLOW TASK
To demonstrate this process, we create a Microsoft Excel file. As a sample, Open SQL Server Data Tools (SSDT via Visual Studio 2017) and click
we have created an “instructor” database, with fields like “id”, “name”, on “File” option and within that click on “New->Project”
“dept_name”, and “salary”. Save that file as Excel 97-2003 Workbook type Once we get the “New Project” window, expand “Business
Intelligence” on the left side pane under “Installed”, and then click
on “Integration Services”
On the next screen, “Control flow” contains the “Data Flow Task”, so we drag
and drop the “Data Flow Task” onto the main window
If we double-click on the “Data Flow Task”, the control automatically moves
to the Data Flow from the Control Flow
We want to extract the data from Excel file that we created earlier, so we
drag and drop the option “Excel source” under “Other Sources” appearing
on the left-hand side pane
2
SSAS
SQL Server Analysis Services (SSAS) is a vital tool in SQL Server
Business Intelligence, enabling efficient data utilization for
end users. By harnessing Online Analytical Processing, Data
Mining, and Reporting features, SSAS constructs
multidimensional OLAP cubes sourced from Data Marts or Data
Warehouses, facilitating swift and comprehensive data
assessment.
IDS 521 ADBMS | SSAS
INTRODUCTION TO SSAS
Online Analytical Processing (OLAP)
solutions are developed using the SSAS
technology, which is a part of the
Microsoft Business Intelligence stack. It comes in two flavors:
Multidimensional and Tabular.
SSAS, a feature of Microsoft SQL
Server, performs analysis using a
range of dimensions.
SSAS may create cubes utilizing data
from data marts and data warehouses for
faster and more extensive data analysis.
It serves as both a multidimensional
OLAP server and an analytics engine.
IDS 521 ADBMS | SSAS
STRUCTURE OF SSAS
01 02 03
SSAS: Data gathered by RDBMS
RDBMS: Using ETL tools, RDBMS is put into SSAS cubes via Client: With the use of
stores data from various data analysis services initiatives. dashboards, scorecards, and
sources, including databases, SSAS cubes produce an analysis portals, data may be
excel, etc. database as a result. Several conveniently obtained.
procedures can be carried out
once the database is prepared.
IDS 521 ADBMS | SSAS
STRUCTURAL FLOW OF SSAS
FEATURES OF SSAS
SSAS allows users to create MOLAP, HOLAP,
and ROLAP architectures.
Data model generation and administration
are versatile in [Link] provides a
dynamic structure, shared metadata, and
security features.
SSAS tools can be used with a variety of
wizards and designers, and this server
supports API backward compatibility.
SSAS features enable users to work in
client-server or offline mode.
INSTALLATION OF SSAS
Step 1: Click on the installation tab
on the left side from the first image.
The installation process starts and
follow all the steps by clicking next.
Step 2: In the feature Selection
tab , select the Analysis Service
box in order for ssas to install.
Step 3: In the last step, this window
pops up showing the certification
and the status of the installation.
Click on close and the installation is
complete.
CHECK OF INSTALLATION AND PROJECT OF SSAS
Before connecting the database, we first establish a
new connection to the SQL Server database engine
and analytical services using SQL Server Management
Studio.
Step 1: Launch SQL Server Management Studio, go
to Connect, select Database Engine, and finally
click "Connect" in the box that appears.
Step 2: Using the same SQL server instance, go to
Connect once more, click on Analysis services, and
then choose "Connect" from the window that
appears.
Step 3: We must now link the newly generated
database connection to a new application.
SSAS: CREATING NEW PROJECT
We now employ SQL Server Analysis Services and all of
its component parts. The first thing we do is start a
Visual Studio SSAS project.
Step1:Open the 'SQL server data tools for visual
studio' program that was installed in the steps prior
to this. Next, we select "New Project.“
Step 2: A list of project types is displayed in the
window that appears. Select 'Analysis Services
Tabular Project' after that.
Step 3: This takes us to the following box, where we
type the project's name and click Create .
Step 4: Next, we choose the 'Integrated Workspace'
option after choosing the tabular model designer.
3
SSRS
This tool is a part of Microsoft's Business Intelligence suite known as MSBI.
SQL Server Reporting Services (SSRS) is Microsoft's reporting software.
SSRS produces organized reports comprising data tables, graphs, images,
and charts.
Visual Studio serves as the platform for running SSRS.
It has the ability to create reports in different formats like HTML, Excel, and
CSV.
IDS 521 ADBMS | SSRS
INTRODUCTION: SSRS
SQL Server Reporting Services (SSRS) is a reporting tool designed for creating
structured reports featuring tables, graphs, images, and charts. These reports
are stored on a server and can be executed with user-defined parameters at
any time. SSRS is a component of the Microsoft SQL Server Services package.
SSRS Benefits:
1. Accelerates processing of reports based on relational and multidimensional
data.
2. Empowers users to interact with data independently of IT professionals.
3. Makes reports accessible via the internet.
4. Supports exporting reports in various formats and facilitates email
transmission of SSRS reports.
5. Offers a range of security features to manage access to reports.
IDS 521 ADBMS | SSRS
SSRS ARCHITECTURE & FEATURES
IDS 521 ADBMS | SSRS
Benefits Challenges
1. It offers faster performance and cost
IDS 521 ADBMS | SSRS
savings. 1. Lack of printing functionality; to print,
2. Provides access to data stored in MS SQL users need to export reports to PDF, Excel,
Server and Oracle databases, streamlining Word, or alternative formats.
reporting processes. 2. Requirement for users to input parameters
3. Eliminates the requirement for expensive for report acceptance.
specialized expertise. 3. Difficulty in modifying custom code and
4. Enables quicker generation of reports on debugging expressions.
both relational and cube data. 4. Inability to include page numbers or total
5. Delivers real-time data to businesses, page counts within the report body.
facilitating improved decision-making.
SSRS: HOW TO INSTALL & CONFIGURE SSRS
Step 1: Install SQL Server Reporting Services Developer version from the official Microsoft website.
Step 2: Configure SQL Server Reporting Services (SSRS)
IDS 521 ADBMS | SSRS
Step 3: Configure the Database: Click on the Database tab on the left and set up the Report Server Database
Step 4: Configure Web Portal URL – Click on the Web Portal URL and click on Apply as shown
Step 5: Download and install MS Report Builder
SSRS: REPORT PREPARATION
Step 1: Open SSRS Configuration Manager and
copy the web URL of your computer's local host by
clicking the Web page URL on the Left Panel.
Step 4: Your Microsoft report builder,
Step 3: To create a new report, click New which was already installed previously,
and then click Paginated report. will open and select the desired report.
Step 2: Paste the URL in the browser, and a
client report page will appear in your browser.
Step 6: Click on Build and Choose
your Data Base from there.
Step 8: After selecting all the required files for
the report, it will give the output you desired.
Step 5: Set up the database by clicking on
NEW and selecting the local host databases
that are available in your system.
Step 7: After selecting your database, the next step is to
enter a query to retrieve the desired tables for your dataset.
Step 9: Run the report from the Home Tab.
Step 10: Now save the report.
Step 11: Upload your report files, which are
saved into this SQL Server reporting service.
SSRS: SAMPLE SHOTS
This is a Sample Dashboard we created for better visualization of Department Key Performing Indicators.
a.) Average Salary of the instructors in each department
b.) Average GPA of the students in every department
c.) Number of Students in each department
This tabular report provides a comprehensive visualization of the entire database, organized row-wise for
enhanced data presentation.
IDS 521 ADBMS | SSRS
SSRS: CREATING A PROJECT
To create a project, select the Report server project. Add groupings and totals:
Click on ‘OK’.
Formatting table heads and columns:
IDS 521 ADBMS | Spring 2023
THANK YOU
Presented By: Ramyashree Keshavamurthy
UIN - 670744401