Business Itelligence

Microsoft Business Intelligence
mit SQL Server
BI-Komponenten von Microsoft SQL Server
zur Abbildung eines automatisierten Berichtswesens
Datum: 05.06.2014
Referent: Thomas Groß
Thank you for the last session
Team Foundation Server
(TFS)
Einführung – Begriffsbestimmung
BI = Business Intelligence
Der Begriff Business Intelligence bezeichnet ein Verfahren und
einen Prozess zur systematischen Analyse (Sammlung,
Auswertung und Darstellung) von Daten in elektronischer
Form.
http://de.wikipedia.org/wiki/Business-Intelligence
Einführung – Schritt für Schritt
Der Vortrag zeigt Schritt für Schritt die Abbildung eines automatisierten
Berichtswesens mit Hilfe der Microsoft BI-Tools.
1. Schritt: Data-Warehouse erstellen (SSIS)
Aus einer relationalen OLTP(OnLine-Transaction-Processing)-Datenbank zur Datenerfassung wird mittels
SSIS-Datentransfer ein Data-Warehouse gefüllt.
Dies entspricht dem klassischen ETL(Extraction-Translation-Load)-Prozess.
2. Schritt: OLAP-Datenbank erstellen (SSAS)
Die Daten der Data-Warehouse-Datenbank werden mit Analysis Services in mehrdimensionale Cubes
einer OLAP-Datenbank (OnLine-Analytical-Prozessing) überführt.
3. Schritt: Berichte erstellen (SSRS)
Mit Reporting Services werden sowohl die relationale Daten aus dem Data-Warehouse als auch
mehrdimensionale Daten aus den Cubes der OLAP-Datenbank als Berichte gerendert.
Die Auswertung der OLAP-Cubes ist auch mit Excel möglich.
4. Schritt: Berichte in SharePoint Portal veröffentlichen
Die Berichte werden in einem SharePoint-Portal veröffentlicht.
Einführung – ssis ssas ssrs - OLTP OLAP DWH Cube Report - sql mdx
OLTP
mysql
OLAP
Ora
ssis
SQL
SQL
SQL
SQL
DWH
Cube
ssas
ssrs
Einführung – BI Tools ala Microsoft
Zu den BI-Komponenten von SQL Server zählen:
SSIS – SQL Server Integration Services zum Datentransfer (ETL Tool)
SSAS – SQL Server Analysis Services zum Erstellen von OLAP-Cubes
(Mehrdimensionale Datenbank)
SSRS – SQL Server Reporting Services zur Berichterstellung (Reporting
Server Tool)
+ SharePoint Server!!!
BUSINESS INTELLIGENCE (BI) IS ONE BIG AREA!
BIG DEFINITIONS, BIG NEW PHILOSOPHY, BIG DATA, BIG SERVERS, ..., BIG MANAGEMENT DECISIONS
Einführung – zur Person - join and share
... Let‘s start
join and share GmbH
Entwicklung kundenspezifischer Anwendungen
Microsoft Silver Partner (Data Platform)
Technologien
• SQL Server
• .net / asp Programmierung
• Business Intelligence
• Share Point
• Office Programmierung
Thomas Groß (MCDBA) entwickelt seit über 25 Jahren datenbankbasierte Anwendungen. Besonderen Wert
legt er auf den Entwurf und das Design von Anwendungen, um eine optimale Abbildung der Prozesse und
eine hohe Usability zu gewährleisten. Für die Implementierung der Software nutzt er MS SQL Server, das
.NET-Framework, Office VBA, SharePoint und die BI-Komponenten von SQL Server.
Vor über 10 Jahren gründete er die join and share GmbH. Das Team von join and share pflegt langjährige
und vertrauensvolle Beziehungen zu seinen Kunden.
SSIS
ETL-TOOL
(FÜR DATA-WAREHOUSE)
SSIS
Sql Server Integration Services
SSIS - Definition
http://technet.microsoft.com/de-de/library/ms169917.aspx
SSIS-Lernprogramm: Erstellen eines einfachen ETL-Pakets
Microsoft SQL Server Integration Services(SSIS) ist eine Plattform zum
Erstellen leistungsfähiger Datenintegrationslösungen, z.B. von ETLPaketen (Extraction, Transformation und Load) für das Data Warehousing.
SSIS enthält grafische Tools und Assistenten zum Erstellen und Debuggen
von Paketen; Tasks zum Ausführen von Workflowfunktionen wie z. B. FTPVorgänge, Ausführen von SQL-Anweisungen und Senden von E-Mails;
Datenquellen und Ziele zum Extrahieren und Laden von Daten;
Transformationen zum Bereinigen, Aggregieren, Zusammenführen und
Kopieren von Daten; einen Verwaltungsdienst, den Integration ServicesDienst zum Verwalten der Paketausführung und -speicherung; und
Anwendungsprogrammierschnittstellen (APIs, Application Programming
Interfaces) zum Programmieren des Integration Services-Objektmodells.
SSIS - Demo
http://karthikeyananbarasan.files.wordpress.com/2011/10/free-ebook-sql-server-integration-servicesssis-step-by-step-version-2-0.pdf
SQL Server Integration Services (SSIS)
Demo
+
Data Flow Control contra SQL Task
Nächtlicher SQL Agent Job zur Ausführung des SSIS Pakets um das Data-Warehouse zu füllen
SSIS - Lernmaterial
Weitere Steuerelemente wie Schleifen, Filter, ftp + mail Tasks siehe
PDF Ebook (0 € aber super)
SQL Server Integration Services (SSIS) – Step by Step Tutorial
http://karthikeyananbarasan.files.wordpress.com/2011/10/free-ebooksql-server-integration-services-ssis-step-by-step-version-2-0.pdf
SSIS – SSAS - DWH
BREAK: Exkurs Date-Warehouse
Wir brauchen noch ein Data-Warehouse
http://www.codeproject.com/Articles/652108/Create-First-Data-WareHouse
Create First Data WareHouse
X-Mart is having different malls in our city, where daily sales take place for
various products. Higher management is facing an issue while decision
making due to non availability of integrated data they can’t do study on
their data as per their requirement. So they asked us to design a system
which can help them quickly in decision making and provide Return on
Investment (ROI).
Demo
Z:\Offline\dotnetUserGroup_BIToolsDesSQLServers\DataWareHouseSQLScript.sql
SSIS – SSAS - DWH
Management Anforderung an BI Lösung
Let us first identify the requirement from management about their
requirements.
• Need to see daily, weekly, monthly, quarterly profit of each store.
• Comparison of sales and profit on various time periods.
• Comparison of sales in various time bands of the day.
• Need to know which product has more demand on which location?
• Need to study trend of sales by time period of the day over the week,
month, and year?
• On what day sales is higher?
• On every Sunday of this month, what is sales and what is profit?
• What is trend of sales on weekday and weekend?
• Need to compare weekly, monthly and yearly sales to know growth
and KPI?
SSIS – SSAS - DWH
Das „Sternschema“
SSAS
CUBES
SSAS
Sql Server Analysis Services
SSAS - Definition
http://technet.microsoft.com/de-de/library/hh231701.aspx
Analysis Services-Lernprogramme (SSAS)
Dieses Lernprogramm vermittelt die wesentlichen Fähigkeiten und
Konzepte hinter mehrdimensionaler Modellierung in SQL ServerDatentools (SSDT). Nach dem Abschluss verfügen Sie über eine
Cubedatenbank auf Basis von Adventure Works-Daten, auf die Sie über
Excel, Reporting Services oder eine andere Clientanwendung zugreifen
können, die eine Verbindung mit Analysis Services herstellt.
OLAP
DWH
Cube
ssas
SSAS – OLTP contra OLAP
Online Transaction Processing (OLTP)
Online Analytical Processing (OLAP)
Designed to support Daily DML Operations of
your application
Holds daily Latest Transactional Data related
to your application
Data stored in normalized format
Databases size is usually around 100 MB to
100 GB
Designed to hold historical data for analyses
and forecast business needs
Data is consistent up to the last update that
occurred in your Cube
Data stored in denormalized format
Databases size is usually around 100 GB to a
few TB
Used by users who are associated with the
Used by normal users
decision making process, e.g., Managers, CEO.
CPU, RAM, HDD space requirement is less.
CPU, RAM, HDD space requirement is higher.
Query response may be slower if the amount Query Response is quicker, management can
of data is very large, it can impact the
do Trend analysis on their data easily and
reporting performance.
generate quicker reports.
T-SQL language used for query
MDX is used for querying on OLAP Cube
What is the difference between OLAP and OLTP?
A
http://www.codeproject.com/Articles/658912/Create-First-OLAP-Cube-in-SQL-Server-Analysis-Serv
SSAS – Cubes
Der Würfel
SSAS - Demo
http://www.codeproject.com/Articles/658912/Create-First-OLAP-Cube-inSQL-Server-Analysis-Serv
Create First OLAP Cube in SQL Server Analysis Service
Demo
Datenquellen, Datenviews, Cube, Faktentabellen, Dimensionstabellen,
Attribute, Hierarchien
+ Sortierung der Monate über KeyColums
+ natural hierarchy  User-Defined Hierarchies = Performace
über KeyColums und Attributrelations
Performance! https://intelligentsql.wordpress.com/tag/natural-hierarchy/
+ Excel
z:\Offline\dotnetUserGroup_BIToolsDesSQLServers\SalesDataAnalysis.xlsx
SSAS - Lernmaterial
Youtube Videos
Serie: Analyis Services – 01 ... 11
https://www.youtube.com/watch?v=pyUsp8qc7hg&list=PL7706561114B01D0D
Youtube
Analysis Services – 01
Analysis Services – 02
Analysis Services – 03
Analysis Services – 04
Analysis Services – 05
Analysis Services – 06
Analysis Services – 07
Analysis Services – 08
Analysis Services – 09
Analysis Services – 10
Analysis Services – 11
Content
Prerequisite Guide
Data Source Creation
Data Source Views
Cube Creation
Dimension Fundamentals
Dimension Hierarchies
Dimension Attribute Relationships
Dimension Storage
Dimension Discretization
Parent/Child Dimension Hierachies
Star and Snowflake Schemas
SSRS
REPORTING
SSRS
Sql Server Reporting Services
SSRS - Definition
http://technet.microsoft.com/de-de/library/ms167305.aspx
Erstellen eines einfachen Tabellenberichts (SSRS-Lernprogramm)
Dieses Lernprogramm hilft Ihnen, mit dem Berichts-Designer einen
einfachen Tabellenbericht zu erstellen, der auf der AdventureWorks2012 Datenbank basiert. Sie können die Berichte auch mithilfe des BerichtsGenerators oder des Berichts-Assistenten erstellen. In diesem
Lernprogramm erstellen Sie ein Berichtsprojekt, richten
Verbindungsinformationen ein, definieren eine Abfrage, fügen einen
Tabellendatenbereich hinzu und zeigen den Bericht in der Vorschau an.
+ Features over Features
+ OLAP Cube als data source
+ deployment
SSRS - Demo
Demo
ssrs tutorials
lesson 1
ssrs tutorials
lesson 2
ssrs tutorials
lesson 4
ssrs tutorials
lesson 5
ssrs tutorials
lesson 6
ssrs tutorials
lesson 7
ssrs tutorials
lesson 8
In this tutorial I use Northwind database to create
an SSRS report. The report displays suppliers and
for each one all the products they supply. We will
see how to create a data source, data set, table,
expressions, parameters and indicators.
This demo gives a brief description of how to make
your first SSRS report using the Report wizard.
This tutorial shows you how to create a basic SSRS
report using the toolbox and without using the
wizard.
This demo shows you how to create a
parameterized SSRS report.
This demo shows you how to create a cascading
parameters ssrs 2008 report
This demo gives you a brief overview of using SSRS
2008 R2 expressions.
This demo gives you an overview of how to create
SSRS Matrix reports. We use the Adventure works
Database in our demo.
CUBE db
Creating Basic Reports
from an SQL Server
Analysis Services
SQL Server Report Builder (User Tool)
OLAP Cube
+ ssrs with mdx part 1 ... 11
OLTP
Northwind
Einführung data sorce, data set,
table
sql
AdventureWorks
Erstellen mit Wizard
AdventureWorks
Erstellen ohne Wizard
Parameter
Cascading Parameter
Expressions
Matrix
(OLTP, AdventureWorks)
Adventure Works DW (Cube)
Tool Report Builder
mdx
SSRS - Lernmaterial
Youtube Videos
sql
Serie: OLTP Reports; Keywords: ssrs tutorials lesson 1 .. 11
SQL db
ssrs tutorials lesson 1
Content
In this tutorial I use Northwind database to create an SSRS report. The report displays
suppliers and for each one all the products they supply. We will see how to create a data
source, data set, table, expressions, parameters and indicators.
ssrs tutorials lesson 2
ssrs tutorials lesson 5
This demo gives a brief description of how to make your first SSRS report using the Report
wizard.
This tutorial shows you how to create a basic SSRS report using the toolbox and without using
the wizard.
This demo shows you how to create a parameterized SSRS report.
ssrs tutorials lesson 6
This demo shows you how to create a cascading parameters ssrs 2008 report
Cascading Parameter
ssrs tutorials lesson 7
This demo gives you a brief overview of using SSRS 2008 R2 expressions.
Expressions
ssrs tutorials lesson 8
This demo gives you an overview of how to create SSRS Matrix reports. We use the
Adventure works Database in our demo.
This demo gives you a brief overview on how to create child groups and drilldowns. We also
look at how to control and toggle the ...
This tutorilas gives you an overview of how to create an SSRS subreport. We look at passing
parameters from a main report to a ..
This demo gives you an overview on how to create basic graphs and charts using SSRS 2008
R2. This demo shows you ways to ...
Matrix
(OLTP, AdventureWorks)
Drillup and -down
ssrs tutorials lesson 4
ssrs tutorials lesson 9
ssrs tutorials lesson 10
ssrs tutorials lesson 11
Remark
OLTP
Northwind
Einführung data sorce, data
set, table
AdventureWorks
Erstellen mit Wizard
Erstellen ohne Wizard
Parameter
Subreports
Graphs
SSRS - Lernmaterial
Youtube Videos
sql
Serie: OLTP Reports; Keywords: Services (SSRS) Part 1 .. 23
Reporting Services (SSRS) Part 1
Reporting Services (SSRS) Part 2
Reporting Services (SSRS) Part 3
Reporting Services (SSRS) Part 4
Reporting Services (SSRS) Part 5
Reporting Services (SSRS) Part 6
Reporting Services (SSRS) Part 7
Reporting Services (SSRS) Part 8
Reporting Services (SSRS) Part 9
Reporting Services (SSRS) Part 10
Reporting Services (SSRS) Part 11
Reporting Services (SSRS) Part 12
Reporting Services (SSRS) Part 13
Reporting Services (SSRS) Part 14
Reporting Services (SSRS) Part 15
Reporting Services (SSRS) Part 16
Reporting Services (SSRS) Part 17
Reporting Services (SSRS) Part 18
Reporting Services (SSRS) Part 19
Reporting Services (SSRS) Part 20
Reporting Services (SSRS) Part 21
Reporting Services (SSRS) Part 22
Reporting Services (SSRS) Part 23
Getting Started with a New Project
Data Sources and Datasets
Keeping Header Rows Visible
Working with Tables
Controlling the number of rows per page
Grouping in Tables
Calculated Fields
Conditional Formatting in Tables
Indicators
Gauges
Basic Parameters
Drop Down List Param
Null Values in Parameters
Cascading Parameters
Matrix
Lists
Linked Reports
Charts
Subreports
Basic Maps
Pivot Table (Dynamic Matrix)
Document Maps
Report Variables and Group Variables
SSRS - Lernmaterial
Youtube Videos
Video: OLAP Reports (Einführung)
Creating Basic Reports from an SQL Server Analysis Services
https://www.youtube.com/watch?v=pQxkoTsT19w
Inhalt
SQL Server Report Builder (User Tool)
OLAP Cube
mdx
SSRS - Lernmaterial
Youtube Videos
Serie: OLAP Reports; Keywords: ssrs with mdx part 1 .. 11
ssrs with mdx part 1
ssrs with mdx part 2
ssrs with mdx part 3
ssrs with mdx part 4
ssrs with mdx part 5
ssrs with mdx part 6
ssrs with mdx part 7
ssrs with mdx part 8
ssrs with mdx part 9
ssrs with mdx part 10
ssrs with mdx part 11
Sonstige Datenbank (CUBE): Cube 4 Reports
Simple Report with Query Wizard
With mdx Query Builder
With mdx Query Builder
Drillup and –down
Simple Parameter
Writing own mdx Query
Calculated fields
groups
Matrix
Parameter
Multiple Parameter Values in mdx
Multiple Parameter in mdx
Cascading Parameters
Null Values in Parameters Mdx IsEmpty
Gouping
Drillup and –down
Drill throw
Pass Parameter to form
Subreports
mdx
SharePoint
PORTAL
SHAREPOINT
SharePoint
Deployment to SharePoint
SharePoint ist das Unternehmensportal
SharePoint ist das Management Cockpit
SharePoint ist die BI-Plattform
und mehr? TFS ist das Tool für Entwickler / thanks for the last TFS session
Business Intelligence in 100 Sekunden