SQLAuthority News – Download PowerPivot or PowerView enabled Workbook Optimizer – Download SQL Server Connector for Apache Hadoop

Earlier this week Microsoft have released two very interesting downloads which got my attention. Haddop, PowerPivot and PowerView all three are not directly related to traditional RDBMS but their important is growing in the industry as big data is taking over market.

The Microsoft SQL Server SQOOP Connector for Hadoop is now part of Apache SQOOP 1.4 and we are not providing a separate download anymore. Please note that Microsoft’s HDInsight service includes the connector as well. Linux (for Hadoop setup) and Windows (with SQL Server 2008 R2 installed). Both are required to use the SQL Server-Hadoop Connector.

With SQL Server-Hadoop Connector, you import data from:

  • tables in SQL Server to delimited text files on HDFS
  • tables in SQL Server to SequenceFiles files on HDFS
  • tables in SQL Server to tables in Hive
  • result of queries executed on SQL Server to delimited text files on HDFS
  • result of queries executed on SQL Server to SequenceFiles files on HDFS
  • result of queries executed on SQL Server to tables in Hive

With SQL Server-Hadoop Connector, you can export data from:

  • delimited text files on HDFS to SQL Server
  • sequenceFiles on HDFS to SQL Server
  • hive Tables

Download instruction for SQL Server Connector for Apache Hadoop

The Workbook Size optimizer for Excel can better compress data inside workbooks that use PowerPivot or PowerView if this data comes from external data sources. The best size compression can be achieved for workbooks based on SQL Server databases and there are a few tricks we can do for other SQL datasources as well. The optimizer will install as an add in to excel and will provide you with a nice wizard to better compress the size of your workbook. Using the optimizer you can often get more than 1,000,000 rows datasets in a workbook under 10 MB, share it in SharePointOnline and interact with it using the Excel Web App in any browser.

Download PowerPivot or PowerView enabled Workbook Optimizer

(Courtesy Microsoft Downloads)

Reference: Pinal Dave (http://blog.sqlauthority.com)


SQL SERVER – Enable PowerPivot Plugin in Excel

Recently I had interesting experience at one conference. My PowerPivot plugin got disabled and I had no clue how to enable the same. After while, I figured out how to enable the same. Once I got back from the event, I searched online and realize that many other people online are facing the same problem. Here is how I solved the problem.

When I started Excel it did not load PowerPivot plugin. I found in option>> Add in the plug in to be disabled. I enabled the plugin and it worked very well. Let us see that with images.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – Microsoft WhitePaper on PowerPivot Data Refresh

I was recently working at customer location on PowerPivot project. It was quite complected as this is relatively new technology and we all are exploring what this technology can do and what it can bring to us on table in real life experience. During this implementation the project design document needed specification regarding Data Refresh rates. It was a bit complected as there were various components and modules to the project and selecting the refresh rates means understand all of the requirement as well understanding our implementation in and out.

I referred following white paper from Microsoft before I move further in the project and found it very helpful and it helped me understand the implication of various data refresh methods and its capacities.

Everything You Always Wanted to Know About PowerPivot Data Refresh but Were Afraid to Ask

Following is the summary of the whitepaper.

The purpose of PowerPivot for SharePoint is to allow users to share their PowerPivot workbooks in a secure and scalable environment. To ensure that you are sharing and collaborating on the most recent data available to you, PowerPivot for SharePoint provides a data refresh feature that lets you automatically update PowerPivot data in an Excel workbook. This white paper describes in detail the data refresh feature in PowerPivot for SharePoint. It starts by explaining the steps for setting up a data refresh schedule in SharePoint, and then it continues with an in-depth view on how data refresh works on a SharePoint farm. Both administrators and the business analysts who author and manage PowerPivot workbook data can benefit from learning more about setting up and using data refresh in a SharePoint environment.

I suggest if you are facing situation like me download this whitepaper and read it before moving ahead in project.

Reference: Pinal Dave (http://blog.SQLAuthority.com)


SQL SERVER – 2008 R2 – PowerPivot for Microsoft Excel 2010 – RTM



Microsoft PowerPivot for Microsoft Excel 2010 provides ground-breaking technology, such as fast manipulation of large data sets (often millions of rows), streamlined integration of data, and the ability to effortlessly share your analysis through Microsoft SharePoint 2010.

I have recently started to work with SQL Server 2008 R2 and find the product extremely stable and feature complete. I have installed PowerPivot and I am finding it to be also integrating very well with the product. I recently did one presentations using this two technology and worked very well.

Let me know if you are using PowerPivot for your power BI users. If you are using what is their experience?

Download Microsoft SQL Server 2008 R2 – PowerPivot for Microsoft Excel 2010 – RTM

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – Office 2010 Readiness Check – Are you ready for Office 2010?

PowerPivot for Excel is a data analysis tool that delivers unmatched computational power directly within the application users already know and love—Microsoft Excel. Office 2010 is the next version of Office 2010. We all know Office 2010 is on the verge of getting released and the reviews available online say that it’s a phenomenal product.

My friend Vijay Raj has written excellent article on Office 2010 Readiness Check. Vijay is a Microsoft MVP, focusing on Application Setup and Deployment. He is also a Springboard Series Technical Expert Panel member for Windows 7.  He is one among the core team members at BDotnet and BITPro User Groups. Apart from being passionate about Technology evangelism, he loves music. He is also a big fan of Mark Russinovich, A R Rahman and Sachin. He blogs at http://www.msigeek.com.

Let us read the piece he sent for SQLAuthority.com readers to aid them with Office 2010 Readiness Check

Are you ready for Office 2010? It may look simple for an end-user, but when you look it from an enterprise perspective, there are lots of things which need to be considered before the migration and Compatibility stands out to be one big stop point. Compatibility issues may cover everything right from your environment till the addins which you develop.

Microsoft has released 2 tools (Office Environment scanning tool (OEAT) and Compatibility Inspector) which will exactly help you address this concern.

Office Environment scanning tool (OEAT)

This a comprehensive tool which (along with documentation) assists a customer in the assessment phase of deployment. It identifies the currently installed applications and installation environment, add-ins currently in use by Office clients, programs that are not registered as add-ins but still interact with Office programs, provides a report on the Environmental assessment (potential upgrade issues) and also reports the Add-ins assessment–list of third party programs and information about the compatibility of those programs with Microsoft Office 2010.

Read this article to know more on how you can use the Office Environment scanning tool (OEAT)

Microsoft Office 2010 Compatibility Inspector

This tool compares existing code against the Office 2010 object model by using a text search for known properties and methods that have changed. It includes a basic scanner where definitions and remediation links are updated from a central online location. It also generates a summary report and a details report. The summary includes the total lines of code scanned as well as total lines identified as potential candidates for object model changes. The details report includes the module name, line number, and links to remediation for each identified issue along with color-coded flags for impact guidance. You can run the tool against specific projects. This tool is available as an addins for both Office and Visual Studio.

Read this article to know more on how you can use the Compatibility Inspector (http://www.msigeek.com/2821/fixing-code-compatibility-issues-with-office-2010-compatibility-inspector)

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQLAuthority News – TechDays Session at Infosys Mysore 2009 – Change Data Capture and PowerPivot

It has been a great pleasure to visit Infosys Mysore for an MSDN session. I had previously visited Infosys Bangalore for Technical session. Please read the details of earlier visit SQLAuthority News – Notes from TechDays 2009 at Infosys, Bangalore. This event was held on Dec 10, 2009.

I have been recently presenting the subject of Change Data Capture; it has been great fun as it is a very interesting subject that really captures your attention. It was a well-received session that lasted for nearly 1.5 hours instead of regular 30 min. The smart crowd at Infosys received the subject very well. They also interacted by asking all sorts of question on the subject. Notably, the attendees seemed to understand the subject and they also participated with me. This event was held on Dec 10th, 2009.

Pinal at Infosys Mysore

Session 1: The History of Log – Change Data Capture (CDC)

Abstract: Learn to capture the history of data using CDC. An age-old method of writing queries and triggers to capture change in database table is replaced with a considerably powerful asynchronous method of change data capture (CDC). All attendees will learn how to configure CDC in less than 60 seconds.

If you are interested to read more about CDC, please download CDC script from here.

Pinal at Infosys Mysore

The infrastructure at Infosys is very advanced. The hall, in which I presented the session, had a seating capacity of nearly 1100 people.

Pinal at Infosys Mysore

You can clearly see that it is very interesting to present a session in front of large screen projection. Whatever you see on the screen appears enlarged, but it is very clear to audience.

Partial Audience

Session 2: PowerPivot Self-Service Business Intelligence in Excel 2010

Abstract: You most likely have already heard of PowerPivot, the ground-breaking new BI technology shipping in Microsoft SQL Server 2008 R2. In this session, we introduce PowerPivot for both analysts and IT in the context of Self Service BI. We look at the client capabilities of PowerPivot for Microsoft Office Excel power users, the collaboration features for teams, and the important IT tools for compliance and effective administration.

Excellent SQL Expert – L Srividya (Microsoft)

It was my pleasure to meet one of the Microsoft evangelist and very known SQL Expert L Srividya. She had the skills to keep the audience entertained as well as educated. She one of those professionals who know SQL inside out. She almost knew all the answers of the questions asked. She even had a good insight of subject and also helped me to solve few problems during the session. She also held a demo extravaganza that entertained people so much that they would not leave the session hall, even after the end of the session. In short, she was successful in capturing the attention of the crowd here.

Infosys Mysore – Multiplex

The session hall which had very large screen and 1100 attendees capacity is known as multiplex and above photo is just outside the session hall.

Infosys Mysore – Vidhansabha

During my sessions at Infosys, I had great time to go around and see campus. Infosys Mysore campus is really huge and very artistically decorated. I was amazed with its infrastructure and size. Infosys’ Chairman and Chief Mentor M.R. Narayana Murthy has described the training centre, which is located 140 km off from India’s Silicon Valley, Bangalore, as the biggest corporate training facility in the world. “We are setting up the biggest corporate training facility in the world with 2,000 rooms that can house 4,000 people on a twin sharing basis.”

It was my great pleasure and I would like to express my special thanks to Microsoft for arranging this huge successful event at Infosys, Mysore.

Reference: Pinal Dave (http://blog.SQLAuthority.com)