1 / 20

SQL Server Analysis Services Fundamentals

SQL Server Analysis Services Fundamentals. Dave Bland daveb8782@gmail.com. Thank you Sponsors. Dave Bland 14 years DBA Experience Teaching SQL Server since 1999 Frequent SQL Saturday presenter in Midwest SQL Server Instructor at Harper College, Palatine, IL

dean
Download Presentation

SQL Server Analysis Services Fundamentals

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL Server Analysis Services Fundamentals Dave Bland daveb8782@gmail.com

  2. Thank you Sponsors

  3. Dave Bland • 14 years DBA Experience • Teaching SQL Server since 1999 • Frequent SQL Saturday presenter in Midwest • SQL Server Instructor at Harper College, Palatine, IL • Currently supervise a team of DBAs at Stericycle • DBA consultant for Einstein Technology Solutions, Lombard, IL About Me

  4. Certifications

  5. SSAS Fundamentals • SSAS Security • Cube Processing • Database Backups • How to read the data Agenda

  6. “Analysis Services is an online analytical data engine used in decision support and business intelligence (BI) solutions, providing the analytical data for business reports and client applications such as Excel, Reporting Services reports, and other third-party BI tools” - Microsoft.com • SSAS can be used for data analysis • Reports • Trending • Predictive Analytics What is SSAS and Why Use it

  7. Microsoft SQL Server Integration Services Microsoft SQL Azure and the WindowsAzureMarketplace 1011000110 DataWarehousing SQLServerDatabaseEngine SQL Server Data Quality Services SQL Server Master Data Services SQL Server Analysis Services SQL Server Reporting Services Microsoft PowerPivot Technologies Business Intelligence Microsoft Business Intelligence Technologies • Microsoft Excel • Data Mining Add-In • PowerPivot Add-In • MDS Add-In Interactive data analysis  Microsoft SharePoint Server Power View Interactive data visualizations Reports, KPIs, and Dashboards

  8. OLAPvsOLTP

  9. Database • Data Source • Data Source View • Cube • Role AnalysisServicesProject

  10. Measure • Dimension • Attributes • Members AnalysisServicesTerminology

  11. Used to aggregate measures by time • Year over Year considerations • Holidays • Generally create for years in future • Can use the wizard, in Data Tools TimeDimension

  12. Two Types • Multidimensional • Tabular ModelTypes

  13. Reporting and Analysis • Process • Data Models • Data Sources AnalysisServicesTerminology • Data • Warehouse •  ETL

  14. Types of Processing • Full • Incremental • Can process individual parts of the cube • Uses XMLA • SQL Agent Job • SSIS task • SQL Server Management Studio • Let SSMS generate script Processing a Cube

  15. Is Role based • Uses Windows Authentication SSAS Security

  16. Backup Database, not just the cube • Determine if there is a need to backup the database <Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine"> <Object> <DatabaseID>AdventureWorksCube</DatabaseID> </Object> <File>\\AWCorp\backups\StorageWorks\AW\SSAS\AWBackup.abf</File> </Backup> Backup Database

  17. Dimensions • Gender • Postal Code • Age • Type of Game • Section of boat • Day of Week Facts • Coin In • Win\Loss • Jackpots • Buy In • Hour of Day • Day of Year • Year • Month Whatwouldwewanttoknowaboutaplayer

  18. Is a project type • Data Source • Data Source View • Cubes • Dimensions • Roles DataTools

  19. SQL Server Management Studio • Excel • MDX • DAX • Reporting Services • Third Party Reporting Tools HowtogettheData

  20. Thank You Questions?

More Related