![Page 1: What is the BI DBA? - Squarespacestatic.squarespace.com/static/...WhatIsABIDBA_JorgeSegarra.pdf · What is the BI DBA? Jorge Segarra ... Oracle, DB2, Office, etc. Security ... –](https://reader031.vdocuments.us/reader031/viewer/2022022601/5b5037827f8b9a396e8e0179/html5/thumbnails/1.jpg)
What is the BI DBA?
Jorge Segarra
Sr. DBA Consultant, SQL Server MVP
Sqlchicken.com | @sqlchicken
![Page 2: What is the BI DBA? - Squarespacestatic.squarespace.com/static/...WhatIsABIDBA_JorgeSegarra.pdf · What is the BI DBA? Jorge Segarra ... Oracle, DB2, Office, etc. Security ... –](https://reader031.vdocuments.us/reader031/viewer/2022022601/5b5037827f8b9a396e8e0179/html5/thumbnails/2.jpg)
MAKING BUSINESS INTELLIGENT www.pragmaticworks.com
About Me
![Page 3: What is the BI DBA? - Squarespacestatic.squarespace.com/static/...WhatIsABIDBA_JorgeSegarra.pdf · What is the BI DBA? Jorge Segarra ... Oracle, DB2, Office, etc. Security ... –](https://reader031.vdocuments.us/reader031/viewer/2022022601/5b5037827f8b9a396e8e0179/html5/thumbnails/3.jpg)
MAKING BUSINESS INTELLIGENT www.pragmaticworks.com
What Is a BI DBA?
![Page 4: What is the BI DBA? - Squarespacestatic.squarespace.com/static/...WhatIsABIDBA_JorgeSegarra.pdf · What is the BI DBA? Jorge Segarra ... Oracle, DB2, Office, etc. Security ... –](https://reader031.vdocuments.us/reader031/viewer/2022022601/5b5037827f8b9a396e8e0179/html5/thumbnails/4.jpg)
What is a BI DBA?
• Still a DBA!
• Base Skills Configuration
Backup/Recovery
Security
Performance Tuning
Troubleshooting
Expectations
Configure/Deploy
Troubleshoot
Monitor
![Page 5: What is the BI DBA? - Squarespacestatic.squarespace.com/static/...WhatIsABIDBA_JorgeSegarra.pdf · What is the BI DBA? Jorge Segarra ... Oracle, DB2, Office, etc. Security ... –](https://reader031.vdocuments.us/reader031/viewer/2022022601/5b5037827f8b9a396e8e0179/html5/thumbnails/5.jpg)
Business Intelligence Stack
• Integration Services (SSIS), Analysis Services
(SSAS), Reporting Services (SSRS) Configuring
Deploying Solutions
Securing
Maintaining (Backups/Restores)
• SharePoint Different beast!
SharePoint for the DBA webcast – Bradley Schacht
![Page 6: What is the BI DBA? - Squarespacestatic.squarespace.com/static/...WhatIsABIDBA_JorgeSegarra.pdf · What is the BI DBA? Jorge Segarra ... Oracle, DB2, Office, etc. Security ... –](https://reader031.vdocuments.us/reader031/viewer/2022022601/5b5037827f8b9a396e8e0179/html5/thumbnails/6.jpg)
MAKING BUSINESS INTELLIGENT www.pragmaticworks.com
Tools of the Trade
![Page 7: What is the BI DBA? - Squarespacestatic.squarespace.com/static/...WhatIsABIDBA_JorgeSegarra.pdf · What is the BI DBA? Jorge Segarra ... Oracle, DB2, Office, etc. Security ... –](https://reader031.vdocuments.us/reader031/viewer/2022022601/5b5037827f8b9a396e8e0179/html5/thumbnails/7.jpg)
Know Your Tools
• Business Intelligence Developer Studio (BIDS)
2005-2008 R2
Version specific!
Development
• SQL Server Data Tools (SSDT)
2012+
Developer-focused
Development
• SQL Server Management Studio (SSMS)
2005-2012
Administration tool
![Page 8: What is the BI DBA? - Squarespacestatic.squarespace.com/static/...WhatIsABIDBA_JorgeSegarra.pdf · What is the BI DBA? Jorge Segarra ... Oracle, DB2, Office, etc. Security ... –](https://reader031.vdocuments.us/reader031/viewer/2022022601/5b5037827f8b9a396e8e0179/html5/thumbnails/8.jpg)
MAKING BUSINESS INTELLIGENT www.pragmaticworks.com
Integration Services
![Page 9: What is the BI DBA? - Squarespacestatic.squarespace.com/static/...WhatIsABIDBA_JorgeSegarra.pdf · What is the BI DBA? Jorge Segarra ... Oracle, DB2, Office, etc. Security ... –](https://reader031.vdocuments.us/reader031/viewer/2022022601/5b5037827f8b9a396e8e0179/html5/thumbnails/9.jpg)
Integration Services
• Integration Services (SSIS) Most common BI service offering
• Deploy/Configure
Environment Configs
• Environment Variables
• Configuration tables
• XML files
![Page 10: What is the BI DBA? - Squarespacestatic.squarespace.com/static/...WhatIsABIDBA_JorgeSegarra.pdf · What is the BI DBA? Jorge Segarra ... Oracle, DB2, Office, etc. Security ... –](https://reader031.vdocuments.us/reader031/viewer/2022022601/5b5037827f8b9a396e8e0179/html5/thumbnails/10.jpg)
Integration Services
• Service Accounts/Permissions SQL Agent service account
Needs access to sources/destinations/resources
Additional rights as needed (e.g. execute procs)
• Drivers Oracle, DB2, Office, etc.
Security
Accounts/Rights in other systems
32/64-bit
![Page 11: What is the BI DBA? - Squarespacestatic.squarespace.com/static/...WhatIsABIDBA_JorgeSegarra.pdf · What is the BI DBA? Jorge Segarra ... Oracle, DB2, Office, etc. Security ... –](https://reader031.vdocuments.us/reader031/viewer/2022022601/5b5037827f8b9a396e8e0179/html5/thumbnails/11.jpg)
Integration Services
• Deployments Methods
Package store/msdb
File system
Project (2012)
File Structures
Options
Single package at a time (manual)
Deployment Manifests
Project Deployments
Source Control (helpful!)
Backups
Msdb
File system
Database (2012)
![Page 12: What is the BI DBA? - Squarespacestatic.squarespace.com/static/...WhatIsABIDBA_JorgeSegarra.pdf · What is the BI DBA? Jorge Segarra ... Oracle, DB2, Office, etc. Security ... –](https://reader031.vdocuments.us/reader031/viewer/2022022601/5b5037827f8b9a396e8e0179/html5/thumbnails/12.jpg)
Integration Services
• Databases Script it out!
Security AD Groups helpful
Mappings
• Automation SQL Agent Jobs
Job owner
Run as (default = Agent service) Automating SSIS Execution Using SQL Server Agent (video link)
3rd Party Schedulers
Job ownership
Package roles
Msdb roles – Agentuser, ssisuser, etc.
![Page 13: What is the BI DBA? - Squarespacestatic.squarespace.com/static/...WhatIsABIDBA_JorgeSegarra.pdf · What is the BI DBA? Jorge Segarra ... Oracle, DB2, Office, etc. Security ... –](https://reader031.vdocuments.us/reader031/viewer/2022022601/5b5037827f8b9a396e8e0179/html5/thumbnails/13.jpg)
Integration Services
• Troubleshooting Learn SSIS!
Basics Variables/Expressions
Package Configurations
Free learning resources PASS Virtual Chapter
Webinars
Websites / Bloggers
Troubleshooting Steps
Agent job errors
Logging/Auditing in package
Native
3rd Party
Monitoring SQL Server 2012 (limited)
3rd party tools
Notifications Alert on fail (job)
Build into package
![Page 14: What is the BI DBA? - Squarespacestatic.squarespace.com/static/...WhatIsABIDBA_JorgeSegarra.pdf · What is the BI DBA? Jorge Segarra ... Oracle, DB2, Office, etc. Security ... –](https://reader031.vdocuments.us/reader031/viewer/2022022601/5b5037827f8b9a396e8e0179/html5/thumbnails/14.jpg)
MAKING BUSINESS INTELLIGENT www.pragmaticworks.com
Analysis Services
![Page 15: What is the BI DBA? - Squarespacestatic.squarespace.com/static/...WhatIsABIDBA_JorgeSegarra.pdf · What is the BI DBA? Jorge Segarra ... Oracle, DB2, Office, etc. Security ... –](https://reader031.vdocuments.us/reader031/viewer/2022022601/5b5037827f8b9a396e8e0179/html5/thumbnails/15.jpg)
Analysis Services
• Deployments Scripted (XMLA)
Check data sources
Verify permissions
Source Control (helpful!)
Processing
Dimensions and Facts
Queries source database
Developer may include processing task in ETL
Security
Know your roles!
![Page 16: What is the BI DBA? - Squarespacestatic.squarespace.com/static/...WhatIsABIDBA_JorgeSegarra.pdf · What is the BI DBA? Jorge Segarra ... Oracle, DB2, Office, etc. Security ... –](https://reader031.vdocuments.us/reader031/viewer/2022022601/5b5037827f8b9a396e8e0179/html5/thumbnails/16.jpg)
Analysis Services
• Backup/Restores It Depends!
Small cubes (<10GB) may “not need backups”
May be faster to just redeploy
TEST!
Configuring SSAS is different
Memory by percentage
Be careful when sharing (Setting Memory Limits blog)
Low, Hard, Total – SSAS Operations Guide
Drillthrough Limits (Advanced)
Monitoring
DMVs
Perfmon counters, Extended Events, Profiler
![Page 17: What is the BI DBA? - Squarespacestatic.squarespace.com/static/...WhatIsABIDBA_JorgeSegarra.pdf · What is the BI DBA? Jorge Segarra ... Oracle, DB2, Office, etc. Security ... –](https://reader031.vdocuments.us/reader031/viewer/2022022601/5b5037827f8b9a396e8e0179/html5/thumbnails/17.jpg)
MAKING BUSINESS INTELLIGENT www.pragmaticworks.com
Reporting Services
![Page 18: What is the BI DBA? - Squarespacestatic.squarespace.com/static/...WhatIsABIDBA_JorgeSegarra.pdf · What is the BI DBA? Jorge Segarra ... Oracle, DB2, Office, etc. Security ... –](https://reader031.vdocuments.us/reader031/viewer/2022022601/5b5037827f8b9a396e8e0179/html5/thumbnails/18.jpg)
Reporting Services
• Configure– Native vs SharePoint
– Still SQL database
– 2008+ no IIS requirement
– Performance Optimizations (SQLCAT)
• Drivers Oracle, DB2, Office, etc.
Security
Accounts/Rights in other systems
32/64-bit
![Page 19: What is the BI DBA? - Squarespacestatic.squarespace.com/static/...WhatIsABIDBA_JorgeSegarra.pdf · What is the BI DBA? Jorge Segarra ... Oracle, DB2, Office, etc. Security ... –](https://reader031.vdocuments.us/reader031/viewer/2022022601/5b5037827f8b9a396e8e0179/html5/thumbnails/19.jpg)
Reporting Services
• Data Sources– KERBEROS!!!!
• Multi-hop configuration
– My Kerberos Checklist (Adam Saxton, Microsoft)
– Still SQL database
– 2008+ no IIS requirement
– Performance Optimizations (SQLCAT)
• Security Internet Explorer (run as admin)
SSRS Roles
Folder Permissions (Inheritance)
Web vs SSMS
![Page 20: What is the BI DBA? - Squarespacestatic.squarespace.com/static/...WhatIsABIDBA_JorgeSegarra.pdf · What is the BI DBA? Jorge Segarra ... Oracle, DB2, Office, etc. Security ... –](https://reader031.vdocuments.us/reader031/viewer/2022022601/5b5037827f8b9a396e8e0179/html5/thumbnails/20.jpg)
Reporting Services
• Deployment– One at a time + data sources
– All or nothing (BIDS)
– 3rd party
• Backups SQL backups (databases)
Source control
![Page 21: What is the BI DBA? - Squarespacestatic.squarespace.com/static/...WhatIsABIDBA_JorgeSegarra.pdf · What is the BI DBA? Jorge Segarra ... Oracle, DB2, Office, etc. Security ... –](https://reader031.vdocuments.us/reader031/viewer/2022022601/5b5037827f8b9a396e8e0179/html5/thumbnails/21.jpg)
MAKING BUSINESS INTELLIGENT www.pragmaticworks.com
Contact Me
• Email: [email protected]
• Twitter: http://twitter.com/sqlchicken
• Blog: http://sqlchicken.com
• Bat Signal, #sqlhelp
![Page 22: What is the BI DBA? - Squarespacestatic.squarespace.com/static/...WhatIsABIDBA_JorgeSegarra.pdf · What is the BI DBA? Jorge Segarra ... Oracle, DB2, Office, etc. Security ... –](https://reader031.vdocuments.us/reader031/viewer/2022022601/5b5037827f8b9a396e8e0179/html5/thumbnails/22.jpg)
ServicesSpeed development through training, and rapid development services from Pragmatic Works.
ProductsBI products to covert to a Microsoft BI platform and simplify development onthe platform.
FoundationHelping those who do not have themeans to get into information technologyachieve their dreams.
For more information…
Jorge Segarra