Posts

Best Practices for Implementing Enterprise BI Solution at SQL Saturday 2013

/0 Comments/in /by

SQL Saturday 2013 in Atlanta was a raging success. Some 555 people attended which made it the most attended SQL Saturday even ever. I’ve uploaded the slides from my presentation “Best Practices for Implementing Enterprise BI Solution” to the Prologika site and Slideshare. The presentation had a great attendance and reviews. Thanks to everyone who attended it and it was great meeting all of you!

“Learn best practices to make your organization a center of BI excellence! I’ll walk you through lessons learned during our implementation of an enterprise end-to-end BI solution. Working experience with the dimensional modeling and the Microsoft BI stack is assumed.”

Leveraging Data to Revolutionize a Mature Business by Bijal Patel at Atlanta BI Group on May 20th

/0 Comments/in /by

Due to Memorial Day, we’ll move our next Atlanta BI Group meeting to May 20th. The meeting will be sponsored by Strategy Analyzer. Our speaker will be Bijal Patel (Director of Data and Integration Services at Cox Media Group) and the topic is “Leveraging Data to Revolutionize a Mature Business”.

“Learn how the integration, management and analysis of data is helping Cox Media Group reinvent its business model. Leveraging SSIS, BizTalk, MDS and several other leading technologies, Cox Media Group is finding new ways to reach its customers and deliver innovative media products and services to its established customer base.”

This looks like a very interesting “from the trenches” presentation given the experience of the speaker and the business of its organization. I hope you can join us. Please register and RSVP on the Atlanta BI Group website.

Presenting at SQL Saturday in Atlanta

/0 Comments/in /by

I’ll present at SQL Saturday in Atlanta on May 18th. Initially, I was planning to talk about dashboard options with the Microsoft BI stack but the organizers had a call for more advanced content. To accommodate this request, I’ll present Best Practices for Implementing an Enterprise BI Solution where I’ll share proven practices harvested from real-life projects.

“Learn best practices to make your organization a center of BI excellence! I’ll walk you through lessons learned during our implementation of an enterprise end-to-end BI solution, which is discussed in the Records Management Firm Saves $1 Million, Gains Faster Data Access with Microsoft BI case study published by Microsoft. Working experience with the dimensional modeling and the Microsoft BI stack is assumed.”

I’m looking forward to seeing you on May 18th.

Default Parameters in Power View

/0 Comments/in /by

Scenario: You need to configure a Power View report to show data for a dynamic date, such as the current date or the last date with data. However, as it stands Power View doesn’t support expressions.

Workaround: Add a Boolean calculated column to the Date table that returns TRUE for the date of interest. For example, if you want the report to show data for today’s date, the expression might be:

=if([Date]=TODAY(), True, False)

where [Date] is the column with date data type. Then, use this column as a filter in Power View.

050413_1722_DefaultPara1

Unfortunately, this workaround has a significant limitation. If the report needs another filter on the Date table, such as to allow the user to overwrite the default filter on the current date, this approach won’t work because filters on multiple attributes are interpreted as an AND condition (Date is 1/1/2013 AND TodayDate=True). Inability to specify OR filter condition between attributes is another Power View limitation. So, users needs to be trained to use either the TodayDate or Date filter but not both.

Looking ahead, it will be nice if Power View supports VB or DAX expressions as regular SSRS reports do.

Many thanks to Darren Gosbell for suggesting the workaround.

Best Practice and Creative Data Visualization by Jen Underwood for Atlanta BI

/0 Comments/in /by

It looks like Atlanta BI Group will have a record attendance tonight with 74 people registered! Jen Underwood will present Best Practice and Creative Data Visualization.

This fun, informative, and inspirational session covers both best practices and creative options for data visualization. We will showcase data visualization techniques in Excel, Power View, Reporting Services, Visio, and a variety of other Open Source projects and Third-Party data visualization offerings. The possibilities are endless with the right mix of tools, tips, and tricks.

About Tableau 8

/5 Comments/in /by

Jen Underwood highly recommended I attend the Tableau 8 tour on Tuesday to witness firsthand its new features. Naturally, I couldn’t resist of comparing everything I saw with Microsoft BI. I took some time after the pretention to take 8.0 for a spin and reconfirm my understanding. Here are the top five things I liked about Tableau.

  1. Simplicity – A few months ago, I blogged about my top 5 Microsoft BI wish list. My number 1 wish was a continued focus on integration and simplification. Tableau 8.0 nailed it down as far as simplicity, at least on the visualization side of things. One desktop tool and its server-based counterpart. A tool whose sole purpose is BI. Not something that was bolted on another tool as a BI add-on. No layers to integrate with and configure on the server side, and error logs to sieve through.
  2. Visualization – Visualizations are yet simple and powerful. The presenter was quick to point out the areas where the tool excels in comparison with Power View. Tree maps and bubble maps were all over the place, as well as the ability to customize them, e.g. change the color of a pie slice. I liked the ability to dynamically group items (similar to custom MDX sets in Excel). For example, the presenter lassoed a bunch of cities in North America and created a North America dynamic group that was subsequently used to analyze sales. Another interesting in-the-box feature is ability to forecast data. In the Microsoft world, this would require some flavor of data mining (not a native feature in both Excel and Power View).
  3. Mobility – This is one area where Tableau has at least a year lead over Microsoft BI (see my number 2 wish item in the above blog). Once the dashboard is published to the server, it can be viewed and edited on mobile devices (iPad was demonstrated). By contrast, due to its Silverlight nature, Power View is currently supported only in Internet Explorer. However, as we’ve heard at conferences, Microsoft is hard at work to change this.
  4. JavaScript API – Developers creating web applications can integrate and embed interactive Tableau content into their applications via the new JavaScript API.
  5. Data reach – Apparently, Tableau customers are asking for specialized connectors to cloud data and emerging data sources. Version 8.0 introduces connectors for Salesforce.com, Google Analytics, Hadoop, SAP Hana, etc.

Being a self-service BI tool, in my opinion the backend is where Tableau trails behind Microsoft BI, including:

  1. No continuum for self-service to organizational BI – While you can publish a workbook to the server, you can’t upgrade the workbook to an organizational BI model. Nor can you connect to a published workbook as a data source, e.g. by using Excel as a front end.
  2. In-memory engine – Based on my experiments, xVelocity (the in-memory engine of PowerPivot and Tabular) excels the Tableau in-memory technology both in compression and speed.
  3. Programming – Tableau supports custom calculations but the potential is not even close to what you can do with DAX. For example, I couldn’t find a way to use many-to-many relationship (not natively supported in Power Pivot but can be handled with DAX formulas).
  4. Scalability – I haven’t tested the server edition yet but I would expect Analysis Services to scale much better than Tableau due to the desktop origin of the latter.
  5. Security – Tableau supports action-level security, e.g. allowing the user to edit workbooks, and basic user filters that allow user access to specific members, such as Bob can see only USA. There is no dynamic data security.
  6. Usability – Besides the presentation layer where Tableau excels, I’ve noticed areas that would be surprisingly difficult for a self-service BI tool. For example, there isn’t option to visualize tables and relationships in a diagram view. For some obscure reason (I guess so it knows what to aggregate and not), Tableau insists on separating entities as dimensions and measures. Tableau stores data and connection definition as external files, and maintains connections per entity. For example, I couldn’t find a way to import more tables using the same connection definition.

In summary, choosing between Tableau and PowerPivot for self-service BI would require careful examination of requirements and comparing pros and cons of the two. On the corporate BI side of things, you should definitely consider Tableau as a front end to Analysis Services if you need an alternative to Microsoft Excel and/or Power View.

Data Warehouse Physical Design Best Practices Session by Carlos Rodrigues at Atlanta BI Tonight

/0 Comments/in /by

It looks like Carlos will deliver another slam tonight with a record number of some 63 people registered and counting… Join our Atlanta BI meeting at 6:30 to see his Data Warehouse Physical Design Best Practices presentation.

Atlanta BI Group Meeting on Monday, December 3rd

/0 Comments/in /by

I’ll be presenting What’s New in Excel 2013 and SharePoint 2013 BI at our Atlanta BI Group on Monday, December 3rd.

Microsoft has recently released the 2013 version of Excel and SharePoint. Both technologies include major enhancements for self-service and organizational BI. Join us to review these new features. Learn how business users can quickly analyze and understand data in Power Pivot which is now natively supported by Excel. See how Power View enables rich data visualization and having fun with data both on the desktop and server. Understand the new Excel and SharePoint features for organizational BI that opens new opportunities for analyzing OLAP and Tabular models.

SQL PASS 2012 Day 1 Announcements

/0 Comments/in /by

I hope you watched the SQL PASS 2012 Day 1 Keynote live. There were important announcements and I was sure happy to see BI being heavily represented. For me, the most important ones were:

  1. The availability of SQL Server 2012 Service Pack 1

For some reason, this announcement went without being applauded from the audience although in my opinion it was the most important news from the tangible deliverables. First, I know that many companies follow the conventional wisdom and wait for the first service pack before deploying a new product. Now the wait is over and I expect mass adoption of SQL Server 2012. At Prologika, we’ve been using SQL Server 2012 successfully since it was in beta and I wholeheartedly recommend it. Second, SP1 is a prerequisite for configuring BI in SharePoint 2013, as I explained previously. Indeed, I downloaded and run the setup and I was able to continue the SharePoint 2013 PowerPivot configuration. BTW, the build number of SP1 is 11.0.2100.60.

Note If you’re configuring PowerPivot for SharePoint 2013, you must also install a PowerPivot for SharePoint 2013 add-in (there is a new installer package called spPowerpivot.msi) in order to get the upgraded version of the PowerPivot Configuration Tool for SharePoint 2013. If you open the RTM version of the PowerPivot Configuration Tool for SharePoint, it will promptly complain that it doesn’t know a thing about SharePoint 2013 and redirect you to this page. Unfortunately, at this time, the link on this page points to the Community Technology Preview of the SQL Server 2012 SP1 Feature Pack and the whereabouts of the official SP1 release of the feature pack are not known (the Feature Pack was published with an incomplete list of files). I downloaded and ran the CTP version of and then ran the PowerPivot Configuration Tool for SharePoint 2013. It appears that the CTP version did a respectable job and all it was capable of successfully configuring PowerPivot for SharePoint. However, please wait for the official release of the SQL Server 2012 Feature Pack to avoid issues.

  1. Power View for Multidimensional – OK, the cat is out of the bag on this one and Amir showed a demo. As a participant of the CTP program Power View for Multidimensional, I’m very happy about it. That’s all I can say at this point while waiting for the public technology preview. Unfortunately, Power View for Multidimensional didn’t make it to SP1 and it’s not known at this point when and how it will ship. But if you have multidimensional cubes (and who doesn’t) the wait will be worthwhile I promise.
  2. Updatable Columnstore Indexes in SQL SERVER.NEXT – This a good news for users of columnstore indexes that will avoid dropping and recreating the indexes. This will be especially useful for columnstore indexes built on top of large fact tables, such as in the scenario I described here.
  3. Hekaton – Plans to ship a long-due in-memory OLTP technology in SQL Server.NEXT.
  4. Polybase – Another new technology slated for the next release of SQL Server 2012 Parallel Data Warehouse (expected in first half of 2013) that will allow you to run T-SQL queries joining relational data residing in PDW and Hadoop data. I guess this is the materialization of the David DeWitt’s Enterprise Data Manager idea that he talked about in his 2011 PASS presentation. I’m looking forward to his sequel which I suppose will go in details on this topic. Did we run out of cool names from the animal kingdom to succeed Hadoop, Mahoot, Pig, etc? I guess will find out in David’s talk.

Here is the list of the forthcoming live sessions.

 

UPDATE 11/8/2012

Here is a direct link to the release build of the PowerPivot Configuration Tool for SQL Server 2012 SP1.

Making Big Data Real from TAG BI

/0 Comments/in /by

TAG BI is organizing a “Making Big Data Real” event on August 23th and I was honored to be one of the panelists. Please join us if you can and ask me the techniques we use to implement multi-terabyte data warehouses on symmetric multiprocessing (SMP) systems that deliver reports within milliseconds and maximize your return on investment.

“The Technology Association of Georgia’s Business Intelligence/Enterprise Performance Management society explores one of the hottest topics in the technology landscape, Big Data. You have likely read the articles and the books and now you need to know more. On August 23, we will bring together a panel of experts for a most important session: “Making Big Data Real”. Please join us as we engage corporate technology leaders and consultants that are on the cutting edge of this new technology landscape. We will tell you what Big Data is all about and how you can make it real and actionable at your workplace and in your career. This is surely a professional event you do not want to miss.”