Search results matching tag 'Excel 2013'

DAX Studio for Excel 2013 finally available! #dax #excel #powerpivot #ssas #tabular

sqlbi — Sat, 04 May 2013 05:49:00 GMT

I'm so happy that DAX Studio finally supports Excel 2013! As Darren Gosbell described in his blog, this release has a few internal changes that will better support future enhancements. I will port the code to capture the query plan for a query in this new release, but unfortunately it will require some weeks because I'm traveling a lot in these days.

If you write DAX formulas and queries for PowerPivot or Analysis Services Tabular, DAX Studio is a must have tool: do you really want to live without a DAX Editor? There are a lot of possible improvements and I hope other contributors will give their help to this Codeplex project.

PowerPivot Workbook Size Optimizer #powerpivot #tabular

sqlbi — Tue, 30 Apr 2013 10:50:00 GMT

Microsoft released the Workbook Size Optimizer for Excel, the first version of an Excel add-in for Excel 2013 that inspects the data model and suggest possible optimizations. Fundamentally, it tries to apply the best practices descripted in a white paper I mentioned a few weeks ago, removing useless columns and changing granularity to those that could reduce the overall memory cost of a table.

There are different setup available in the download page, depending on operating system (Windows 7 or Windows 8) and on Office version (32 or 64 bit). Once installed, you have a new tab in the Excel ribbon, called Workbook Size Optimizer, showing a single button that starts a wizard.

I tried to run the optimizer with a workbook where I imported several tables from Adventure Works Data Warehouse sample database. The first page shows a few information about the workbook size and the option of automatic detection or manual choice of rules. The latter is an option you can request also later, so I started with the default.

After a short analysis, I received three smart suggestions (considered the model I have). We might wonder that removing UnitCost is a smart thing, because it could be required in order to perform calculations and rounding the value might be not correct for our analysis.

Since I requested to apply some changes, I have the option of changing which rules to apply. This corresponds to the choice you have if you choose “Let me choose the rules myself” in the first screen of the wizard.

I kept all the rules and after I click Next I had to wait several seconds in order to complete the optimization process. The result shows a few information about the result of the job.

This is a good starting point. Don’t blindly trust any suggestion and try to consider carefully the rules to apply in order to avoid losing important data for your analysis. Moreover, you might have a better knowledge of your data model than a wizard and consider the deletion of many useless columns (for your analysis) that are not identified by the wizard. My article Checklist for Memory Optimizations in PowerPivot and Tabular Models contains several best practices that you can apply to your data model.

New PowerPivot 2013 book available! #excel #powerpivot

sqlbi — Tue, 02 Apr 2013 11:25:00 GMT

Our new book about PowerPivot 2013 is finally available in printed edition, too!

The title is Microsoft Excel 2013: Building Data Models with PowerPivot and it is a partial rewriting of the previous book about PowerPivot for Excel 2010. In the previous book we had a target audience that included advanced Excel users and BI developers, because at that time there was no option to get the same engine in Analysis Services. But 30 months are elapsed, a new version of Analysis Services has been released and in this new book we focused mainly on Excel users. For this reason, we wrote a comprehensive book of all the feature of PowerPivot, but most important we tried to pass concepts of data modeling that might be pretty obvious for a DBA and a BI developer, but are completely new to an Excel user that never had the ability to create a data model with more than one table.

This book is focused on Excel 2013, so we included specific feature of this release related to PowerPivot, such as writing DAX queries and linked back tables, and features unique to Excel 2013, such as Power View. However, all of the PowerPivot features (so the 85% of the book) are good also for PowerPivot for Excel 2010 in its latest release (SQL Server 2012 SP1 PowerPivot for Microsoft Excel 2010), so you can safely use this book for both version of Excel.

You can download the first chapter of the book from the book page on SQLBI web site. And if you want to attend a training in a classroom or online, look at the complete list of available trainings on PowerPivot Workshop web site. The next online courses are scheduled on April 22-24, 2013 and June 17-19, 2013 (following online workshops are every other month).

Here are the links to directly order the book on Amazon around the world:

Amazon.com: hardcopy – kindle
Amazon.ca: hardcopy – kindle
Amazon.co.uk: hardcopy – kindle
Amazon.de: hardcopy – kindle
Amazon.es: hardcopy – kindle
Amazon.fr: hardcopy – kindle
Amazon.it: hardcopy – kindle

And here is the list of chapters:

Chapter 1 Introduction to PowerPivot
Chapter 2 Using the unique features of PowerPivot
Chapter 3 Introducing DAX
Chapter 4 Understanding data models
Chapter 5 Publishing to SharePoint
Chapter 6 Loading data
Chapter 7 Understanding evaluation contexts
Chapter 8 Understanding CALCULATE
Chapter 9 Using Hierarchies
Chapter 10 Using Power View
Chapter 11 Shaping the Reports
Chapter 12 Performing Date Calculations in DAX
Chapter 13 Using Advanced DAX
Chapter 14 Using DAX as a Query Language
Chapter 15 Automating Operations Using VBA
Chapter 16 Comparing Excel and SQL Server Analysis Services

This book should help you starting with PowerPivot at the very beginning, and you will probably use only the first chapters at that point. Over time, you will use following chapters and will learn more advanced techniques. This is not a book you can digest in a couple of days (after all, it is 500 pages long!), it will be your companion for several months, until you will master PowerPivot!

Update to the PowerPivot for Excel 2013 licensing #powerpivot #excel

sqlbi — Wed, 27 Feb 2013 14:21:00 GMT

~~Rob Collie~~ Ken Puls (thanks Bob!) wrote a very interesting post about the PowerPivot for Excel 2013 licensing.

Short recap:

PowerPivot for Excel 2013 is only available in a few editions of Excel/Office (more details here)
If you want to buy a perpetual license of Excel with PowerPivot, you need to be included in a Volume Licensing program
The easiest way to enter a volume licensing program is buying 5 licenses for Microsoft products, even different ones
The cheapest license you can buy is around 7$ (i.e. Microsoft DVD Playback Pack for Windows Vista Business)
Buying 4 of them grants you the ability to buy Office 2013 Pro Plus license as fifth product to enter the volume license agreement

This really does not make any sense to me, and like everyone I hope Microsoft will fix the licensing issues that could limit the adoption of PowerPivot for Excel 2013. In the meantime, there is a way to get the product spending just 30$ more.

The Right Version of Excel 2013 for using PowerPivot #powerpivot #excel

sqlbi — Mon, 18 Feb 2013 11:25:00 GMT

Many people started using PowerPivot with Excel 2010. In order to start using PowerPivot for Excel 2010, you just have to download the add-in and install it for free. In Excel 2013, PowerPivot is already installed and you just have to enable it. However, you have to be careful about the Excel 2013 version you use, because not all the versions have all the features available.

Data Model features available in all Excel 2013 versions

Internal xVelocity engine
Load multiple tables in a data model
Create relationships
Navigate a data model with multiple tables using a single PivotTable
Use only implicit measures

PowerPivot features available in selected versions of Office 2013 (*)

Create calculated columns
Create calculated fields
Use PowerPivot window and all the other features available there
Also Power View is available only in these versions of Excel

So what are the version of Office 2013 that enable the usage of PowerPivot features?

Here is the list:

Office Professional Plus 2013 via Open, Select or EA (Volume Licensing)
Excel 2013 standalone via Open or Select (Volume Licensing)
Office 365 ProPlus via Office 365 (www.office365.com) Subscription when it becomes available (February 27, 2013)

The only way to get these full BI features is through a Microsoft Volume License Agreement or Office 365 service. If you are not included in a Microsoft Volume License Agreement, the only way to get a copy of Excel 2013 that has all PowerPivot and Power View features available is getting an Office 365 ProPlus subscription.

UPDATE Feb 27, 2013: read about a workaround to get a Volume License Agreement for just 30$.

This might disappoint those of you that are used to buying a single license that never expires, but there is a good reason to move to Office 365 for using BI features: in the upcoming months and years, you will automatically receive updates of Office before perpetual (non-subscription) customers, and Excel will increase the number of BI features available at a faster cadence than ever before (yes, they promised it!). If you attended MS conferences and/or watched some of the last keynotes of BI speeches, you might have already seen some interesting previews (i.e. 3D mapping with GeoFlow for Excel), and probably more is coming.

Again, it’s important to know that Office Standard 2013 does not include Business Intelligence features, so all the options available when you need fewer than five licences does not include PowerPivot and Power View. You have to get a subscription of Office 365 ProPlus in this case, and the only action you can to today is using the free preview of Office 365 ProPlus until the end of February, when such a subscription will be commercially available.

I have seen some confusion in these first days of Office 2013 availability and for this reason I think it is important to clarify what is the right version of Excel you have to buy in order to use PowerPivot. I will update this blog post as soon as the Office 365 ProPlus will be commercially available.

New PowerPivot Workshop for Excel 2013 available! #powerpivot #excel2013 #excel

sqlbi — Mon, 04 Feb 2013 12:23:00 GMT

The Microsoft has launched the new Office 2013, we are proud to announce the availability of the new PowerPivot Workshop for Excel 2013! We have a fresh new website that offers all the training options: online and classroom courses. The target of this training are Excel Advanced Users and there are two versions of the workshop: the BASIC version is 2-day long and is good for everyone who want to start his experience with PowerPivot; the FULL version is 3-day long and the additional day is dedicated to more DAX content and practice, so also the more advanced users can satisfy his needs. All these versions are based on our upcoming Microsoft Excel 2013: Building Data Models with PowerPivot book that will be available in March 2013 (workshop’s attendees receive a free copy of that book).

The PowerPivot Workshop online is delivered every other month by me or Alberto and has different time scheduling depending on the delivery dates. We provide homework to students and use the available time online to teach content and answer attendees’ questions. The first online course will be delivered on February 19-21, 2013, and you have just one week to register using the use the Early Bird discount – more info for Basic and Full version following links.

We established partnerships with several authorized training centers around the world to deliver PowerPivot Workshop in classroom: in this case hands-on-labs assisted by the teacher are part of the course. Moreover, the PowerPivot course can be delivered in local languages: we report date, city, state, country and language of the Workshop in the list of courses available on our web site. We will publish more dates and locations in the coming weeks. Please note we are looking for training centers in states and countries we still don’t cover – we already have many ongoing discussions, but if you are interested just write us to get more info.

We'll also have more news for PowerPivot users in the coming weeks and months, which will be useful also to SSAS Tabular BI developers. The 2013 will be a long year!