This article, is an introduction to SQL Server PowerPivot and also a review of my session on the MS Momentum 2009 event, that took place in Cyprus.
About the MS Momentum 2009 Event in Cyprus
On November 26th 2009, Microsoft-Cyprus launched Momentum 2009; a New Efficiency Community Launch event.
The event took place in Nicosia, Cyprus, Europe, and had at around 400 attendees. Two tracks were featured: one for Developers, and another one for IT-Pros.
My SQL Server-Related Session at the Event
I had the pleasure to participate as one of the Cyprus .NET User Group (CDNUG) speakers.
The topic of my session was “Self-Service Business Intelligence with SQL Server 2008 R2”. Actually, I talked about PowerPivot 🙂
Though before the deep dive into PowerPivot and the demonstration of the unlimited power it offers, I provided a short introduction to the great SQL Server 2008 R2 features along with a first demo showcasing these features.
Some of the exciting new features in SQL Server 2008 R2 (November 2009 CTP) are:
- Connectivity to SQL Azure
- Data-tier Application Upgrade
- SQL Server Utility
- Master Data Services
- A large number of enhancements on Reporting Services
You can find more information on the new features of SQL Server 2008 R2 Nov. CTP on this link.
What is PowerPivot?
After the above introduction to SQL Server 2008 R2, my session got focused on the Analysis Services set of features in SQL Server 2008 R2 and more specifically on SQL Server PowerPivot for Excel 2010.
So what exactly PowerPivot is?
PowerPivot (formerly known as “Gemini”) is an add-on for Excel 2010 / SharePoint 2010 which brings the full power of SQL Server Analysis Services right into Excel.
Its engine called “Vertipaq” uses in-memory column based compression allowing millions of rows of data to be stored, sorted and aggregated.
PowerPivot supports a wide variety of sources and provides the Business User with a rich set of mathematical functionally featuring the existing functions already in Excel and the powerful Data Analysis eXpressions (DAX).
Example Using PowerPivot
You can access PowerPivot by clicking on the “PowerPivot” button on the Ribbon in Excel 2010:
Then, you are presented with the PowerPivot dialog:
Within the PowerPivot dialog, under the “Home” menu, you can select from a huge variety of data sources such as:
- Several providers to DBMSs
- Text files
- Excel workbooks
- Reporting services
- RSS feeds
After you select the data source(s), you can import data which is imported in tabular format.
You can even import data from the clipboard with copy-paste!
Under the “Table” menu, you are able to manage tables’ properties along with managing the various relationships between the tables. You can also create new relationships:
Under the “Column” menu, you are able to manage the columns’ properties such as data types and format. Also, you can add new columns:
After importing the data, by returning under the “Home” menu and by clicking on the “PivotTable” button, you are able to create Pivot Tables based on several presets. Then PowerPivot automatically switches back to the workbook (it lets you either create new sheets or use the existing ones) where you are able to easily perform aggregations and many other mathematical calculations on the data you have previously imported.
This is a very basic introduction to PowerPivot as it is within the context of the review regarding my session on Microsoft Momentum 2009. In subsequent posts, I will thoroughly explain and demonstrate all the exciting features of PowerPivot!
Throughout my session many demos were performed including the following scenarios:
- Import data from the clipboard with copy-paste
- Import data from Excel workbooks
- Import data from a SQL Server database containing 5+ million rows
- Perform a mashing-up of data using various data sources (clipboard, excel workbooks, SQL Server database)
After importing data based on the different scenarios, aggregations were performed against the data for answering business queries.
Please find below the PowerPoint presentation of my session at Momentum 2009:
CDNUG presence on the event was strong! We had a special “CDNUG Area” where developers could sign-up with the User Group. Many people expressed interest on the activities of our User Group. The feeling was great! 🙂
The event was definitely a huge success thanks to the great organization of Microsoft and the rich and high-quality content delivered by all the speakers. CDNUG participated by delivering three sessions on the Developers track. You can find the full review of our User Group’s participation on the CDNUG website along with a description of the entire Developers track.
The “PowerPivot Experiment”
I have to admit that I was really looking forward to test the capabilities of SQL Server PowerPivot. Thus being a SQL Server fan, in one of my tweets, I mentioned that I would not rest until I tried the real capabilities of PowerPivot 🙂
To this end, after the event, I tried importing 100+ Million rows from a SQL Server Database into Excel 2010 using PowerPivot. Guess what? I did it! 🙂
After designing a database and populating it with a total of 100+ Million rows, I imported the data from its five tables into excel. It only took 7 minutes to import the data! Also, performing aggregations on the data was very fast!
On one of my subsequent posts on PowerPivot, I will provide the schema of my database and a full description of entire process!
Make sure you check back my blog soon for newer posts on this topic!
Learn more tips like this! Check our Online Course!
Check our online course titled “Essential SQL Server Development Tips for SQL Developers” (special limited-time discount included in link).
Sharpen your SQL Server database programming skills via a large set of tips on T-SQL and database development techniques. The course, among other, features over than 30 live demonstrations!
Featured Online Courses:
- Boost SQL Server Database Performance with In-Memory OLTP
- Essential SQL Server Administration Tips
- SQL Server Fundamentals (SQL Database for Beginners)
- Essential SQL Server Development Tips for SQL Developers
- The Philosophy and Fundamentals of Computer Programming
- .NET Programming for Beginners: Windows Forms (C#)
- Introduction to Data Science and SQL Server Machine Learning
- Introduction to Azure SQL Database
- SQL Server 2019: What’s New
- Entity Framework: Getting Started (Complete Beginners Guide)
- How to Import and Export Data in SQL Server
- Get Started with SQL Server in 30 Minutes
- A Guide on How to Start and Monetize a Successful Blog
Related SQL Server Development Articles:
- Error converting data type varchar to float
- Error converting data type varchar to numeric
- The set identity_insert Command in SQL Server
- Handling NULL Character x00 when Exporting to File Using BCP
- The Net.Tcp Port Sharing Service service on Local Computer started and then stopped
- …more SQL Server development articles
Subscribe to our newsletter and stay up to date!
Check out our latest software releases!
Check out Artemakis’s eBooks!
Rate this article:
Reference: SQLNetHub.com (https://www.sqlnethub.com)
Artemakis Artemiou is a Senior SQL Server Architect, Author, and a 9 Times Microsoft Data Platform MVP (2009-2018). He has over 15 years of experience in the IT industry in various roles. Artemakis is the founder of SQLNetHub and TechHowTos.com. Artemakis is the creator of the well-known software tools Snippets Generator and DBA Security Advisor. Also, he is the author of many eBooks on SQL Server. Artemakis currently serves as the President of the Cyprus .NET User Group (CDNUG) and the International .NET Association Country Leader for Cyprus (INETA).