Microsoft BI Tools Snapshot


  •  SQL Server Management Studio (SSMS)

-Manage database schema
– Write & execute queries
– Stored Procedures

  • SQL Server Integration Services (SSIS)

– Extract, Transform and Load (ETL) data
– Updating data warehouses
– Cleaning and mining data
– Creating “packages”
– A series of tasks of manipulating the input data
– Can take a variety of sources
–  XML, flat files, relational data sources

  • SQL Server Analysis Services (SSAS)

– Design, create and manage multidimensional structures with
data that is aggregated from multiple sources
– Data cubes
-Analysis by data mining

  • SQL Server Reporting Services (SSRS)

– Server-based reporting platform
– Publish reports to a report server, or Microsoft Windows application or to a Sharepoint site.
– Can schedule reports to run
– Users can subscribe to reports
– Reports (.rdl files) can be exported to a variety of formats
– Excel, PDF, CSV, XML, Word

Study Notes: Busniess Intellengence (B.I.)

What is Business Intelligence?

Business intelligence is used for transforming raw data into meaningful information for analysis through techniques and tools such as MSS, Data Warehousing, OLAP, Data Mining, either individually or in the combination.

Diagram: BI through DW, OLAP & DM


How does it work?

  •  Once raw data is gathered, data engineers/analysts or BI developers use  ETL (Extract Transform Load) tool to manipulate, transform, and classify the data in a structured database
  • The structured databases are so-called data warehouses or data marts, where business owners and decision makers can access business data and use them to solve their business problems.
  •  BI developers/analysts can use the data visualization tools (eg. Power BI) to summarize the results on visual dashboards, making such information accessible to various stakeholders. It helps users analyze past performances and adapt future strategy in light of a specific goal. Such as: what does the data say about my latest sales performances, and how can I improve them? Is the data revealing increased ROI from my advertising investments? etc.

Microsoft BI-related Products:

• SQL Server Management Studio (SSMS)
• SQL Server Integration Services (SSIS)
• SQL Server Analysis Services (SSAS)
• SQL Server Reporting Services (SSRS)
– Report Builder
• Power BI
•SQL Server Visual Studio

- Dr Ami Peiris, Infosys330 lecture notes, The University of Auckland
- Hugo Le Squeren, retrived from: