Microsoft Power BI Reviews

Power BI: a new suite of Business Intelligence tools

Over the past few months, teams at Microsoft have made several new Business Intelligence tools available for preview; some only privately and some to the public.  The entire suite will soon be available for either public preview or release under the new name: “Power BI”.  All of the components of Power BI are listed below but the big news is a new hosted offering called “Power BI for Office 365” and “Power BI Sites”.  The announcement was made at the Worldwide Partner Conference this week.  Users can sign-up to be notified when the new offerings are available for general availability, apparently in the very near future.  I’ve had an opportunity to work with early, pre-released versions and it has been interesting to see the gaps being filled a little at a time.  On the heals of the new suite, some of the names of existing products are also being changed.  It’s hard to have a conversation about the collection of Microsoft’s “Power”/”Pivot”/”Point”…named tools and not get tongue twisted but these changes bring more consistency.

Bottom line: this is good news and a promising step forward – especially for smaller businesses.  Larger, enterprise customers should know that this move is consistent with Microsoft’s “cloud first” philosophy and these capabilities are being introduced through Office365/Azure platform with required connectivity.  Read the commentary on community leaders’ sites below.  I have no doubt that there will be a lot of discussion on this in the weeks to come with more announcements from Microsoft in the near future.

Power BI for Office 365 and Power BI Sites

When Power View was released with SQL Server 2012 Enterprise and Business Intelligence Editions, it was available only when integrated with SharePoint 2010 Enterprise Edition.  This is a good solution for enterprise customers but it was complex and expensive for some to get started.  Power View was also offered only as a Silverlight application that wouldn’t work on many mobile devices and web browsers.  For this reason, Power View has really been viewed as a “Microsoft only” tool and only for big companies with deep pockets and very capable IT support groups.  Even the new Power View add-in for Excel 2013 ProPlus Edition requires Silverlight which is not a show-stopper for most folks but a hindrance for multi-platform and tablet users.  This all changes with this new offering as the Power View visualization tool in the hosted product come in 3 new flavors: native Windows 8 app (runs on desktop, Surface RT & Pro), native iOS (targeting the iPad) and HTML5 (works on practically any newer device).  This means that when you open a Power View report on your Surface or iPad, it can run as an installed app with all the cool pinch-zoom and gestures you’ve come to expect on a tablet device.  For now, this is good news for the cloud user as no on-premises option is currently available.  An interesting new edition will be the introduction of a semantic translation engine for natural language queries, initially for English.

Power Query

Formerly known as “Data Explorer”, this add-in for Excel 2013 allows you to discover and integrate data into Excel.  Think of it as intelligent, personal ETL with specialized tools to pivot, transform and cleanse data obtained from web-based HTML tables and data feeds.

Power Map

This Excel 2013 ProPlus add-in, which was previously known as “GeoFlow”, uses advanced 3-D imaging to plot data points on a global rendering of Bing Maps.  Each data point can be visualized as a column, stacked column or heat map point positioned using latitude & longitude, named map location or address just like you would in a Bing Maps search.  You can plot literally thousands of points and then tour the map with the keyboard, mouse or touch gestures to zoom and navigate the globe.  A tour can be created, recorded and then played back.  Aside from the immediate cool factor of this imagery, this tool has many practical applications.

Power Pivot

The be reveal is that “PowerPivot” shall now be known as “Power Pivot”.  Note, the space added so that the name is consistent with the other applications.  We all know and love this tool, an add-in for Excel 2010 and Excel 2013 ProPlus (two different versions with some different features) that allow large volumes of related, multi-table data sources to be imported into an in-memory semantic model with sophisticated calculations.  On a well-equipped computer, this means that a model could contain tens of millions of rows that get neatly compressed into memory and can be scanned, queried and aggregated very quickly.  Power Pivot models (stored as an Excel .xlsx file) can be uploaded to a SharePoint where they become a server-managed resource.  A Power Pivot model can also be promoted to a server-hosted SSAS Tabular model where data is not only managed and queried on an enterprise server but also takes on many of the features and capabilities of classic SSAS multidimensional database.  Whether a Power Pivot model is published to a SharePoint library or promoted to a full-fledged SSAS Tabular model, the data can be queried by any client tool as if it were an Analysis Services cube.

Power View

For now, Power View in Excel 2013 ProPlus and Power View in SharePoint 2010 Enterprise and SharePoint 2013 Enterprise remain the same – the Silverlight-based drag-and-drop visual analytic tool.  With the addition of SQL Server 2012 CU4, Power View in SharePoint can be used with SharePoint published Power Pivot models, SSAS Tabular models and SSAS Multidimensional “cube” models.  There has been no news yet about a non-Silverlight replacement for the on-premise version of Power View.  The Microsoft teams and leadership have heard the requests and feedback, loud-and-clear, from the community and we can only guess that there is more is in-the-works but I make no forecast or assumptions about the eventual availability of an on-premise offering similar to Power BI for Office 365.

Here’s a demonstration that Amir Netz did of the new Power BI features at the World Wide Partner Conference this week

Additional thoughts and information from the community can be found at:

Chris Webb: Some Thoughts About Power BI

Andrew Brust: Microsoft Announces Power BI for Office 365

SQL Server Blog: Introducing Power BI for Office 365

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, students, 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.

Why Tabular?

SQL Server Analysis Services is a solid and mature platform that now serves as the foundation for two different implementations.  Multidimensional models are especially suited for large volumes of dimensionally-structured data that have additive measure values that sum-up along related dimensional attributes & hierarchies.  

By design, tabular architecture is more flexible than multidimensional in a number of scenarios.  Tabular also works well with dimensional data structures but also works well in cases where the structure of the data doesn’t resemble a traditional star or snowflake of fact and dimension tables.  When I started using PowerPivot and tabular SSAS projects, I insisted on transforming data into star schemas like I’ve always done before building a cube.  In many cases, I still do because it’s easier to design a predictable model that performs well and is easy for users to navigate.  A dimensional model has order and disciple however, the data is not always shaped this way and it can take a lot of effort to force it into that structure.

Tabular is fast for not only additive, hierarchal structured data but in many cases, it works well with normalized and flattened data as long as all the data fits into memory and the model is designed to support simple relationships and calculations that take advantage of the function engine and VertiPaq compression and query engine.  It’s actually pretty easy to make tabular do silly, inefficient things but it’s also not very hard to make it work really well, either.

James Serra has done a nice job of summarizing the differences between the two choices and highlighted the strengths and comparative weaknesses of each in his April 4 blog post titled SQL Server 2012: Multidimensional vs Tabular.  James points out that tabular models can be faster and easier to design and deploy, and that they concisely perform well without giving them a lot of extra attention for tuning and optimization.  Honestly, there isn’t that much to maintain and a lot of the tricks we use to make cubes perform better (like measure group partitioning, aggregation design, strategic aggregation storage, usage-base optimization, proactive caching and cache-warming queries) are simply unnecessary. Most of these options don’t really exist in the tabular world.  We do have partitions in tabular models but they’re really just for ease of design.

What About Multidimensional – Will Tabular Replace It?

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.

(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

You drive adoption of BI through three approaches; firstly you set adoption as a goal, secondly you make your content compelling and ONLY THIRDLY do you think about tools.  Too many people focus on item 3 and their BI doesn’t penetrate the organisation like they wanted it to and therefore doesn’t deliver the benefits they sought.

It may sound strange to set adoption as a goal, however we have all worked in organisations that have taken an IT-led or procurement-led approach to BI without sitting back and working out what the BI is for.  In the NHS we have a lot of BI projects that work like that.  The goal might be to recreate the old reports on a new platform that looks more shiney and therefore will be used.  The goal might be to implement something the CEO saw at a trade fair.  Sometimes the goal might be to implement something that should deliver a performance framework (people, processes and technologies) that will show how a division is making a contribution to a national strategic agenda, local operations or delivery of service line responsibilities.

When you get into this area you are starting down the right road but the wheels come off if this top-level intention is not enshrined in operational delivery methods.

I often meet organisations that bought a reporting solution because they were going to implement service line reporting.  There is a particular reporting application vendor that is doing quite well out of this trend just now, with a high number of wins but a questionable level of adoption.  Their software looks cool. It has in-memory BI and therefore you can get going with it pretty quickly.  The licensing model means it is quite attractive for PoC work.  Moreover the pitch really talks to the value of self-service BI as an enabler of behavioural change and therefore performance improvement.

Obviously I am not talking about the Microsoft stack here.

Contrast this with the perception of the Microsoft stack, though, for a minute.  Enterprise-class solution, feature rich and therefore perceived as expensive, not nimble and therefore not suited to quick PoC work – often we here this story.  Not true, my friends.  Not true.  We have done a fair few PoCs on the platform and scaled them out quickly and relatively inexpensively – so it can be done.  But in terms of this blog the point is that the reporting solution I was talking about is very costly to scale and therefore that is a barrier to adoption.

So, we agree.  The best way to achieve adoption is to set it is a target and focus on delivery.  Put information in the hands of decision makers and they will make better decisions – give them a shiney tool and they may or may not.

The key point is achieving the link between the evidence and the decision – in other words creating compelling content.  Compelling content will provide decision-makers with what they feel they need in order to do their job.  It’s not difficult to understand that.  I favour the agile software development approach of collecting a user story, such as:

This tells me what the Theatre Scheduler considers to be compelling so that I can work out the data he needs (session times, staff, work done etc) and then how to render it in the fewest clicks.

After all that I can then worry about tools……guess which ones I would use blog-readers!

Disclosure: The company I work for is a Microsoft Partner

I know that Microsoft have promised that they will be playing catch up later this year in terms of Mobile BI, and for sure they need to. Offerings available right now from the likes of Cognos, QlikView, Business Objects and Microstrategy (current personal favourite) far outstrip what you can do with the current Microsoft stack. But what does work?

Well, from what I can see, anything Sliverlight based is out, so that rules out PowerView and Decomp Trees in PPS. It seems that most of the other things work though, so there’s much you can still do.

Having managed to blag a company iPad in my new role as Reporting & Analytics lead, I figured I’d hook it up to the MSFT 2008R2 demo server I built that currently hosts some of Logica’s Spark Centre demos. Having installed Junos Pulse, a VPN app that allows me to securely connect to my work network, I found that the Sharepoint “pretend Telco” site renders quite well on the iPad.

Firstly, I checked out SSRS… There’s no right click on iPad so you need to hit the screen over any drop downs and wait a moment for the selections to pop up.

Looks pretty good… Next was PPS – remember, no Silverlight and doesn’t look like drill downs are fully working, but still able to do things like selecting chart items, changing from chart to grid and most impressively, export to Excel and PowerPoint works just fine (providing you have an Office programme installed such as QuickOffice, Docs to Go etc). Click Export to Excel and you get a choice..

And here’s the report in QuickOffice

Last thing to try was Excel services. Here, the Open in Excel function does not work. Apparently, there is no fooling it in to accepting being opened in a cheap substitute ;) but the charts look OK…

So, not perfect, but not all despair, and I’m assured that there are lots of goodies to come later in the year once the SQL2012 launch is out of the way. Still, it will need to be good to match my current favourites… If you get the chance, have a look at the Microstrategy iPad app….

And the nice app from RoamBI

This Post is not about Microsoft BI VS Sap BI. NO. NO. NO.

Then What is it?

well, I have been playing with SAP’s Netweaver BW Tools for past three months now as a part of a Business Intelligence class that’s about to conclude – Also, I have been involved with work on Microsoft’s SQL server Business Intelligence Tools. So I thought – it would be FUN to map  SAP Netweaver BW Tools (that I got to play with in an academic capacity) and Microsoft’s Business Intelligence Tools (which is currently what I am working on) – so, here you go:

Note about SAP BusinessObjects: I mapped the Tools in Microsoft BI with the tools that I got to study in my SAP class. Then I was searching what’s the current scenario in SAP world (I know about Microsoft’s!)– I learned that SAP BI world is comprised of TOOLS in SAP Netweaver BW + SAP BusinessObjects (BO). And in the course I studied the following components of Business Objects:

1. Web Intelligence for ad-hoc query and reporting
2. Crystal Reports for enterprise reporting
3. Xcelsius (BO Dashboard) for Dashboard designing

For those interested I am also mapping few terms used while cube development in Microsoft BI and SAP Netweaver BW

Note:

1) I have not mapped the Tools in Self Service BI space.

2) This comparison is not for deciding between Microsoft BI vs. SAP Netweaver BI/SAP BusinessObjects – this post is just meant for mapping tools available in Microsoft BI and SAP Netweaver BW and so if you are an expert in say Microsoft BI – this post will help you see what corresponding tool are available in SAP Netweaver BW world. Consider it as a starting guide for your research.

3) Note the date the post was written – the name of the products may have changed in future. refer to official sites for latest & greatest!

Thanks for reading.

This post was republished from Mapping Business Intelligence Developer’s Tools: Microsoft SQL server & SAP Netweaver BW

A conversation on Twitter earlier this week has inspired me to pose this question…Is Report Builder the “Red-Headed Stepchild” when it comes to the Microsoft Self-Service BI toolset?  Report Builder doesn’t get much attention these days, for various reasons we’ll discuss below.

(Note:  The perspective of this blog entry is Self-Service BI so Report Builder is the reference – however, everything stated is applicable to full-fledged Reporting Services as well.)

Is Report Builder Deprecated?

Nope!  Report Models, however, are deprecated.  As of SQL Server 2012, you can no longer create or update Report Models (SMDL files).  You can continue to use Report Models currently, but it is recommended to move away from them as time allows.  Report Builder itself is at version 3.0 and is a mature product.

Self-Service BI Tools

The primary set of Microsoft Self-Service BI tools includes Excel (+ add-ins for data modeling such as PowerPivot), Power View, and Report Builder.  Some people actually wouldn’t include Report Builder in this list, but I do believe it has a valid place as one of the Self-Service BI tools (albeit, a smaller audience & used for specific purposes).

Drawbacks of Report Builder

• Learning curve / ease of use for report designer.  There are a significant number of properties and options.  This offers significant control over the output – the cost for this significant control is ease of use because all the options in Report Builder can be a bit overwhelming for the casual business user.  However, it’s not overly difficult to use for technically adept users who enjoy working with reporting tools and data.
• Limited interactivity.  While there are some interactive features (such as drill-down, drill-through, sortable columns), each has to be explicitly defined by the report designer.  Report Builder isn’t dynamically interactive like Power View or even Excel – rather, Report Builder is far more suitable for fully formatted reporting needs.
• Longer to develop.  There’s some things that can be done with Power View or Excel that are inherently more work to do in Report Builder.  The first example that comes to mind is hierarchies – with Power View or Excel, you drop a hierarchy onto the row & the navigation up/down works; with Report Builder you’d have to set up what is shown vs. hidden and the toggle properties.  This is not overly difficult to set up, but could be frustrating for someone just getting started with Report Builder.
• GUI support is limited.  There is a drag & drop graphical interface for SQL Server and Analysis Services data sources (plus a couple of others).  This limited support leaves the report designer writing query syntax sometimes – which is obviously not the most user-friendly for non-technical folks.  Currently the nicest way for users to work with Report Builder is using a BISM data source (i.e., the data is stored in Analysis Services or PowerPivot).  SQL Server (relational) can be ok for users to work with if the data sources are made to be understandable & easy to work with (ex: with reporting views or stored procedures) – this takes some effort from the IT Dept. or BI Center to make sure it’s made suitable for self-service.

Positives of Report Builder

• Native connectivity to many data sources, including BISM.  The Microsoft BI framework is very different from other BI tools (such as Cognos or Business Objects) which require a metadata layer – i.e., a report model between the data source & the reporting tool.  Microsoft permits tremendous flexibility here – in fact, you can natively send queries from Report Builder to a variety of databases including non-Microsoft.  Power View is very limited in terms of accepted data sources, and Excel can be somewhat limited (unless you bring the data into an intermediary PowerPivot model first – PowerPivot offers great flexibility in this regard, but do realize you are storing the data redundantly). 
• Significant formatting control.  If you need a pixel-perfect highly formatted report, Report Builder is the tool for you.  Ironically, this is the inverse of the “learning curve / ease of use” drawback listed above.  With Excel you can exercise a lot of control over the look & feel of your report (except Pivot Tables – you have to use formulas if you need to "break” out of the Pivot Table).  Power View has some text size control and some color schemes to choose from, but overall offers minimal user control over formatting (after all, it’s a data discovery tool meeting an entirely different need – and it is purposely trying to remain simple).
• Consistent RDL file format.  If a business user starts a report in Report Builder and needs some help with it, one of the IT or BI folks can open the report using their tool of choice (BIDS or SSDT in Visual Studio), make some updates, and send it back to the user with the file format intact.  The consistent format is also helpful if a report is being promoted from the Self-Service environment to the Corporate BI environment.
• Reusable elements.  To facilitate Self-Service BI using Report Builder, things such as shared data sources (reusable data connections) are obvious but there are others as well.  Shared datasets (reusable queries) can be really helpful to handle common things like Dates and Geography.  Report parts (reusable charts, graphs, tables) can be helpful to display commonly used elements.  Images can also be stored centrally for reuse.  Setting up reusable elements does take some effort from the IT Dept. or BI Center though, but can improve the Self-Service user’s experience tremendously.
• Parameterization.  Reports with a number of parameters (within reason of course) can sometimes be thought of as “guided ad-hoc analysis” because one report can yield many different combinations of results depending on parameter values.  Report Builder handles parameterization well.
• Subscriptions and alerts.  If you wish to have reports delivered to you at a predefined frequency or based upon a condition, then Reporting Services is the tool to make that happen.
• Export and RSS capabilities.  Report Builder can export to many different file formats.  It can also publish an RSS feed – this can be very useful for a business user to consume existing aggregated/calculated data that has been rendered by Report Builder without recalculating or reinventing anything.  Utilizing published report data via RSS also helps with the elusive “one version of the truth” that’s a constant challenge.
• Integration with SharePoint.  With a Report Builder report, you can view or edit the report directly from the SharePoint document library (with appropriate permissions of course).  Alternatively, a Report Manager portal can be used (although it would be used in isolation from other BI tools and reports).

So, even though sometimes Report Builder seems to be the “Red-Headed Stepchild” I very much appreciate having the tool in our toolbox.  I hope it’s alive and well for a long time. 

Got any thoughts on this subject?  Leave a comment … I’d love to hear your thoughts.

Disclosure: The company I work for is a Microsoft Partner

After working on a couple of serious tabular model projects, I’ve been talking to other BI practitioners lately about their thoughts regarding tabular model technology and I’m finding that there is little consensus among all these industry experts about which option to recommend for new BI projects.  At SolidQ, I work with some of the most experienced Business Intelligence experts in the industry.  At conferences and in discussion groups, there has been a lot of discussion about the choice between Analysis Services multidimensional models and the newer tabular semantic model technology introduced in SQL Server 2012.  There are pros and cons for either side but I think there are some important factors to consider.  Among some of the most experienced Analysis Services consultants, I have heard that some are primarily using and recommending tabular for most new projects and others are arguing that tabular offers little significant value over the tried-and-true multidimensional cubes and MDX script solutions.

As is typical for a newer product, the usability experience for the model designer isn’t perfect.  In some ways, it’s different and just may take some getting used to, but in other ways there is clearly room for improvement.  The question now is; do the advantages of tabular vs multidimensional outweigh the somewhat rocky design experience?  I’m not taking sides in this comparison but merely offering an objective analysis of the state of tabular model option in SQL Server 2012 Analysis Services, Service Pack 1.  I expect this product to go through changes so for future reference, this information is for the released product at the time of this posting in June of 2013.

The Good

• The core VertiPaq (or xVelocity) query and aggregation engine is stable & reliable.  Originally developed about five years ago and released with PowerPivot for Excel and SharePoint in SQL Server 2008R2 over three years ago, this technology has proven to be ready for serious use.

• Under most conditions, for analytic reporting, data scanned and aggregated from an in-memory data structure performs faster than other conventional options; including relational and multidimensional storage.

• Tabular can be less complex than multidimensional, OLAP SSAS.  The core design and usage concepts are easier for both those who design models and for those use use them for analysis and reporting.

• Tabular models can be easier & faster to implement because the model structure is simpler and there may be fewer steps in the design process.

• DAX, the core calculation expression language for tabular models, is fairly easy to learn.  Fundamental DAX expression concepts can be easier to understand than equivalent MDX commands used in multidimensional modeling and calculations.

The Bad 

• Comparing features, tabular can be hard to justify when compared to multidimensional.  Traditional SSAS cubes still have more capabilities than tabular models and for someone who already has OLAP skills and background, tabular design is a new learning curve.

• PowerPivot is a version 2 product.  As such, it’s simple and fairly reliable in the majority of design scenarios.  In some, mostly rare, cases, error handling and debugging capabilities aren’t as mature and robust as more tenured technologies like SSAS multidimensional or relational.

• Tabular SSAS is a version 1 product.  As such, it has a list of well-known design annoyances and features that could (and should) improve in the next product go-round.

• The recommended design patterns & best practices, both from Microsoft development and support teams and from members of the expert practitioner community,  are still emerging.                         

• One model.bim file = one developer working at a time.  A departure from the long-time SSAS project development environment where every object (such as data sources, dimensions and cubes) were defined in isolated files managed by the project; SSAS tabular manages all of the database objects in a single model definition file.  The model designer treats the model is more of a black box than a solution comprised of independent objects.  However, the fact is that most of the same objects we work with in multidimensional/cube projects are all defined as XML elements in this file.   It can be opened and properties manipulated,and there are a growing number of third-party tools to provide enhancements.  Regardless, it is one big project file tat would need to be checked-out in source code management as a single project asset.

• The tabular SSAS support community is thriving but still small.  A core group of trusted professionals from all over the world are the loudest voices right now.  They promote tabular solutions and provide active and collective support for one another.

• The DAX expression editor in PowerPivot & the Visual Studio-based SSDT designer is quirky.  You have to know is strengths and weaknesses and be willing to use it in that context.  It attempts to assists with code completion but in the end, it doubles-up brackets and duplicates, rather then replaces old code, that it suggests.  No doubt that the experience will get better as enhancements are developed and released but we must live with a product today that is useful and reliable a lot of the time but, it leaves plenty of opportunity for improvements.

• The entire tabular model must fit in memory.  There’s no caching, swapping or distributed processing option for large models.  This means that very large models need very large hardware and there is currently no scale-out option to distribute work loads or fail-over.  Got a lot of data?  Get a lot of memory.

The Ugly

• After you get into serious data with 30, 40 or 50 tables and some complexity to your model, the version 1.0 SSDT tabular model designer can be cumbersome and error-prone.  I’ve seen it stop responding and throw errors when there were no errors.  I will say that it’s helpful and reliable most of the time but on occasion, when it falls down, I often save and close my work; shut down Visual Studio all together and the fire it back up.

• My biggest peeve about the SSDT model designer is that all design work is performed while connected to the workspace database.  This means that for every table and column rename,and for every single property setting change, this information is written to the model workspace database on the local SSAS tabular server, and after the changes have been committed, control is given back to the model designer.

• Some key SSAS features are not currently supported.  These include things like parent-child hierarchies, many-to-many relationships, cube actions and display folders.  Actually, this is not entirely true; actions and display folders can be added using after-market tools (like BIDS Helper, available in the CodePlex library) and by hand-writing the XMLA script, but they are not currently supported through the SSDT model designer.  There is simply a category of features that didn’t find their way into this first version of the product.  There are work-arounds and methods to enable these capabilities but they’re not supported, or at least not to the the same degree as they are in multidimensional SSAS.

The Beautiful

• There is no doubt that in-memory, tabular model technology is the promise of the future.  It just makes sense.  Several vendors have come to the same conclusion and are developing products following this paradigm.  Oracle just made a big announcement about adding in-memory column store to their future 12C product. 

• Data residing and processed in memory is faster than data residing in disk.  This is what VertiPaq does; whether implemented as PowerPivot, an SSAS tabular model or as a SQL Server column store, it works efficiently and elegantly without the complexities and overhead of indexes, partitions, file groups and other techniques typically used to optimize on-disk data stores.

• Even though tabular is fairly new, many useful & valuable features are supported today and work well.

• PowerPivot models upgrade seamlessly to tabular models.  This provides a path for business users and IT professionals to author models in familiar tools (Excel or Visual Studio) and then promote them to a server hosted environment.

• Tabular models are managed and stored by SQL Server Analysis Services!  Although some components of the tabular engine and the designer are new and still have wrinkles to be ironed-out, the core product is based on the solid and time-tested foundation of SSAS.  This means that many of the features not implemented now will be available in future builds.

• Client applications that support SSAS multidimensional will also support tabular.  In fact, any application built to work with SSAS cubes will natively work with PowerPivot and tabular as if it were a cube.  This is because SSAS tabular uses the same data provider that understands both MDX & DAX queries.

  Disclosure: The company I work for is a Microsoft Gold Partner