1 / 34

BDBI Radio

BDBI Radio. 13 Weekly tips on Microsoft SQL Server/BI/SharePoint Technologies http://www.BDBIRadio.com Week of 01/20/2013: 13 reasons to upgrade to SQL Server 2012. B aker’s D ozen B usiness I ntelligence Webcast Radio. BDBI Radio.

julinka
Download Presentation

BDBI Radio

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. BDBI Radio 13 Weekly tips on Microsoft SQL Server/BI/SharePoint Technologies http://www.BDBIRadio.com Week of 01/20/2013: 13 reasons to upgrade to SQL Server 2012 Baker’s Dozen Business Intelligence Webcast Radio

  2. BDBI Radio • Hosted by Kevin S. Goff, Microsoft SQL Server MVP and author of CoDe Magazine “Baker’s Dozen” Productivity Series • Weekly webcast - Sundays, 11 AM to 12:30 PM EST • Site: http://www.BDBIRadio.com • http://kevin_s_goff.typepad.com/kevin_s_goff_weblog/bdbi-radio/ • Program format: • First 60 minutes - 13 quick tips on different SQL/BI/SharePoint topics • In any week, tips might cover one topic (13 features in PowerPivot or 13 SQL Interview topics), or 13 random tips on anything from data warehousing to MDX/DAX programming, to maybe even a few .NET tips for scenarios when the .NET and BI worlds collide • Second 30 minutes, open Q/A (email me at BDBIRadio@gmail.com) • Webcast recorded, recordings will be available on website • Once in a while, a special guest interview BDBI Radio- 13 features in SQL 2012

  3. BDBI Radio • So why am I doing this? • From 2007 to 2012, I worked for a training company as a SQL/BI instructor, and taught hundreds of students. That company closed its doors, leaving some students in limbo regarding their quest to learn the SQL/BI tools. This is a way to help some of them continue their learning path. • I’ve been writing articles on .NET and SQL/BI topics for CoDe Magazine since 2004, and have always wanted to bring a live, interactive element to the content of my articles • I’ve been a Microsoft MVP since 2005 and I believe very strongly in helping other developers to ascend the learning curve • It will be fun! BDBI Radio- 13 features in SQL 2012

  4. BDBI Radio • About the webcast/radio show • I’m producing this myself • Streaming (for now) using Google Hangouts on Air and YouTube • Advantage: I can control format, I can customize as needed • Downside: I don’t create webcasts for a living, so the production might not be as tight or as sophisticated as a commercial webinar • Q/A session uses email: so email questions to BDBIRadio@gmail.com • I screen email questions on another machine before I present them on the webcast – so please, no profanity or inappropriate remarks (99.9% of people know never to do that anyway) • I might not be able to answer every question right away, so some I might answer off-line and then talk about in next week’s show BDBI Radio- 13 features in SQL 2012

  5. BDBI Radio • Today: 13 great features in SQL Server 2012 • Not going to delve heavily in any one feature: • Coming weeks/months, will cover these in more detail • That’s the way many of these webcast programs will go: some weeks will be overviews, other weeks a more deep-dive into a topic • SQL Server 2012 released to manufacturing in April 2012 • Service Pack 1 for SQL 2012 just released in late fall • SQL 2012 is, for some places, a TRUE game-changer BDBI Radio- 13 features in SQL 2012

  6. BDBI Radio • Today: 13 great features in SQL Server 2012 • SSIS/SSAS/SSRS now work with Visual Studio 2010 interface (SSDT) • New Columnstore Index • T-SQL enhancement: Sequence Objects • T-SQL enhancement: OFFSET/FETCH for Paging result sets • T-SQL enhancement: LAG/LEAD • T-SQL enhancement: Median and other analytic functions • FileTable object • Many new features in SSIS 2012 • Better SSRS integration with SharePoint (now a Service application) • New SSAS 2012 Tabular Model (alternative to OLAP) • Updated PowerPivot 2.0 • Improvements in the DAX Language • New PowerView report tool BDBI Radio- 13 features in SQL 2012

  7. BDBI Radio – (1) SSDT • Number 1: SSIS/SSAS/SSRS now work with Visual Studio 2010 interface (SSDT) • No longer need to use VS2010 and BIDS 2008 side by side • Can use VS 2010 • No longer called BIDS, it’s called SQL Server Data Tools BDBI Radio- 13 features in SQL 2012

  8. BDBI Radio – (2) Columnstore Index • Number 2: Columnstore index • A huge game changer for data warehouse applications • Will talk about in detail in a future show • Some queries more than 10x faster • I’ve talked about this in community events over the last year • If you want to get a head start on the slide deck: • http://kevin_s_goff.typepad.com/kevin_s_goff_weblog/2012/12/1282012-slide-decks-for-my-last-2-community-events-.html BDBI Radio- 13 features in SQL 2012

  9. BDBI Radio • New relational database index in SQL Server 2012 • Potentially Significant performance enhancements for data warehousing and data mart scenarios • (not really for OLTP databases, we’ll see why later) • Best for queries that scan/aggregate large sets of data • My opinion? One of the coolest things ever to occur in SQL Server, and a real game changer!!! • In a regular index, all indexed data from each row is kept together on a single page, and data in each column is spread across all pages in an index. • In a columnstore index, the data from each column is kept together so each data page contains data only from a single column New Features in SQL Server 2012 Return to TOC

  10. BDBI Radio • Highly compressed - Exploits similarity of data within a column • IO Statistics - dramatically reduces # of logical reads!!! • Smart IO and caching using read-ahead reads • Part of Microsoft’s VertiPaqtechnology – compression is roughly factor of 8 (and about twice as efficient as page compression) • Once posted, only READONLY • Best for data warehouse/data mart queries that scan and aggregate large amounts of data • Some queries might run at least 10x faster (or more) • Major areas that can benefit: • Queries and reports against Data Warehouses/Data Marts • Load from Data Warehouses/Data Marts into OLAP Cubes • SSAS OLAP Databases that use the ROLAP methodology or passthrough mode • New Analysis Services Tabular Model uses the Vertipaq engine New Features in SQL Server 2012 Return to TOC

  11. BDBI Radio • Columnstore versus row store • Columnstore index stores each column in a separate set of disk pages (versus storing multiple data rows per page) • In a columnstore index, only columns needed are fetched • Easier for SQL Server to compress redundant column data • Columnstore index uses Vertipaq technology found in PowerPivot • IO scan and buffer hit rates greatly improved New Features in SQL Server 2012 Return to TOC

  12. BDBI Radio • New Batch Processing Mode • Some of the more expensive operators (Hash Match for joins and aggregations) utilize a new execution mode called Batch Mode • Batch mode takes advantage of advanced hardware architectures, processor cache, and RAM , improves parallelism • Packets of about 1,000 rows are passed between operators, with column data represented as a vector. • Reduces CPU usage by factor of 10 (according to TechNet, sometimes up to a factor of 40!) • Much faster than row-mode processing • Other execution plan operators that use batch processing mode are bitmap filter, filter, compute scalar • General recommendation: include all columns in a columnStore index • Batch Mode restrictions: • Queries using OUTER JOIN directly against columnstore data, NOT IN <subquery>, UNION ALL won’t leverage batch mode, will revert to row processing mode New Features in SQL Server 2012 Return to TOC

  13. BDBI Radio • Syntax is very simple: just use new COLUMNSTORE keyword • 1 Columnstore index per table: cannot be clustered • Order of columns does not matter – typically you’ll reference all the columns from the table CREATE NONCLUSTERED COLUMNSTOREINDEX [IX_BPO_ColumnStore] ON [BigPurchaseOrderHeader] (VendorID, TotalDue) New Features in SQL Server 2012 Return to TOC

  14. BDBI Radio • Some (understandable) restrictions: • Cannot be clustered • Cannot act as a PK or FK • Does not include sparse columns • Can’t be used with tables that are part of Change Data Capture or FileStream data • Cannot be used with certain data types, such as binary, text/image, rowversion/timestamp, CLR data types (hierarchyID/spatial), nor with data types created with MAX keyword…e.g. varchar(max • Cannot be modified with an ALTER – must be dropped and recreated • Can’t participate in replication • It’s a readonly index • Cannot insert rows and expect columnstore index to be maintained. New Features in SQL Server 2012 Return to TOC

  15. BDBI Radio New Features in SQL Server 2012 Return to TOC

  16. BDBI Radio – (3-6) TSQL features • Sequence Generators • New Sever-side paging capability – ORDER BY with OFFSET and FETCH • 2 special WINDOW functions: LAG and LEAD • New Analytic functions: • Firstvalue/lastValue • percentRank and Cumulative Distance • Median functions New Features in SQL Server 2012 Return to TOC

  17. BDBI Radio • Sequence Generators • Table-independent identity values • Generate predictable number of values • No need to use uniqueidentifier if we only need uniqueness in the database • Can be used on four-byte (int) and eight-byte (bigint) data types • Can be used in a DEFAULT statement • Thread-safe • Restrictions – can’t be used in: • Subqueries • TOP (n) statements • CHECK CONSTRAINTs • Arguments to Aggregate functions New Features in SQL Server 2012 Return to TOC

  18. BDBI Radio • New Server-side Paging Construct: • ORDER BY with OFFSET and FETCH • Allows for retrieving a range of rows in a paging scenario (1000 rows in result set, grab rows 26-50, or 51-75, etc.) • Previous solution was to use ROW_NUMBER and a subquery New Features in SQL Server 2012 Return to TOC

  19. BDBI Radio • LAG and LEAD New Features in SQL Server 2012 Return to TOC

  20. BDBI Radio • Analytic Window functions (OVER clause enhancements): New Features in SQL Server 2012 Return to TOC

  21. BDBI Radio • New Analytic Window functions (OVER clause enhancements: • LAG/LEAD • Access data from previous/future rows in result set, based on order • No longer need for self-join or subquery/CTE • FIRST_VALUE, LAST_VALUE • Returns first value / last value over an ordered set of rows • Permits defining a custom RANGE • PERCENT_RANK – relative rank of row within ordered rows • CUME_DIST – relative position of a value within ordered rows • PERCENTILE_CONT, PERCENTILE_DISC – can be used to determine median, but with additional flexibility New Features in SQL Server 2012 Return to TOC

  22. BDBI Radio (7: FileTable) • FileTable object: a combination table and file system in 1! • Takes FileStream object to the next level (FileStream reads are now up to 5x faster in SQL Server 2012) • Mechanism to map folder system structure to a special database table • Can store files/documents in special database tables (“FileTables”) but access from Windows applications/Windows Explorer, as if the content was stored in the file system. • Opens the door for applications that need to maintain the content of a hierarchical-like folder structure, using SQL DML and other database operations • Necessary steps: • Configure an instance of SQL Server for FileStream access • Create an empty database (FileTableExampleDB)with FileStream capabilities • Alter the new database and set a FileStream directory name reference • Create a table with a reference to a specific directory (in this case, a folder that will hold images) • At the end, we can issue standard DML statements against the table in SQL Server, and it will affect the contents of the folder! New Features in SQL Server 2012 Return to TOC

  23. BDBI Radio • A few of the many uses for FileTable • Data driven approach to change file attributes • Ability to integrate Fulltext search! • Can create a folder structure of Word documents, and then search from within SQL Server • Must install Microsoft Filter Pack 2.0 and then SP1 • http://www.microsoft.com/download/en/details.aspx?id=17062 • http://support.microsoft.com/kb/2460041 • Check table sys.fulltext_document_types to make sure the file type you’re searching is included! • Must create FULLTEXT INDEX , using PK name of the filetable • Can query the filetable, which will turn around and search the documents! • Note the new GetFileNamespacePath function • SELECT stream_id, name, file_stream.GetFileNamespacePath(1,1) as FileLocation, * FROM dbo.tblResumeWordDocs where CONTAINS(file_stream, 'near(SQL,Server)') New Features in SQL Server 2012 Return to TOC

  24. BDBI Radio (8-SSIS ) • SQL Server Data Tools (Visual Studio 2010, WPF-based user interface) • Shared Connection managers • SSIS Project/Package Parameters • New Variable Expression Task (no need for scripts to increment/accumulate variables) • UNDO/REDO in editor • New SSIS Expression Features (REPLACENULL) • New ODBC Data Flow Source and Destination Components • Better handling of invalid metadata • New SSIS tasks to support Change Data Capture • New/improved deployment features and SSIS Management Dashboard for deployed packages • New Data Taps to dynamically “tap” into pipeline New Features in SQL Server 2012 Return to TOC

  25. BDBI Radio (9 – SSRS) • No new features in Report Authoring in SSDT (formerly BIDS) • Remember, SSRS 2008 and 2008R2 had many authoring enhancements • New features in SharePoint 2010 integration (requires SP1) • SSRS 2012 is now a regular service application inside of SharePoint 2010 – more seamless integration • Overall performance of SSRS reports inside SharePoint 2010 is improved • New feature in SSRS 2012 in SharePoint 2010: Data Alerts (alert an end user when data in a report changes) New Features in SQL Server 2012 Return to TOC

  26. BDBI Radio (10-Tabular SSAS) • Major new feature in SQL Server 2012 • Need to take a step back to look at the history of SQL Server Analysis Services and OLAP • 1998: Microsoft released OLAP Services with SQL Server 7 • 2000: Microsoft released Analysis Services with SQL Server 2000 • Better support for business dimensions • Limitations by today’s standards (only 1 fact table per cube), but an important release • 2005: Enhanced version of Analysis Services with SQL Server 2005 • UDM (Unified Dimension Model): an “API”, a “bridge” between users/developers and multidimensional sources • Support for more dimension types • Significant Data Mining functionality • To many, the first truly powerful version for creating OLAP solutions • 2008: Enhanced version of Analysis Services w/SQL Server 2008 • More data mining enhancements • Some MDX enhancements • By 2008, OLAP developers had great tools with SSAS to build multidimensional OLAP cubes New Features in SQL Server 2012 Return to TOC

  27. BDBI Radio • To many, Microsoft OLAP with Analysis Services is great • To others, some criticisms: • Some perceive a steep learning curve • The query language (MDX) can be difficult to learn • Different paradigm than relational databases • The market started to see competing tools that allowed developers/power users to create analytic databases with less perceived complexity (QlikView) • In the spring of 2010, Microsoft responded… New Features in SQL Server 2012 Return to TOC

  28. BDBI Radio • In spring of 2010, Microsoft introduced a very important tool with Excel 2010 / SQL Server 2008R2: PowerPivot • Free add-on for Excel: allowed users to take a relational database (or other data sources) and create the equivalent of a compressed star-schema cube (as a database inside of Excel), so that users could “slice and dice” the data in the same way they previously could with OLAP data • Integration with Analysis Services 2008R2 and SharePoint 2010, so that users could publish the combination pivot table/chart and underlying database to SharePoint, so that others could interface with it • Introduced the Vertipaq engine (now called xVelocity - same technology used for the columnstore index in SQL Server 2012) • Introduced DAX (Data Analysis Expressions) for creating calculations and expressions • A 1.0 product: some quirks, interface missing some features, some issues with SharePoint integration, but most felt would eventually become a game-changer • Not as powerful as a full-blown OLAP SSAS database solution, but provided a certain level of autonomy for power users to create certain analytic solutions using nothing more than Excel New Features in SQL Server 2012 Return to TOC

  29. BDBI Radio • So as of mid-2010, there were 2 ways to create analytic databases: • Microsoft Analysis Services (multidimensional OLAP) for larger, corporate and enterprise BI solutions • Very powerful • Still required steep learning curve to master • Usually requires strong knowledge of XMLA and MDX • PowerPivot (either with or without SharePoint 2010 integration) for personal or departmental BI solutions • Easy to use • Needed more functionality • DAX language could be as difficult as MDX • Needed access to this data from other reporting / client tools New Features in SQL Server 2012 Return to TOC

  30. BDBI Radio • SQL Server Analysis Services 2012 and the new Business Intelligence Semantic Model (BISM) • Three ways to create Business Intelligence database applications: • 1) Analysis Services multidimensional OLAP Model, created using SQL Server Data Tools (formerly BIDS). • Tool of choice for those who have already gone through the SSAS/OLAP learning curve, and are looking for the most advanced functionality • A few additions/changes, but largely unchanged from SQL Server 2008 • 2) New Analysis Services Tabular Model (also created using SQL Server Data Tools) • Not nearly as powerful as multidimensional OLAP, but might be sufficient for some corporate BI solutions • Can create an SSAS database using SSDT and then access in tools like SSRS or PerformancePoint services , just like you would with an OLAP database • Very fast for medium-large databases • Good for prototyping of what will eventually become OLAP multidimensional databases • 3) New updated version of PowerPivot 2.0 • Greatly enhanced over PowerPivot 1.0 • Better data designer, KPIs New Features in SQL Server 2012 Return to TOC

  31. BDBI Radio New Features in SQL Server 2012 Return to TOC

  32. BDBI Radio • Ability to create analytic databases using Excel • Termed by many as “Self-Service BI” (Business Intelligence) • Good for “Personal BI” and “small department BI” • Alternative to small OLAP cubes (size limit of 2 GB, no partitioning, no security roles, no support for ROLAP/DirectQuery) • End-users can mash up data from relational sources or other sources – “one big VLOOKUP” • Similar to new Tabular model, can create data relationships, calculations, KPIs, hierarchies… • Underlying database uses xVelocity engine (Vertipaq), based on columnstore data storage ( • Two parts to PowerPivot • Excel component, end users can share spreadsheets on a File Share • SharePoint integration component (can deploy Excel PowerPivot content to SharePoint) • New Diagram view and Hierarchy Designer • New Measure Grid to create measures • “Sort by” columns (date sorting was a pain in PowerPivot 1.0) • New functionality to create KPIs • Features from SSAS (perspectives, aggregations) • New DAX features (about 30 new functions) – DAX is greatly improved! • Ranking, TopN , Filtering, Lookups New Features in SQL Server 2012 Return to TOC

  33. BDBI Radio (13: PowerView) • New Data Exploration/Visualization Tool in SharePoint 2010, SP1 • Uses Silverlight plug-in • Initial working name was “Project Crescent”) • Self-service reporting tool • Power View is to Reporting Services as PowerPivot is to OLAP Databases – a way for “power users” to get a good portion of the functionality normally obtained through developer tools • Only works in SharePoint Enterprise • Only works with Tabular mode, not multidimensional mode • “Storyboarding” capabilities through report snapshots • Can export to PowerPoint, and still interact with report filters New Features in SQL Server 2012 Return to TOC

  34. Recommended reading • I’ve written some articles in CoDe Magazine on SQL 2012 • 2 part series on Columnstore index, T-SQL Features, and SSIS Features • http://code-magazine.com/articleprint.aspx?quickid=1203051&printmode=true • http://code-magazine.com/articleprint.aspx?quickid=1206021&printmode=true • Look for more articles on SQL 2012 later in the year • I ran a 13 part webinar series on new features in SQL 2012 • Link to the recordings • Some outstanding new books on SQL 2012: BDBI Radio- 13 features in SQL 2012

More Related