Presentation Transcript

1. Business Intelligence and Data Visualization Enhancements

   Emrah Uslu MVP, Project Manager emrah.uslu@tcm.com.tr Osman Çokakoğlu MVP osman.cokakoglu@tcm.com.tr
2. Sponsors Main Gold Bronze Media Swag
3. Agenda PowerQuery(Data Explorer) PowerMap(GeoFlow) PolyBase in PDW PowerView +
4. Easy Access to Data, Big and Small Key features Search, access and shape Simplify access to public and corporate data Easily preview, shape, and format your data Project code name “Data Explorer” Windows Azure Marketplace Windows Azure HDInsight Service & Microsoft HDInsight Server (preview) 100% Apache Parallel Data Warehouse with Polybase Combine with unstructured Combine and refine data across multiple sources Gain insight across relational, unstructured, and semi-structured data Easily manage and query Common management of structured and unstructured data Query across relational DB and Hadoop with single T-SQL Query
5. Data Explorer Enable self-service data discovery, query, transformation and mashup experiences for Information Workers, via Excel and PowerPivot Discovery and connectivity to a wide range of data sources, spanning volume as well as variety of data. Highly interactive and intuitive experience for rapidly and iteratively building queries over any data source, any size. Consistency of experience, and parity of query capabilities over all data sources. Joins across different data sources; ability to create custom views over data that can then be shared with team/department.
6. Data Explorer Discover, combine, and refine Big Data, small data, and any data with Data Explorer for Excel. Excel add-in to enhance self-service BI Identify and import external data: Relational dB Excel Text XML Odata Web pages Hadoop HDFS Discover relevant data by using search Combine and transform multiple data sources
7. Data Sources S Azure SQL Database Azure HDInsight Windows Azure Marketplace Windows Active Directory
8. What Is Code-Name “GeoFlow”? Code-name “GeoFlow” for Microsoft Excel enables information workers to discover and share new insights from geographicaland temporal data through three-dimensional storytelling.
9. Code-Name “GeoFlow” Pillars Map data Discover insights Share stories
10. Unique Strengths 3D Geospatial Temporal Guided Tours
11. Common Use Cases Sales performance Distribution of crime data Disease control Weather patterns Seasonality analysis Voting trends Real estate assessment
12. Mission Transform data into fluid, three-dimensional stories to unlock new insights for everyone
13. Code-Name “GeoFlow”Excel Add-in to Enhance Data Visualization Map data, discover insight, and share stories
14. Polybase in PDW V2 (b) PDW query in, results stored in HDFS (a) PDW query in, results out Query Query Results 1 1 3 Hadoop Hadoop PDW» PDW Results 2 2 HDFS blocks HDFS blocks DB DB HDFS HDFS HDFS as new distribution type for PDW tables Parallel loads directly from HDFS into PDW Fully parallel, bidirectional CTAS between PDW and HDFS All query processing done by PDW nodes Both Linux and Windows clusters supported
15. Native Query Across Hadoop and PDWIntroducing Polybase Traditional schema-based DW applications Sensor & RFID Social Apps How to overcome the “impedance mismatch” WebApps Mobile Apps RDBMS Unstructured data Structured data Increasingly massive amounts of unstructured data driven by new sources At the same time, vast amounts of corporate data and data sources, and the bulk of their data analysis Hadoop Polybase addresses this challenge for advanced data analytics by allowing native query across PDW and Hadoop, integrating structured and unstructured data
16. Native Query Across Hadoop and PDWPolybase Features in SQL Server PDW Querying data in Hadoopfrom PDW using regular SQL queries, including Full SQL query access to data stored in HDFS, represented as ‘external tables’ in PDW Basic statistics support for data coming from HDFS Querying across PDW and Hadoop tables (joining ‘on the fly’) Fully parallelized, high performance import of data from HDFS files into PDW tables Fully parallelized, high performance export of data in PDW tables into HDFS files Integration with various Hadoop distributions: Hadoop on Windows Server, Hortonwork and Cloudera. Supporting Hadoop 1.0 and 2.0
17. Native Query Across Hadoop and PDWQuerying Unstructured Data Example – Creating external table ‘ClickStream’: CREATE EXTERNAL TABLE ClickStream(urlvarchar(50), event_date date, user_IPvarchar(50)), WITH (LOCATION =‘hdfs://MyHadoop:5000/tpch1GB/employee.tbl’, FORMAT_OPTIONS (FIELD_TERMINATOR = '|')); Text file in HDFS with | as field delimiter Query Examples 1 Filter query against data in HDFS SELECT top 10 (url) FROMClickStream where user_IP = ‘192.168.0.1’ 2 Join data coming from files in HDFS (Url_Descriptionis a second text file in HDFS) SELECTurl.descriptionFROM ClickStreamcs, Url_DescriptionurlWHERE cs.url = url.name and cs.url=’www.cars.com’; 3 Join data from HDFS with relational PDW table(Users is a distributed PDW table) SELECTuser_nameFROMClickStreamcs, Users u WHEREcs.user_IP = u.user_IP and cs.url=’www.microsoft.com’; Querying data in HDFS and displaying results in table form (using external tables) Joining data from HDFS with relational PDW data
18. Native Query Across Hadoop and PDWParallel Data Import from HDFS into PDW Traditional DW applications Sensor & RFID Social Apps WebApps Mobile Apps CREATE TABLE ClickStream_PDWWITH DISTRIBUTION = HASH(url) AS SELECT url, event_date, user_IPFROMClickStream Retrieval of data in HDFS “on-the-fly” Results CTAS PDW External Table Enhanced PDW query engine Structured data Unstructured data Parallel HDFS Reads ParallelImporting HDFS bridge Hadoop DMS Reader N DMS Reader 1 Persistently storing data from HDFS in PDW tables Fully parallelized via CREATE TABLE AS SELECT (CTAS) with external tables as source table and PDW tables (either distributed or replicated) as destination
19. Native Query Across Hadoop and PDWParallel Data Export from PDW into HDFS Traditional DW applications Sensor & RFID Social Apps WebApps Mobile Apps CREATE EXTERNAL TABLE ClickStream (url, event_date, user_IP) WITH (LOCATION =‘hdfs://MyHadoop:5000/users/outputDir’, FORMAT_OPTIONS (FIELD_TERMINATOR = '|')) AS SELECT url, event_date, user_IPFROMClickStream_PDW Results CETAS PDW External Table Enhanced PDW query engine Structured data Unstructured data HDFS data nodes ParallelReading Parallel HDFS Writes HDFS bridge» DMS Writer N DMS Writer 1 … Fully parallelized via CREATE EXTERNAL TABLE AS SELECT (CETAS) with external tables as destination table and PDW tables as source ‘Round-trip of data’ possible with first importing data from HDFS, joining it with relational data, and then exporting results back to HDFS
20. Power View for Multidimensional Models Power View on Analysis Services via BISM Native support for DAX in Analysis Services Better flexibility: Choice of DAX on Tabular or Multidimensional (cubes)
21. Sponsors Main Gold Bronze Media Swag
22. For more Information #sqlsatistanbul