-
DAX Editor New Features
March 15, 2015 / No Comments »
UPDATE 4/24/2019 The new JSON-based Tabular schema doesn't support extensions so Tabular Editor and BI Developer Extensions (BIDS) won't work. As it stands, Tabular (versions 2012 and 2014) doesn't have the equivalent of a cube script. Instead, the developer has to use the Measures grid to maintain DAX calculated measures. This is OK with a few measures but as the number of measures grows, the development experience suffers because it's getting harder to locate these measures. Besides, every time you make a change, you need to wait for the Tabular environment to refresh which gets annoying quickly. However, you can use the DAX Editor community sample to simulate a Tabular script. DAX Editor allows you to extract all measures from a Tabular model and maintain them outside the model in a DAX file. DAX Editor was initially developed by Microsoft (kudos to Nickolai Medveditskov). Marco Russo and I teamed up...
-
Power BI vNext SSAS Connector and Security Reloaded
March 7, 2015 / No Comments »
To follow up on my previous post on the same subject, a customer was eager to jump on the Power BI->On Prem SSAS bandwagon and try the simplified security model. But "omne initium difficile est" (every beginning is difficult). Their SSAS server was installed on a domain acme.com while their e-mail addresses were using a different scheme, e.g. user@contoso.com, although both server and accounts were under the same acme domain. As we've quickly found out, Active Directory had an issue with this setup which manifested with the following error in the SQL Server Profiler connected to SSAS. "The following system error occurred: The name provided is not a properly formed account name." If you see this error, follow these steps to confirm the issue: Remote in to your SSAS server. Open Command Prompt and enter: Whoami/upn If you see that your login domain name is different that the e-mail you...
-
Projecting a Scalar Value with DAX EVALUATE Queries
March 4, 2015 / No Comments »
When you work on more complicated DAX measures, you should get out of the Power Pivot or BISM design environment and use the excellent DAXStudio (or SSMS) with the EVALUATE query syntax. But then eventually the measure would return a scalar value while EVALUATE requires a table. You can use the DAX ROW function to create a single-row, single-column table, and then show the result using EVALUATE.
-
Power BI vNext SSAS Connector and Security
March 3, 2015 / No Comments »
As you've probably heard, Power BI vNext will allow you to keep your data on premises in SSAS MD and Tabular data models while your reports in the cloud can connect to these data models on premises. Currently, the Analysis Services connector support only Tabular models while plans for MD have been announced as well. Currently, SSAS understand Windows security only and you might wonder what needs to be put in place for security to work. Interestingly, if your users have used their work e-mail addresses to sign up for Power BI and your company is using Active Directory, you don't need do synchronize your AD with Azure using DirSync. That's because Power BI will pass the user identity on the connection string using the EffectiveUserName property. Consequently, connectivity and data security will work as usual. On the other hand, if users used an .onmicrosoft.com e-mail address, DirSync is required....
-
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...
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.
