• 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. 

  • SQL Server and 20 Cores Limit

    January 7, 2015 / No Comments »

    Scenario: You execute a SQL Server 2012 task that uses parallelism, such as index rebuild or a query on a server with more than 20 cores running SQL Server 2012 Enterprise Edition. In the Windows Task Manager, you observe that the task uses only 20 cores. We discovered this scenario during a rebuild of a columnstore index. To confirm this further, you examine the SQL Server log and notice that a similar message is logged when the SQL Server instance starts: “SQL Server detected 8 sockets with 4 cores per socket and 4 logical processors per socket, 32 total logical processors; using 20 logical processors based on SQL Server licensing. This is an informational message; no user action is required.” Explanation: More than likely, you have upgraded to SQL Server 2012 from SQL Server 2008 R2 under Software Assurance. Microsoft created a special SKU of Enterprise Edition to support this...

  • Tabular M2M Relationships on the Horizon

    January 4, 2015 / No Comments »

    One of the biggest strengths of Microsoft self-service BI is the ability to create sophisticated data models on a par with organizational BI models built by professionals. This fact is often overlooked when organizations evaluate self-service tools and the decision is often made based on other factors but not insightful understanding of the data model capabilities. This is unfortunate because most popular tools on the market don't go much further than supporting a single dataset. By contrast, Power Pivot allows you to import easily multiple datasets from virtually anywhere and join the resulting tables as you can do in Microsoft Access. This brings tremendous flexibility and analytical power. Unlike multidimensional cubes, one of the limitations of the Power Pivot and Tabular data models has been the lack of support for declarative many-to-many relationships. The workaround has been using a simple DAX formula to resolve the relationship over a bridge table,...

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