Creating a Business Intelligence Solution with D&B Business Data & Excel 2013 BI Tools

26 03 2014

This series of video sessions will demonstrate how to create a business intelligence solution with sets of business data from multiple sources.  In the first session, I import business records from 5 different D&B data feeds using the Windows Azure Marketplace with desktop BI tools in Excel 2013 ProPlus.  In future sessions, I will combine public data sets, use features of Power BI and migrate to an enterprise scale BI solution with SQL Server, SSAS tabular and SharePoint.

D&B data fees in the Windows Azure Marketplace can be used to get detailed, current demographic and financial information about specific businesses or companies in different regions and categories.  These business records can be used for things like market analysis, sales, verification and research.

Session 1 – Getting started (48:00)
Analyzing Dunn & Bradstreet business records data from the Windows Azure Marketplace
using Excel 2013 Power Query, Power Pivot and Power View to create a basic dashboard

To watch this video with more playback controls, you can also view it on ScreenCast here.

Session 2 – Enhancing the Solution (32:00)
Combining Dunn & Bradstreet Customer Records with Public & Enterprise Data
Geographic visualization with Power Map, publishing to Power BI and using Q&A to search a model with natural language

FirstFrame

 

Digg This




Book Review of Microsoft Tabular Modeling Cookbook

18 02 2014

by Paul te Braak, from Packt Publishing
on Amazon

Good technology books usually come in one of two forms.  Some of the books on my shelves go deep technically, contain useful tidbits of code but read like the phonebook.  Just a few are interesting and insightful.  This book is a rare gem that does both.  Paul te Braak is well-known in the Business Intelligence community for his expertise and contributions and he delivers a unique guide that starts with the basics and proceeds to cover all of the essentials with depth and practical examples to solve some challenging business problems.

You might expect a book that introduces fundamental concepts to gloss-over advanced topics and avoid complex applications.  This book covers the essentials of data modeling and analysis with Excel and Power Pivot in language that is plain and easy to understand but it doesn’t stop with the basics.  It covers practical and useful applications without insulting the reader’s intelligence.  As an experienced data modeler, I found several useful techniques and new methods to use the tools and language.  Paul’s coverage of practical techniques spans the spectrum of business applications and product features.  This is a rare book that is not only a good tutorial with many hands-on examples that can be repeated by the reader but it’s also a great reference of useful techniques and code samples.

Highlights include:

  • The integration of Excel features such as ranges, tables, pivot tables and pivot charts with the additional features of Power Pivot and Power View that extend and enhance these capabilities
  • Examples and instructions are directed at Excel 2010 users and the author compares some the different features in Excel 2010 and Excel 2013.
  • Fundamentals of the DAX calculation language
  • Importing data as text, different date formats and implied data type columns
  • Beyond the basics, a schema.ini file is used to define column data types
  • Importing data from a database, working with database tables, views and queries, managing connections and challenges encountered running the table import wizard multiple times
  • Data feeds using OData and using a Reporting Services report to provide a data feed
  • Decisions a designer makes to enable the user’s experience when browsing a model.  This includes sorting values, navigating hierarchies that enable drill-down interaction. 
  • DAX “X” functions (SUMX, MINX, etc.) to perform row-level aggregation
  • Working with parent-child hierarchies using specialized DAX path functions. 
  • Advanced browsing features, adjusting pivot table options to optimize the user experience
  • Building and using KPIs and using alternate table relationships
  • Time calculations and date functions.  This chapter covers running totals and totals to date
  • Date part aggregate functions (MTD, YTD, etc.),
  • Essential data math and comparisons
  • LastYear and PriorPeriod functions, TotalYTD
  • Manufacturing calendar, working with “445” dates
  • Creating a dynamic relative time measure, using a shell dimension table
  • Using DatesBetween to show the average value for the past 10 days
  • Apply advanced modeling technique to bin, sort and rank values for reporting
  • Expand concepts introduced in chapter 3, using the DAX “X” functions to perform row iteration in advanced financial applications
  • Defining and working with many-to-many relationships.  This is often no trivial task to completely understand many-to-many relationship requirements and to apply a working solution that provides the intended results
  • Addressing inventory and stock-keeping challenges
  • Conditional aggregation at different levels
  • Budgeting and forecasting vs actuals
  • Programming Excel to enhance the users experience
  • Excel VBA event programming to respond to slicers
  • Using cube functions
  • Interacting with charts and slicers
  • Building solutions for the enterprise
  • Using the SSDS Tabular designer
  • Migrating Power Pivot models to Tabular server solutions
  • managing connections, implementing impersonation, managing security
  • Using roles and perspectives
  • Generating and using XMLA script
  • Defining and implementing role-based, dynamic row filtering
  • Performing currency conversion
  • Managing and optimizing  a Tabular solution
  • Deployment scenarios
  • Using SSDT to deploy and process models
  • Using the SSAS Deployment Wizard
  • Generating and using deployment scripts
  • Creating and managing partitions
  • Scheduling and executing processing tasks
  • Utilizing DirectQuery for real-time data results
  • Using Profiler to troubleshoot and optimizing a model
  • Querying a model using DAX
  • comparison of similar and different concepts in multidimensional and Tabular semantic models
  • Query with MDX
  • Query with DAX
  • DAX tools and debugging techniques
  • Using DAX query techniques to simulate SQL query operations
  • Column aliases, joins, filters, deriving tables
  • Samples and top ranked results
  • Using Power View to present results and visualize data
    • Essential design features
    • creating a table report
    • using a matrix to pivot results
    • time and data filters
    • advanced filters
    • creating charts
    • bar charts
    • stacked charts
    • cluster chart
    • using tiles to navigate sectioned results
    • using images
    • managing tables with default field sets
    • table behavior and cards
    • data categories and visual behaviors
Digg This






SSAS Tabular Models: The Good, the Bad, the Ugly & the Beautiful

24 06 2013

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.

Digg This






Power View for Multidimensional Cubes Released

1 06 2013

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.





Data Visualization Choices SQL Saturday Presentation

16 02 2013

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.





Power View Now Works with Cubes!

30 11 2012

This is a very significant day in the progression of the Microsoft Business Intelligence platform.  Earlier this year, Power View was released with SQL Server 2012; a remarkable step forward in data visualization. It wasn’t all bad news that Power View only worked with the new tabular semantic models and PowerPivot worksheets published in SharePoint but it did limit our options.  We’ve quietly lived with the fact that this great new reporting tool couldn’t be used with Microsoft’s flagship analytical data (OLAP) engine without building new semantic models.  Well, now it does, and that’s very good news.

Read the rest of this entry »





An Unusual Book–on DAX Formulas

29 11 2012

image

One of the characteristics of a really good, classic movie is that it has a lot of memorable dialog.  I could go on for hours quoting one-liners from The Blues Brothers or Princess Bride.  Likewise, I think a good book leaves the reader with gems to ponder and to stimulate ideas.  Such has been my recent experience reading Rob Collie’s “DAX Formulas for PowerPivot, The Excel Pro’s Guide to Mastering DAX”.  Read the rest of this entry »











Follow

Get every new post delivered to your Inbox.

Join 216 other followers