Todo lo que quieres saber sobre Analysis Services 2016

Los dejo con el video de mi sesión presentada en el SQL Saturday Puerto Rico 2016 en Junio 24, 2016. La primera parte de la sesión muestra un repaso de cómo crear un modelo usando Analysis Services en modo Tabular. La segunda parte muestra las novedades que trae la versión 2016 así como SQL Data Tools 2015 para trabajar con los modelos tabulares.

Introducción a Azure Machine Learning

Los dejo con la grabación de mi presentación de Azure Machine Learning en las sesiones mensuales de Puerto Rico PASS el 22 de  Noviembre del 2015. Que lo disfruten!

Register to attend the Microsoft MVP Virtual Conference

Hi All – I wanted to let you know about a great free event that Microsoft and the MVPs are putting on, May 14^th & 15^th.  Join Microsoft MVPs from the Americas’ region as they share their knowledge and real-world expertise during a free event, the MVP Virtual Conference.

 
The MVP Virtual Conference will showcase 95 sessions of content for IT Pros, Developers and Consumer experts designed to help you navigate life in a mobile-first, cloud-first world.  Microsoft’s Corporate Vice President of Developer Platform, Steve Guggenheimer, will be on hand to deliver the opening Key Note Address.

Why attend MVP V-Conf?  The conference will have 5 tracks, IT Pro English, Dev English, Consumer English, Portuguese mixed sessions & Spanish mixed sessions, there is something for everyone!  Learn from the best and brightest MVPs in the tech world today and develop some great skills!

 

Be sure to register quickly to hold your spot and tell your friends & colleagues.   

The conference will be widely covered on social media, you can join the conversation by following @MVPAward and using the hashtag #MVPvConf.

 

Register now and feel the power of community!

http://mvp.microsoft.com/en-us/virtualconference.aspx

Analysis Services en el IICOM Puerto Rico

Los invito a participar del próximo seminario de Analysis Services Multidimensional que estaré presentando a través del Instituto de Ingenieros de Computadoras (CIAPR).

Los detalles a continuación:

Explorando los sabores de Analysis Services PowerPivot, Tabular y Multidimensional

Hace unos días tuve la oportunidad de presentar en el Capítulo Virtual de PASS en Español (SpanishPASSVC) - t:SpanishPASSVC - acerca de los Sabores de Analysis Services. Si se la perdieron o quieren revisarla, aquí el video bajo demanda.

El concepto del Unified Dimensional Modeling (UDM) fue introducido en Analysis Services 2005 como una capa semántica para facilitar el desarrollo de cubos OLAP de fuentes diversas. En Analysis Services 2012 se introduce el concepto del Business Intelligence Semantic Model (BISM) como una evolución al UDM ofreciendo dos “sabores”: Multidiemnsional y Tabular. El BISM representa el corazón de la estrategia de Inteligencia de Negocios de Microsoft. Esta sesión explicara los conceptos básicos de Analysis Services 2014 y PowerPivot. Discutiremos los diferentes escenarios que permitirán a desarrolladores y arquitectos de BI a tomar mejores decisiones al momento de desarrollar los cimientos de la estrategia empresarial para el análisis de sus datos.

Aquí los slides usados en la presentación:

 

Explorando Analysis Services: Power Pivot, Tabular y Multidimensional from SpanishPASSVC

Así mismo, los invito a visitar el Canal del SpanishPASSVC en YouTube donde encontrarán excelentes videos adicionales de las pasadas presentaciones.

Alan

Soluciones de Inteligencia de Negocios con Excel en el ICOM–Ponce

Para los que viven en Puerto Rico, los invito a participar este sábado 8 de Febrero de mi presentación en el Instituto de Ingeniero en Computadoras (ICOM) de Ponce, donde estaré mostrando las novedades en Microsoft BI usando Excel 2013 como base extendiéndose a Power BI, la innovadora propuesta de Microsoft de BI colaborativo y en la nube.

Para registrarse, visiten la página principal del Instituto: https://ciapr.org/ciapr.org/index.php/seminario.

Nos vemos,

Alan

-- UPDATE 02-18-2014. PPT usada durante la presentación.

 

Anuncio: Nuevos Mentores Regionales de PASS para Latino América

Hola a todos, espero que hayan recibido este nuevo 2014 con mucha alegría y con las ganas de seguir siendo mejor cada día (en todo aspecto de sus vidas). Para mi este año empieza con nuevas emociones, una de ellas es la satisfacción y agradecimiento por haber sido nombrado como Mentor Regional de PASS para Latinoamérica para este 2014. Me acompañan en esta nueva aventura un excelente equipo de profesionales reconocidos en el mundo de SQL Server en nuestro idioma, con los cuales esperamos seguir haciendo crecer a los Capítulos de nuestra región y asimismo seguir difundiendo el conocimiento y acceso al aprendizaje o a la obtención de mayores beneficios de SQL Server en nuestros paises.

Con autorización de Karla Landrum de PASS, comparto a continuación parte del email de anuncio oficial donde se reconoce la gran labor de mis buenos amigos del equipo anterior (Jesús, Mary, John y Carlos), e informa de los nuevos integrantes (la parte introductoria de la comunicación ha sido traducida al Español para facilitar la lectura).

Seguimos en contacto a través de este blog y de PASS LATAM!

Alan

------------------

Hello LATAM Chapter Leaders,

"Les anunciamos al nuevo equipo de Mentores Regionales (MR) de PASS para la región de LATAM para el 2014, pero primero que nada, únanse a mi para extenderle un gran aplauso al equipo de MR que ha representado a su región por muchos años. Ellos han hecho un tremendo trabajo en construir conciencia y crecimiento de PASS a través de los capítulos y eventos de SQL Server para LATAM. PASS agradece a Jesús Gil, Mary Arcia, John Bulla y Carlos Farías por todo su esfuerzo en construir las bases para PASS LATAM. Ustedes son la razón principal de los éxitos vistos en su región. Felicitaciones a todos ustedes por todo lo que han hecho, y continuarán haciendo en sus carreras y en la comunidad SQL Server de PASS.

Otro cambio, específico a Brasil, es la partida de Luciano Moreira. Luciano pasó el último año trabajando de manera cercana con Laerte Junior en continuar el crecimiento de los capítulos locales y eventos SQL Saturday en Brasil. Luciano además llevó el primer evento SQL Saturday a su ciudad Brasilia, con una calurosa asistencia de cerca de 500 personas. ¡Fabuloso! Gracias Luciano por todo lo has hecho para ayudar a continuar con el crecimiento de PASS en Brasil.

Extiendan sus agradecimientos a aquellos Mentores Regionales quienes los ayudaron a ser exitosos.

Con algunos "big shoes to fill" ("espacios grandes que llenar"¿?), es tiempo de anunciar quiénes son los próximos en el rol de Mentor Regional para LATAM comenzando en enero 2014. Por favor, demos la bienvenida a..."

-----

We have a new PASS Regional Mentor team to announce for the LATAM region for 2014, but first, please join me in extending a huge round of applause to the RM team that has represented your region for many years. They have done a tremendous job in building the awareness and growth of PASS through chapters and SQL Server events for the LATAM. PASS wants to “thank” Jesus Gil, Mary Arcia, John Bulla, and Carlos Farias for all their efforts in building the foundation for LATAM PASS. You were the core reason to the successes seen in your region. Congrats to all of you, and all you have done and will continue to do in your careers and in the PASS SQL Server community.

Another change, specific to Brasil, is the departure of Luciano Moreira. Luciano spent the last year working closely with Laerte Junior in continuing the growth of chapters and SQLSaturdays in Brasil, Luciano even bringing the very first SQLSaturday to his home city of Brasilia, with an overwhelming attendance of nearly 500. Fabulous! Thanks Luciano for everything you’ve done to help to continue to grow PASS in Brasil.

Be sure to extend your “thanks” to those Regional Mentors who have helped you become successful.

With some “big shoes to fill” (I hope you all know this expression J), it’s time to announce who is next in the role as Regional Mentors for LATAM beginning January 2014. Please welcome…

-------

LATAM region (outside of Brasil):

Eduardo Castro (ecastrom.blogspot.com) is an architect focused on SQL Server infrastructure and high availability. During his career he has work with projects in industries like Taxes, Water Supply, Power Generation and Administration. Eduardo has been involved with the SQL Server Community for more than 12 years, he is a regular speaker at the local user groups and SQL Saturdays in Latin America. Twitter: @edocastro

 

 

 

Jose Chinchilla (http://sqljoe.com) is a Microsoft Certified Database Administrator and Business Intelligence Architect, President & CEO of Agile Bay, Inc. with more than 15 years of experience with special focus on architecting, modeling and developing Data Warehouse and Business Intelligence solutions. He serves as President of the Tampa Bay Business Intelligence User Group and as a Regional Mentor for PASS. He is a frequent speaker at local user groups, SQL Saturdays, Code Camps, and national events such as PASS Summit. Jose is author of three SQL Server books including SQL Server 2012 Bible (Wiley), Professional SQL Server 2012 Administration (Wrox) and Master Data Services Essentials with SQL Server 2012 (PacktPub). Twitter: @SQLJoe

 

Jose Redondo (http://redondoj.wordpress.com) is an Information Technology System Business Consultant, & design and develop Database & Data Warehouse. He has worked as a consultant for more than 20 years. He enjoys solving business problems to customers in different areas. He knows and has worked with MS SQL Server from version 4.2 both OLTP and OLAP. Currently, he serves as Architect and Consultant BI independent in Caracas, Venezuela and Bogota, Colombia as well as other Latin American countries. He has been involved with technology communities, and especially with SQL Server since 2007 when it was called to create the SQL PASS Community Venezuela - Caracas Chapter in 2009. He has participated in the SQL Saturday as volunteers and speaker. Born in Barranquilla, Colombia but lived more than 20 years in Caracas, Venezuela; and today they reside both in Colombia as well as U.S.A.  E-mail: redondoj@gmail.com Twitter: @redondoj

 

Alan Koo (http://www.alankoo.com) is a Senior Consultant and Product Manager at Nagnoi, LLC. with more than 13 years of experience working across different industries in the public and private sectors. Former Microsoft MVP in SQL Server from 2008 to 2011. Specialized in Business Intelligence and Performance Management since 2005. Co-founder of Puerto Rico PASS in 2005 and president until 2013. He enjoys sharing his knowledge and experiences through his Microsoft Business Intelligence and more…blog, and other channels/events including SQL Saturdays, 24 Hours of PASS LATAM and the MSDN SQL Server Forums. Other technical passions are NET development and business topics. He is originally from Peru, currently living in Puerto Rico since 2004 together with his wife, son and little daughter. Twitter: @Alan_Koo

 

For Brasil:

Diego Nogare (http://www.diegonogare.net) SQL Server MVP since 2008, has MCP, MCTS, MCITP and MCT certifications. PASS Chapter Leader in São Paulo/Brazil. Worked for great companies as Deloitte, GE and Bayer. Worked for the fundation of SolidQ in Brazil and also at Microsoft at SQL Server Team, to improve the SQL Server 2012 launch. Works focused in Business Intelligence at Minha Vida WebPortal. Is frequently speaker in events from Microsoft, Codificando .Net, InetaBR and PASS. Twitter: @DiegoNogare

 

 

Remaining for Brasil for another year:

Laerte Junior (www.shellyourexperience.com) is a PowerShell MVP, SQL Server specialist and an active member of WW SQL Server and the Windows PowerShell community. He also is a huge Star Wars fan (yes, he has the Darth Vader´s Helmet with the voice changer). He has a passion for DC comics and living the simple life. “May The Force be with all of us”. Twitter: @LaerteSQLDBA

The PASS Regional Mentors will periodically touch base with you to check in and see how things are going, but feel free to reach out to them any time at RM.LATAM@sqlpass.org. Regional Mentors are there to help you in several ways, such as ideas on marketing your user group to help grow your membership and attendance to your meetings, they can provide guidance on how to find speakers, several have hosted events, including SQLSaturdays, so can provide their experience if you are interested in hosting one of PASS’ free community events in your city.

Karla Landrum 
Community Evangelist | PASS Headquarters

Big Data: El qué y el cómo

Hola a todos. Hace unos días participé en el evento “Maratón de Inteligencia de Negocios”, organizada por la Comunidad Intermezo y Microsoft TechNet. Durante este día tuve la oportunidad de presentar una introducción a lo que es (y no es) Big Data y a la estrategia de Microsoft dentro de este nuevo mundo con la sesión que lleva por título este post.

Si quieren ver la lista de sesiones y sus grabaciones para acceso bajo bajo demanda, pueden visitar el link original del evento: http://bit.ly/1a2BTpB.

Comparto a continuación la grabación de la presentación. Lamentablemente el tiempo me quedó corto para realizar una demostración de Hive lo cual estoy en deuda y prometo preparar y publicar próximamente.

Los dejo con el video, espero lo disfruten tanto como yo.

Los slides de la presentación por si la quieren ver con mejor resolución:

 

Alan

SQL Server 2014 Video Series– Installing Sharepoint 2013

This is the second video for the SQL Server 2014 series. In the first video I showed you how to install SQL Server 2014 CPT1 (including Business Intelligence components) in a single server.

Microsoft full story for Business Intelligence solutions is tightly related to SQL Server, Sharepoint and Excel. For this reason, in this new video, I’ll show you how to install and configure a new Sharepoint 2013 in a single server (including PerformancePoint Services, Excel Services and Visio Graph Services) to set you ready to create and deploy your Business Intelligence solutions.

In the next video, we will install and configure Reporting Services (including Power View) in Integrated Mode with our Sharepoint 2013 installation.

Alan

SQL Server 2014 Video Series (SQL Server 2014 Installation)

This is the first of a series of videos regarding the new features that SQL Server 2014 will bring us. As you could be aware of, some months ago, Microsoft made public the first public release of SQL Server 2014 (CPT1). SQL Server 2014 as the name indicates, is expected to be launched at some time during 2014. You can download and play installing it by yourself from here.

One of the most promoted new features in this new release of SQL 2014 is without any doubt, the embedded In-Memory engine in the relational engine not just for read-only indexes (as column store indexes) but also for all insert, update and delete transactions as typical in transactional applications. This new in-memory engine technology is coded-named “Hekaton”.

From my perspective, I will be sharing with you the new features and experiences that SQL 2014 will bring us to deliver better business intelligence and performance management solutions.

As it is typical, we will start showing you how to do a basic SQL Server 2014 installation using the CPT1. The next two videos of this series will be related to installing Sharepoint 2013 over SQL Server 2014 and configuring Reporting Services (Power View) in Sharepoint Integrated Mode.

Here the first one. Installing SQL Server 2014 CPT1:

Here some other resources where you can more interesting information about SQL Server 2014:

• What is new in SQL Server 2014 (CPT1)?
• Microsoft® SQL Server® 2014 CTP1 Product Guide
• SQL Server Blog 
• New and Advanced Features of Microsoft SQL Server 2014
• Features supported by the Editions of SQL Server 2014

Alan

TechNet’s subscription retirement and the Microsoft Certified Trainers (MTC) program impact

 

If you are an MCT, you should already know about the Microsoft TechNet’s retirement announcement plan. Based on this, there has been a lot of discussion inside the MCT community and social networks during the last few weeks. To our benefit, some MCTs had taken the lead and the initiative contacting Microsoft directly to tell them more about the basic tools we as MCTs need in order to keep doing a great job for our students.

One of this MCTs is Kostantin Ceran. For this reason following I‘m sharing Kosta’s last update (with his permission) regarding his communications with the Microsoft Learning team for all MCTs and specially those in the Puerto Rico region.

Alan

---

I am the Regional Lead for Microsoft Certified Trainers in Turkey. Following Microsoft’s announcement about TechNet’s retirement, I have tried to create awareness within Microsoft that the adopted way of action will hurt legitimate customers and will cause long-term financial loss for both parties. I decided to send an e-mail to Steve Ballmer, which was kindly welcomed. This allowed me to discuss the matter with the managers of Microsoft Learning.

Furthermore, I wrote a report about the long-term effects of TNS retirement on the MCT community. I prepared an MCT value proposition and presented it to the management of Microsoft Learning. Their initial reaction was positive and I hope that my report will be able to spark some discussion during the MCT Summit in September.

Today, I have received information that Microsoft have decided to partially remedy the complaints of the MCT community. I am expecting further developments during the next couple of weeks, but as of today, 3 points have been made clear by Microsoft:

1. TechNet subscribers that have activated their accounts before the 1st of September will receive a “free, one-time, 90 day subscription extension”. This will adjust the date of the current MCT TechNet subscription deadline to 29 June 2014, earliest. The exact date of expiration can be checked from the “My Account” page of the TechNet Subscriptions benefits portal. You can continue to access downloads and licenses until your updated expiration date is over. For details go to http://technet.microsoft.com/subscriptions/default.aspx
   

2. TechNet Evaluation Center will start providing access to prior versions of the available trial software. Windows 7, Windows Server 2008 R2, and Office 2010 are made available as of today. Microsoft stated that they will add additional products over time. For details go to http://technet.microsoft.com/en-US/evalcenter/dn407368

3. For MCTs only, Microsoft plans to replace their current TechNet NFR Subscription with a new solution (yet to be determined) that will provide them access to non-time bombed software for the duration of the 2014 MCT program membership year. As expected, the software contained in the said solution will be used for instructional purposes only. More details will be available in the coming weeks.

Konstantin Ceran

Exam 70-461 (T-SQL SQL Server 2012) preparation Jump Start

For all people interested in taking exam 70-461 (requirement for any SQL Server 2012 certification) or in learning more about the new T-SQL enhancements in SQL Server 2012, the SQL Server Team will be presenting a full day online training and best of all, for FREE!. More information and registration here: http://blogs.technet.com/b/dataplatforminsider/archive/2013/09/04/querying-microsoft-sql-server-2012-databases-jump-start-september-13th.aspx. (Thanks to @SQLTitan for the info).

Alan

 

Querying Microsoft SQL Server 2012 Databases Jump Start–September 13th

If you’re already familiar with database fundamentals and you’ve worked with SQL Server, the Querying Microsoft SQL Server 2012 Databases Jump Start course will help you take your skills to the next level. You’ll learn about data manipulation and how to alter tables, and you’ll see it all in action in the demos presented by the experts: Brian Alderman, CEO and Founder of MicroTechPoint, and Tobias Ternstrom, Microsoft Program Manager in the SQL Server division. If you’re working on a Microsoft certification, this course will help you prepare for Exam 70-461.

Register now for this course to get a detailed look at:

• Introduction to SQL Server 2012
• Advanced SELECT statements
• SQL Server data types
• Grouping, aggregating and modifying data
• Table expressions and SET operators
• Programming with T-SQL
• Retrieving SQL Server metadata

This course takes place:

Date: September 13, 2013
Time: 9:00am – 5:00pm
Where: Live, online virtual classroom
Cost: Free!

Introducción a Self Service Business Intelligence con Excel – Parte 2

Como les comenté, hace algunos días tuve la oportunidad de realizar una presentación para el Instituto de Ingenieros en Computadoras (IICOM) de Puerto Rico con respecto a Self Service BI. En este post les presento la segunda parte de dicha presentación y si no lo han hecho ya, los invito a revisar la primera la primera parte de esta serie en éste enlace.

En esta segunda parte nos adentramos al uso de Excel como herramienta de Business Intelligence específicamente haciendo demostraciones de los componentes de Power BI: Power Query, Power View y Power Map. Debido a que el objetivo de la sesión era dar una vista de 360 grados del potencial de Excel como herramienta preferí dejar el tema de Power Pivot para una próxima presentación y en su lugar darle mayor espacio a Power Query.

Esta segunda grabación tiene una duración de casi 1hr 40Mins. Si quieren visitar directamente alguna de las demostraciones, a continuación una guía de los mismos:

• Power Query: Analizando el uso de los archivos de nuestra PC (11:45)
• Power Query: Buscando y explorando data – Wikipedia (23:45)
  Lista de Radios en Puerto Rico
  Jugadores de la NBA con mayor cantidad de premios MVP
• Power Query: Extrayendo datos desde páginas web (43:00)
  ClasificadosOnline.com
• Power Query: Importando  y analizando datos desde GapMinder (1:00:22)
  Índice de Masa Corporal  (BMI), PBI per Cápita (GDP) y Población
• Power Map Demo (1:31:35)

En cada uno de los demos usando Power Query realizo alguna visualización rápida con pivot tables de Excel o con Power View.

Sin más detalles los dejo con la grabación. Espero lo disfruten.

Alan

Introducción a Self Service Business Intelligence con Excel – Parte 1

Hace unos días tuve la oportunidad de brindar un seminario para el Instituto de Ingenieros en Computadora (IICOM) de Puerto Rico. El enfoque del seminario fue llamar al interés de los participantes a que puedan conocer e interesarse de alguna de las nuevas herramientas para el manejo y análisis de datos a las que podamos tener acceso hoy en día.

Por este motivo, preparé este seminario de casi 3 horas de duración en donde la primera parte del mismo y presentada en este post, busca llamar la atención con respecto a las últimas tendencias de las Tecnologías de la Información (TI), así como algunos aspectos básicos de los temas de interés como lo son Big Data y Self-Service Business Intelligence (BI).

Update 1: Incluyo la presentación desde SlideShare para referencia:

En la próxima entrega estaré publicando la segunda parte del seminario, en donde presento demostraciones prácticas de cómo trabajar con Excel como herramienta de Self Service BI, específicamente haciendo uso de Power BI el cual incluye las herramientas Power Query, Power Pivot, Power View y Power Map.

Pónganse cómodos y espero disfruten el video.

Alan

Presentación en el CIAPR

Para no perder la costumbre, este miércoles 21 de agosto estaré presentando el tema “Enabling Self Service Business Intelligence using Excel” en la sede central del Instituto del Ingenieros en Computadoras del Colegio de Ingenieros y Agrimensores de Puerto Rico. El seminario tendrá una duración de dos horas y media, tiempo suficiente para ver un poquito de las novedades de Excel 2013 y Power BI. El ingreso es libre.

A los que estén cerca, espero verlos por ahí.

Alan

Análisis Predictivo usando Analysis Services 2012

Comparto con ustedes la grabación de mi presentación en el SQL Saturday Puerto Rico 2013 organizado por Puerto Rico PASS, presentando el tema “Predictive Analytics using Analysis Services 2012”. El objetivo de la presentación es mostrar los escenarios de uso, problemas que resuelve el análisis predictivo, y cómo Analysis Services 2012 nos ayuda a trabajar con estos escenarios. Espero lo disfruten tanto como yo.

Alan

Probando los sabores de Analysis Services 2012

Comparto con ustedes la grabación de mi presentación en el SQL Saturday Puerto Rico 2013 organizado por Puerto Rico PASS, presentando el tema “Tasting the Flavors of Analysis Services 2012”. El objetivo de la presentación es ayudarles a entender y decidir la versión de PowerPivot y/o Analysis Services que pueden usar según las necesidades del negocio o las capacidades técnicas de cada una de las opciones. Espero lo disfruten tanto como yo.

Alan

IT Cloud Skills Gap - A certificarse en temas de la Nube

Looking at your next move for your IT career?

…according to IDC, 1.7 million unfilled cloud-related jobs in 2012. 7 million open cloud positions within the next three years. A near-crisis in IT hiring circles to find qualified candidates with essential experience, training and certification.”

View full image Microsoft Learning

Microsoft Data Explorer Preview for Excel–Part 1

Some days ago, Microsoft released the first public preview of Microsoft Data Explorer for Excel (http://www.microsoft.com/en-us/download/details.aspx?id=36803) as strategy to enforce their Self-Service Business Intelligence strategy beyond PowerPivot and Power View.

This new and very interesting tool which installs an Excel Add-In for Excel 2010 SP1 and Excel 2013, is described by the Microsoft website as a tool that “...simplify access to public and corporate data. Easily discover, combine, and refine data for better analysis in Excel.“

To unveil what this new tool brings us, this is the first part of a series of post to discover some of the capabilities of Data Explorer. Let’s begin!

After you install Data Explorer, you will see a new ribbon in Excel:

For this first post, we will play with the Online Search feature, that allows us to search and discover data in public sources. For this preview, the search is limited to Wikipedia but it is expected to expand the number of public sources in the next releases:

The Online Seach pane opens:

Try a new search related to the highest income States which relative quickly shows 112 results distributed in pages:

If you mouse-over the search results, an automatic new Excel sheet named “preview” appears and shows a preview of the data exposed by each search result:

You can move your mouse to the next result and the same will happen for the new data source. Additional information is shown in the preview such as the description and the original URL for the data source:

If you clic over one of the data sources, you will navigate to the original webpage. In this case Wikipedia:

When you finally find your data source you need to clic on “USE” to get the data to your Excel spreadsheet:

Data will be downloaded to a new sheet:

The Query Settings pane appears with some new options and update information for the recent query:

You can personalize the default query by clicking Filter & Shape:

The “Edit Query” window opens showing the OData query for this data source:

If you clic over the row number you will get a preview of the whole row in form view:

Part of the “power” of Data Explorer is shown when you right clic selecting any column. You can change the data type, hide some rows, aggregate rows using a group by among other options. We will explore more of these options in future posts:

Other very interesting feature is that everything that you do with your query is “recorded” and shown in the Steps pane inside the Query Editor:

The first step is the selection of the “Source”:

If you clic over the Source step, the OData Feed query is shown:

I will perform some additional actions over my query:

Accordingly, the Steps are updated with each query “transformation”:

Now I removed the last two steps and filter the States begining with “A”:

The OData query and data are updated:

Now sort the Median Household Income descending:

Query and Steps are updated:

Going back to the Query Pane in Excel, you can notice some options about the data load in the third section: “Enable download” allow to enable or disable the use of the query. “Load to worksheet” indicates Data Explorer if in addition to create the data source query, Excel should download the data to a new Excel sheet. The last option “Load to data model” indicates Data Explorer to download the data to the workbook Power Pivot data model.

In this case we will turn-off the “Load to Worksheet” and load the data directly to our PowerPivot model:

Data is removed from the sheet:

After it indicates data has been loaded to data model, we open the Manage model from the PowerPivot ribbon:

Having the data in PowerPivot allows you to create very complex and powerful data models:

Just to show you how easy is to play with our new dataset, you can create new Pivot Tables or better, if you have Excel 2013,  create a new Power View report:

Wrapping up…

As you saw, the new Microsoft Data Explorer Preview for Excel makes honor to its name and description, putting in the hands of the end-user (Information Worker in the Microsoft lingo), a very simple to use and powerful way to discover and obtain new data from local and external sources to enrich our analysis, even from non relational sources such as Wikipedia pages, fast and easy as never before. In the next post we will play with more of one source of data and the merge capabilities.

Alan

Webcast Análisis Predictivo con Analysis Sevices

Hace unos días tuve la oportunidad de presentar el tema que titula este post, dirigido al Capítulo Virtual de SQL Server en Español el cual comparto a continuación.

Es bueno resaltar que ya hace un par de meses había presentado el mismo tema para PASS LATAM 24 Horas, pero en aquella ocasión no pudimos dar los demos por temas técnicos, pero ofrecí grabarlos en el futuro cercano. Lo prometido es deuda.

 

Alan