-
Seeking Oracle Reloaded
April 17, 2015 / No Comments »
Scenario: You create an SSIS 2012 project in SSDT that uses the Attunity Oracle connector. By default, Attunity creates a private connector. You verified that the package connects and executes successfully. Then, you promote connector to a project-level connector so that you can manage the credentials in the SQL Server job configuration across all packages. When you attempt to deploy to the SSIS catalog, you get the following error: Failed to deploy project. For more information, query the operation_messages view for the operation identifier <X> When you query the operation_messages view for that message_id, you get the enlightening reason: Failed to deploy the project. Fix the problems and try again later.:Unable to create the type with the name 'MSORA'. Solution: I had my own fair share of Oracle OLE DB driver installation issues, some of which I documented here. I don't know how this customer managed to mess up the...
-
Microsoft Acquires Datazen to Bring On-premise Mobile BI
April 14, 2015 / No Comments »
Microsoft announced today that it acquired Datazen Software. This is a great news because an on-premise mobile dashboard solution has been a big pain point with Microsoft BI. SharePoint Excel Services and Reporting Services are mobile-friendly but Power View is still Silverlight-based. And, customers have been skeptical about the time it will take for Microsoft to deliver a true mobile-ready on premise solution that complements its cloud-based Power BI. I haven't personally used Datazen but its looks very promising especially considering that "In particular, SQL Server customers love Datazen, because it is optimized for SQL Server Analysis Services and the overall Microsoft platform, enabling rich, interactive data visualization and KPIs on all major mobile platforms: Windows, iOS and Android". And, the price is right: "As of today, SQL Server Enterprise Edition customers with version 2008 or later and Software Assurance are entitled to download the DataZen Server software at no...
-
Implementing Conditional Formatting in Tabular
April 7, 2015 / No Comments »
As I mentioned here and here, Marco and I teamed up to invigorate the DAX Editor with some new features. Today we officially released the latest updates and published in to the Visual Studio Gallery. The easiest way to install is to do it directly from Visual Studio/SSDT: In Visual Studio 2010, 2012, or 2013, go to Tools->Extensions and Updates. Click the Online tab and search for "dax" or "dax editor". Note that if you use Visual Studio 2013, you need to make a configuration change in devenv.exe.config that I explained here. In the second post, I've mentioned that I've added the option to inject some custom MDX script in the Tabular script, such as to set up default members. You can also use the custom script to implement conditional formatting – a feature that Tabular doesn't support natively. Suppose that you want to change the front color of the...
-
DAX Editor Adds Support for Tabular Default Members
March 19, 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. In my previous blog, I announced a few new features for DAX Editor for Tabular. Today, I checked in another change that adds support for default members. Currently, Tabular doesn't have UI for defining default members. However, you can define default members in the MdxScript section of the BIM file using MDX syntax just like you can do so in Multidimensional. The only issue is that if you make a change to any Tabular calculated measure in the designer, SSDT will regenerate the script and your manual changes will be lost. That is unless you use DAX Editor as its support for default members keeps them in the script. As a disclaimer, I took the backdoor approach for defining the default member syntax. The right approach would be to build...
-
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.
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.
