Book Review: Instant Creating Data Models with PowerPivot How-to

I recently had the opportunity to review a pre-published copy of the “Instant” series book “Creating Data Models with PowerPivot How-to” by Leo Taehyung Lee, published by Packt Publishing.

This is a short, concise book; only 46 pages long.  Quite honestly, if I had picked up this book for myself, I wouldn’t have gotten much out of it.  But, I’m an IT Professional with a few year’s worth of experience with PowerPivot and I need to go beyond the basics.  Last week, I taught a PowerPivot/Excel 2010 class to a group of financial business users that covered getting started, the basics and some intermediate use scenarios; and I will recommend this book to every student in that class.  It’s a perfect fit.

Readers who are using Excel 2010 will find this a good fit for them.  The book was written using examples in Excel 2010 and then mentions that PowerPivot is also available for Excel 2013 but doesn’t explain the differences, which are many (a newer version of PowerPivot is part of the Professional Plus edition of Excel 2013).  Excel 2013 ProPlus users may be able to apply a good portion of the material but will encounter features on different ribbons and menus and other subtle differences between the two products that are not noted in the book.

Just like the title states, “Creating Data Models with PowerPivot How-to” is a short, fast and focused guide that will get a new PowerPivot user started and using the Excel add-in to answer real business questions and to get good use of this product.  It’s well-written guide, using clear and easy-to-understand language.  The basics are covered sufficiently without getting into the weeds and exploring complex features.  Topics are effectively illustrated and easy to find.  If you’re a non-technical business user and need quick, simple guide to get you started, this book is a good place to begin.

More information s available from the publisher here.

Time to Complete the 2013 BI Survey

It’s that time of year again… end of the school year, graduation, Fathers’ Day and of course; time to take the annual BI Survey!  The survey is an annual report conducted by Business Application Research Center (BARC), an independent software industry analyst, comparing the industry BI tools rated by thousands of industry professionals.  Participants will have access to a free version of the results that they can use to compare and rank each vendor tool on a variety of criteria.  BARC also sells a report of detailed survey results with exhaustive product and vendor background information.

Take the Survey Here

Taking the Tabular Journey

A Getting-Started and Survival Guide for planning, designing and building Tabular Semantic Models with Microsoft SQL Server 2012 Analysis Services.

by Paul Turley

This post will be unique in that it will be a living document that will be updated and expanded over time.  I will also post-as-I-go on the site about other things but this particular post will live for a while.  I have a lot of good intentions – I know that about myself and I also know that the best way to get something done is to get it started – especially if I’m too busy with work and projects.  If it’s important, the “completing” part can happen later.  In the case of this post, I’ll take care of building it as I go, topic by topic.  Heck, maybe it will never be “finished” but then are we ever really done with IT business solutions?  I have been intending to get started on this topic for quite some time but in my very busy project schedule lately, didn’t have a concise message for a post – but I do have a lot to say about creating and using tabular models.

I’ve added some place-holder topic headers for some things that are on my mind.  This list is inspired by a lot of the questions my consulting customers, IT staff members and business users have asked me on a regular basis.  This will motivate me to come back and finish them and for you to come back and read them.  I hope that you will post comments about your burning questions, issues and ideas for related topics to cover in this living post about tabular model design practices and recommendations.

Here goes…

Why Tabular?

 

What About Multidimensional – Will Tabular Replace It?

SQL Server Analysis Services is a fine product and it works well in many scenarios.  The fact is the multidimensional databases (which most casual SSAS users refer to as “cubes”) will be supported for years to come.  The base architecture for SSAS OLAP/UDM/Multidimensional is about 13 years old since Microsoft originally acquired a product code base from Panorama and then went on to enhance and then rewrite the engine over the years as it has matured.  In the view of many industry professionals, this is still the more complete and feature-rich product.

Both multi and tabular have some strengths and weaknesses today and one is not clearly superior to the other.  In many cases, tabular performs better and models are more simple to design and use but the platform is lacking equivalent commands and advanced capabilities.  In the near future, the tabular product may inherit all of the features of its predecessor and the choice may become more clear; or, perhaps a hybrid product will emerge.

 

Isn’t a Tabular Model Just Another Name for a Cube?

No.  …um, Yes.  …well, sort of.  Here’s the thing:  The term “cube” has become a defacto term used by many to describe the general concept of a semantic model.  Technically, the term “cube” defines a multidimensional structure that stores data in hierarchies of multi-level attributes and pre-calculated aggregate measure values at the intersect points between all those dimensions and at strategic points between many of the level members in-between.  It’s a cool concept and an an even cooler technology but most people who aren’t close to this product don’t understand all that.  Users just know that it works somehow but they’re often confused by some of the fine points… like the difference between hierarchies and levels.  One has an All member and one doesn’t but they both have all the other members.  It makes sense when you understand the architecture but it’s just weird behavior for those who don’t.

Since the tabular semantic model is actually Analysis Services with a single definition of object metadata, certain client tools will continue to treat the model as a cube, even though it technically isn’t.  A tabular Analysis Services database contains some tables that serve the same purpose as measure groups in multidimensional semantic models.  The rest of the tables are exposed as dimensions in the same way that cube dimensions exists in multidimensional.  If a table in a tabular model includes both measures and attribute fields, in certain client tools like Excel, it will show up twice in the model; once as a measure group table and once as a dimension table.

(June 9 draft – more to come)

Preparing Data for a Tabular Model

Data Modeling 101 for Tabular Models

Are There Rules for Tabular Model Design?

Tabular Model Design Checklist

What’s the Difference Between Calculated Columns & Measures?

What are the Naming Conventions for Tabular Model Objects?

What’s the Difference Between PowerPivot and Tabular Models?

How to Promote a Business-created PowerPivot Model to an IT-managed SSAS Tabular Model

Getting Started with DAX Calculations

DAX: Essential Concepts

DAX: Some of the Most Useful Functions

DAX: Some of the Most Interesting Functions

Using DAX to Solve real-World Business Scenarios

Do I Write MDX or DAX Queries to Report on Tabular Data?

Can I Use Reporting Services with Tabular & PowerPivot Models?

Do We Need to Have SharePoint to Use Tabular Models?

What Do You Teach Non-technical Business Users About PowerPivot and Tabular Models?

What’s the Best IT Tool for Reporting on Tabular Models?

What’s the Best Business User Tool for Browsing & Analyzing Business Data with Tabular Models?

Survival Tips for Using the Tabular Model Design Environment

How Do You Design a Tabular Model for a Large Volume of Data?

How Do You Secure a Tabular Model?

How to Deploy and Manage a Tabular Model SSAS Database

Tabular Model Common Errors and Remedies

Tabular Model, Workspace and Database Recovery Techniques

Scripting Tabular Model Measures

Simplifying and Automating Tabular Model Design Tasks

Power View for Multidimensional Cubes Released

The Power View connectivity for Multidimensional Models has been released to the public as part of SQL Server 2012 Service Pack 1 Cumulative Update 4.  This announcement was made by Robert Bruckner to the SQL Server BI community last night, on May 31, 2013.  The official public announcement, posted by Siva Harinath, is on the Analysis Service & PowerPivot Blog.

In March, I posted about the public preview of the “Microsoft SQL Server 2012 With Power View For Multidimensional Models”.  Well, the official release is now available for those currently using SQL Server 2012.  When the preview became available a couple of months ago, I was very excited to test it out so I downloaded it, quickly scanned the release notes and then proceeded to upgrade an existing SQL Server 2012  SP1 development server.  What I missed in the release notes was the requirement to uninstall several existing components and then to install them from scratch.  That wasn’t as easy as I had hoped but it’s pretty typical for prereleased software to not include upgrade support.  After all, the product teams are focused on finishing features and debugging and not getting all the upgrades and installation details sorted out.  Those steps usually happen last in the release cycle.

Not to worry, this new capability is now part of the Cumulative Update 4 for SQL Server 2012. This means that it will be fully-supported as an upgrade to an existing SQL Server 2012 installation.  This is very exciting news.  If you have seen Power View demonstrated with new SSAS tabular models and PowerPivot models in Excel and SharePoint, you know what a simple and powerful data browsing and visualization tool it is.  Some people have been a little disappointed that Power View initially only worked with new xVelocity-based tabular models and not the multidimensional cubes built with SQL Server Analysis Services, that have become common in many Microsoft centered IT shops throughout the industry.

The Microsoft product teams have shared a lot of good news, like this, recently about BI innovations – with Power View in Excel 2013 and GeoFlow recently released.  They are likely to share even more good news in the weeks and months ahead.  It’s an exciting time to see some very impressive, powerful, fun to develop and fun to use BI business and IT tools all coming together to meet very real business problems.

I don’t know about you but I’m going to get this baby installed and working right away.  I have clients who have been waiting patiently (and some not so patiently) to use Power View with their existing cubes.  I love to be the bearer of good news.

As is usual when something noteworthy happens on the Microsoft BI community, Chris Webb has blog eloquently on the topic and with significant detail.  Read today’s post on his blog here.

GeoFlow Released for Public Preview

In this morning’s keynote address at the PASS business Analytics Conference, Amir Netz demonstrated using GeoFlow, a geographic data visualization add-in for Excel 2013, with a set of data showing the success of pop starts and their countries of origin.  It was a very interesting display of 3D geographical data.  Amir gets so excited when he does these demonstrations and his passion for these new BI tools is infectious.

The public beta for GeoFlow is released publically today and you can download the installer from here.  I blogged about GeoFlow in a short video presentation titled SQL Server Community World Tour with GeoFlow.  I need to update my very brief set of data but it does show some of the capabilities of an earlier private preview version of the tool.

From the official Microsoft Connect page for GeoFlow, this is what you can expect to see in this release:

The GeoFlow Preview for Excel contains the following new features:

• A single install for 32-bit and 64-bit versions

• Geo-disambiguation dialog showing what values were not geocoded

• Support for Tables which have Relationships

• Add Data button in the Excel Ribbon, part of the Map split button which allows you to add data from your sheet to the Data Model after selecting it

• Refresh Data button in the GeoFlow ribbon

• 2D charts and brushing

• Touch navigation and gestures

• Navigation controls

• New tour model with no need to save or refresh your scene

• Transitions and Effects

• Improved performance

• Many improvements in the UI – ribbon, time controls, navigation controls

You can read more details in the Getting Started document.

GeoFlow can be installed here.

(Apr 14 13 Update) – GeoFlow requires Microsoft Office 2013 Pro Plus.  Make sure you have that edition of Excel installed and then download and install the add-in.  After that,  open a document in Excel, go to File > Options and Add-Ins, select to manage COM Add-ins and hit the Go button.  Check the GeoFlow for Microsoft Excel item and click OK.  That should do it!

You’ll find the GeoFlow option on the Insert ribbon, GeoFlow group and the Map drop-down button.

If you can’t get to the content on the Microsoft Connect site, try this link.  You must log into the Connect site using a Microsoft account like you would to use HotMail.

Report Expression Sample Reports

These are the sample reports from the Penton eLearning session today on SSRS Report Expressions:

http://sqlserverbiblog.wordpress.com/?attachment_id=1058

This view is necessary to add to the AdventureWorksDWxxxx database:

http://sqlserverbiblog.wordpress.com/?attachment_id=1061

Mastering Reporting Services Expressions, March 26

As part of SQL Server Pro eLearning series, I’ll be presenting three 75 minute sessions on March 26th on using Expressions to enhance report capabilities.  The sessions are online with live chat from 11:00 to 4:30 Eastern Time.

Register today to get in on the early-bird discount. 

Visual Studio 2012/SSDT Addition for BI Project Version Compatibility & Themes

We can now use Visual Studio 2012 to create and edit SQL Server BI projects!  Woo Hoo.

In the past, if you wanted to develop and maintain BI projects in the same development environment as other Visual Studio projects, you had to install multiple versions of Visual Studio.  This may no longer be necessary if you install the new Business Intelligence for Visual Studio 2012 (SSDT BI). 

SQL Server Community World Tour with GeoFlow

When I started playing with GeoFlow, I just had to find a way to do something interesting with it.  This is a short world tour with visits to a few SQL Server community members and leaders around the globe talking about their SQL Server communities.  GeoFlow is a new BI tool currently in development at Microsoft.  It’s an add-in for Excel 2013 using the SQL Server Analysis Services tabular in-memory aggregation that plots and visualizes data points on the globe using imagery from Bing Maps.  After visualizing data on a map, you can create a 3D tour with full zoom and navigation in 3D geographical space.

Thanks to my friends and associates who have contributed to this effort thus far.  This is the first draft so please check back and I’ll add more stops on the tour along with more sophisticated data points.  The dataset in my current GeoFlow workbook is very light and with more contributions, I’ll can add creative ways to visualize the global SQL Server world community.  Enjoy!

SQL Server World Tour

►

 

GeoFlow is still in development and not yet generally available.  It’s too soon to foresee when it will be complete enough for a public preview and then for release but I’ll let you know what I know when I know it.

Please Vote on Microsoft Connect Items

I’ve posted two feedback items on the Microsoft Connect today, suggesting feature enhancements to Power View and Reporting Services.  If you think these capabilities would be important, I encourage you to follow the links below to the Microsoft Connect site and vote on these items.

Data Visualization Choices SQL Saturday Presentation

Today in Vancouver, British Columbia, at the SQL Saturday #198; I presented a session titled “Data Visualization Choices”.  As promised, my slide deck is available for download here. 

This is the first draft of the session I’m preparing for the PASS Business Analytics Conference coming up on April 11-12 in Chicago.  I’ll have another update for that conference.

Speaking at the Business Analytics Conference, April 1-12 in Chicago

The email message earlier today said “Please note that the official schedule will be released tomorrow, February 6^th, so please do not share your schedule until that time.”  Well, what does that mean, exactly?  It means that it’s midnight right now and time to tell the world….  We are going public, people!  The PASS Business Analytics 2013 session schedule has been completed, the speakers are working on their presentations, feverously practicing their demo material and rehearsing  in the mirror, to no one in the car, to the kids and to the dog who already knew you were crazy before all this mad ness began.  Yep, we take this stuff pretty seriously.

Reporting Services 2012 BI Training

Just wanted to get the word out that our SSRS 2012 training course is available.  Are you looking for expert training on SQL Server 2012 Reporting Services?  SolidQ now offers a SSRS training course with focus on designing report solutions for  Business Intelligence and SharePoint integration.  Not using SharePoint?  Private classes can be adapted to use Reporting Services in native mode.  Training is available for public and private events, onsite or remote delivery.

Designing BI Report Solutions with SQL Server 2012 Reporting Services and SharePoint 2010

Is Self-service BI Threatening IT Professionals’ Livelihoods?

(this is another follow-up to Chris Webb’s post on “Why Corporate BI and Self-Service BI Are Both Necessary”)

There is little doubt that the movement toward self-service BI is going to change the climate for some IT professional who specialize in BI – but the question is “how?” and “how much?” 

Debugging Parameter Dependencies in Reporting Services

Creating a report with two datasets that reference the same pair of parameters, I’m seeing the following error:

The Value expression for the query parameter ‘DatesYear’ contains an error: The expression references the parameter ‘DatesYear’, which does not exist in the Parameters collection. Letters in the names of parameters must use the correct case. (rsRuntimeErrorInExpression)
—————————-
An error has occurred during report processing. (rsProcessingAborted)