• About Gartner Magic Quadrant 2015 for BI

    February 25, 2015 / No Comments »

    The 2005 Gartner Magic Quadrant is out and according to Gartner, the distance between Tableau and the other leaders is widening. Here is the full report. It's obvious that Gartner focuses only on the self-service aspect of BI and throws away the entire gamut of tools required to deliver successful BI solutions, including RDBMS, ETL, data models, MDM, etc. But even if we focus on self-service BI, I don't quite agree with Gartner's infatuation with Tableau (see my blog "Top 10 Reasons for Choosing Microsoft Self-service BI"). It's a good visualization tool but based on what I hear, people tend to overestimate its capabilities and get in trouble. Nevertheless, for the most part I agree with the Gartner's assessment related to Microsoft BI cautions, except: 1. "Microsoft had the highest percentage of customer references citing absent or weak functionality (for example, no drill-through capabilities in Power View) as a platform problem."...

  • Fixing Power View to SQL Server 2014 SSAS Multidimensional

    February 24, 2015 / No Comments »

    Scenario: You have Power View integrated with SharePoint. You attempt to create a Power View report that connects to a SQL Server 2014 cube. The connection fails with "Internal Error: An unexpected exception has occurred". The connection used to work or works with SQL Server 2012 SSAS MD. Resolution: On the SSAS 2014 server, install Cumulative Update 2 for SQL Server 2014. This article provides more details about the issue.

  • Top 10 Reasons for Choosing Microsoft Self-service BI

    February 22, 2015 / No Comments »

    Every organization should have a strategy for self-service BI. As a rule of thumb, my advice is that 80% of the BI effort should be directed toward implementing organizational BI solutions (DW, ETL, data model, dashboards, big data, predictive analytics, and so on), while 20% should be left for completing self-service BI analytics. But which tool to choose for self-service BI? With so many vendors on the market, it's easy to get persuaded by marketing propaganda and eye candy. My advice would be to start with what you already have. And, what you have is probably Excel. It's common when I talk to clients to find that they don't realize that the most if not all of their self-service BI needs can be met by the Excel self-service BI capabilities, many of which are unmatched by the competition. Microsoft has built a comprehensive self-service ecosystem marketed under the Power BI...

  • Introducing the Reimagined Power BI Platform

    February 18, 2015 / No Comments »

    Come and join us for an information-packed meeting of the Atlanta Microsoft BI Group on February 23th. You will learn about Power BI vNext and Panorama Necto. Our sponsor will be Panorama. Introducing the Reimagined Power BI Platform by Jen Underwood, Microsoft You have seen glimpses of the new Power BI platform during the Public Preview reveal. Although it may not be apparent, Microsoft has totally reimagined the Power BI platform and user experience for BI professionals, developers and business users. In this new release, Microsoft has added Power BI Designer, developer APIs, custom templates/apps, hybrid direct connectivity to on-premise data sources without data copying, a native mobile BI app and other top secret enhancements that we can dive into by the time of this session. Please join me to further explore all these great changes and enjoy a fun demo-intensive session. Panorama Necto - Panorama Necto is advancing Business...

  • Filtering Tables Dynamically in DAX

    February 15, 2015 / No Comments »

    Sometimes, your DAX calculated measures might need to filter a table dynamically based on a certain condition. For example, you might have a Type 2 table like this one: ClaimID ClaimDate ClaimStatus RowStartDate RowEndDate XXX-1 1/1/2015 Open 1/1/2015 1/15/2015 XXX-1 1/1/2015 Approved 1/16/2015 12/31/9999 When a change is detected on the existing claim, this design expires the previous row and adds a new one. A common business question would be "How many claims do we have as of a given date?" Tabular is more flexible than MD answering this question because you can directly filter the table. In your first attempt, you might write the following calculated measure: ClaimCount:=CALCULATE(DISTINCTCOUNT([ClaimID]), [RowStartDate]<=[FilterLastDate] && [FilterLastDate]<=[RowEndDate])) Where FilterLastDate is another calculated measure that returns the max date if the user has selected a date range, such as an entire month from a Date hiearchy, so that the measure is evaluated as the last date...

  • Finding Duplicates in DAX

    February 15, 2015 / 5 Comments »

    A prerequisite for creating a relationship in Tabular/Power Pivot is to have a primary key column in the table on the One side of the relationship. This column must have unique values. If it doesn't, the relationship won't get created and you'll get an error that the both tables have duplicate keys. If you have a relatively large table, it might be difficult to find the duplicates. IDColumn11Foo2Foo1Foo  However, given the above table design, you can add a simple calculated column to the table to return the count of duplicates for column ID using the following DAX formula =CALCULATE (COUNTROWS(), ALLEXCEPT(Table1, Table1[ID])) This expression uses the COUNTROWS() function to count the number of rows of Table1. Coupled with the CALCULATE function, this expression will be resolved in the context of every row. To ignore the column that you want to count on (ID in this case), you need to exclude...

  • My Wish List for SQL Server.vNext

    February 5, 2015 / No Comments »

    In an attempt to give customers more transparency, Microsoft recently published a Cloud Platform Roadmap. The product groups are also actively seeking feedback for the on-premise products (also known as boxed products). For example, if you have subscribed to the Power BI.vNext public preview, you can use the BI in SQL vNext forum to provide feedback (not sure why there instead of on connect.microsoft.com). So, here is my open high-level SQL Server.vNext wish list: SSAS – Merge Multidimensional and Tabular and take best from both. I like the in-memory engine and flexibility of Tabular but at the same time I miss the MD pro features. To put this in perspective, I'd like to see the following features added to Tabular: Additional relationship types, such as M2M relationships (it looks like this will happen judging by the Power BI features), multi-grain relationships (joining to a table at a higher grain than...

  • Microsoft is Serious about Statistical Analysis and Machine Learning

    January 25, 2015 / No Comments »

    Microsoft announced two company acquisitions related to data analytics. It announced that it will acquire Revolution Analytics. Revolution Analytics is the leading commercial provider of software and services for R, the world's most widely used programming language for statistical computing and predictive analytics. This acquisition could help more companies use the power of R and data science to unlock big data insights with advanced analytics. Previously, Microsoft also announced that it will acquire Equivio. Equivio is a provider of machine learning technologies for eDiscovery and information governance to help customers tackle the legal and compliance challenges inherent in managing large quantities of email and documents.

  • Demystifying Clustered Columnstore Indexes

    January 13, 2015 / No Comments »

    Non-clustered columnstore indexes (NCCI) were introduced in SQL Server 2012 to improve the performance of large aggregate queries (common for data warehousing) with the caveat that there were read-only. Consequently, the ETL process has to drop NCCI, load the data, and recreate the columnstore index. NOTE Building an index (columnstore or regular) should be a highly-parallel operation. Building a columnstore index in particular should max out all licensed cores. Currently, we have an open support case with Microsoft where a columnstore index allows adding a computed column (while it shouldn't). Consequently, SQL Server builds the index using a single thread which may lead to excessive index creation times. SQL Server 2014 introduced clustered columnstore indexes (CCI) which offer two main advantages: CCI is updatable -- Therefore, you don't have to drop and recreate the index anymore. Storage is greatly reduced -- For example, you might have a fact table of...

  • Presenting at Atlanta.MDF

    January 7, 2015 / No Comments »

    I'm presenting at the Atlanta.MDF group on Monday, January 12th. I'll be covering a wide range of tips and techniques for analyzing and improving performance of SQL Server-based data analytics solutions. Hope you can make it. Title: Can Your Data Analytics Solution Scale? Abstract: Does your ETL exceed its processing window? Do your users complain about the SSRS spinny? Can your SQL Server database design deliver the expected performance? Can the system scale to thousands of users? Join this session to learn best practices and tips for isolating bottlenecks and improving the performance of data analytics solutions. I'll dissect the layers of a "classic" solution (relational database, ETL, data model, reports) and share solutions harvested from real-life projects to address common performance-related issues. 

Training

We offer onsite and online Business Intelligence classes! Contact us about in-person training for groups of five or more students.
For more information or to register click here!

Books

Learn Power BI at your own pace with our latest book Applied Microsoft Power BI. Targeting information workers, data analysts, pros, and developers, It is designed as an easy-to-follow guide for learning how to implement BI solutions spanning the entire personal-team-organizational BI spectrum.

Syndication