• What’s New for BI in SQL Server 2016 CTP2?

    May 28, 2015 / No Comments »

    The first public preview of SQL Server 2016 (CTP2) got announced yesterday. The natural question for BI pros is what's new for BI. The "What's New in …" topics in the SQL Server 2016 Books Online provides the detailed description of the BI features that made the CTP2 cut. To summarize the major features: SSAS Process partitions within a Tabular table in parallel. Previously, partitions within a table were processed sequentially. New DAX functions. The join-related ones, such as NATURALINNERJOIN, NATURALLEFTOUTERJOIN, UNION, will be useful. SSRS Subscription enhancements, such as Enable/disable subscriptions (New user interface options to quickly disable and enable subscriptions), change subscription owner, shared credential for file share subscriptions. Report Builder for SQL Server 2016 supports High DPI SSIS Incremental Package Deployment – ability to deploy individual packages to the SSIS catalog. AlwaysOn support – ability to host the SSIS catalog on a database configured for AlwaysOn for...

  • Power BI 2.0 User Provisioning

    May 15, 2015 / No Comments »

    As a continuation to my "Power BI vNext (let's now call it Power BI 2.0 to align terminology with Microsoft) SSAS Connector and Security" blog, you might wonder how Power BI provisions users. For example, if a user signs with his business e-mail and a coworker shares BI artifacts with him, what happens when the user leaves the company? Can he still gain access? As it turns out, when the user signs to Power BI, Microsoft adds the user transparently to the Azure AD (AAD). Syncing AD with AAD is not a requirement. This is why you don't need to extend your AD to Azure or synchronize it when you want Power BI reports to connect to on-prem Tabular models. If you do not sync your AD with Azure AD and remove user from AD, they continue to exist in Azure AD. If the tenant is a managed tenant (i.e....

  • What’s New in SQL Server 2016 BI

    May 11, 2015 / No Comments »

    Lots of announcements and roadmap updates coming from Microsoft Ignite which replaced TechEd and other conferences with one event. My favorites are the What's New sessions with the BI-specific ones below. Microsoft SQL Server BI Drill Down Microsoft BI Overview What's New in Master Data Services (MDS) and Integration Services (SSIS) in SQL Server What's Next for Business Analytics in Microsoft Excel Microsoft Azure SQL Data Warehouse Overview What's Next for Visualizations in Microsoft Office

  • SQLSaturday Atlanta

    May 11, 2015 / No Comments »

    SQL Saturday Atlanta is around the corner. I'm presenting "Increase your BI productivity with community tools" scheduled in the first time slot at 8:15 AM. "Microsoft has always provided a comprehensive toolset for BI developers but functionality gaps remain. In attempt to fill in some of these gaps, MVPs and community have implemented outstanding tools. Join this session to learn how to increase your developer productivity and improve your BI solutions by using some of the most prominent community tools, including BIDS Helper, DAX Studio, DAX Editor, Query Capture, and OLAP Extensions. This session assumes developer experience in designing and implementing BI solutions with SSAS, SSRS, and SSIS." If this sounds interesting but you can't attend, don't worry as a repeat is in order for the Atlanta BI Group on Monday, May 18th.

  • Plotting Goal in Power BI

    April 23, 2015 / No Comments »

    Plotting Goal in Power BI

    A customer wanted a bar chart where each bar shows the invoiced and realized sales and a marker to show a goal, as sketched below (notice the Budget marker). The chart had to render the data from SSAS. In SSRS, this of course can be done using a bullet gauge, which I hope one day will make its way to Excel and Power View. Because Excel PivotChart doesn't support XY Scatter charts when connected to SSAS, the compromise is to use a column chart, such as the one below, where the goal was plotted as a line chart with the line hidden and only markers visible. The closest Power View compromise would be the chart below. This requires the latest Power View build which is only available in Power BI 2.0. In Power BI, Power View supports a Combo Chart Stacked variation.

  • First Look at Datazen

    April 20, 2015 / 1 Comment »

    First Look at Datazen

    UPDATE: 1/1/2016   As Microsoft announced in the public BI roadmap (http://blogs.technet.com/b/dataplatforminsider/archive/2015/10/29/microsoft-business-intelligence-our-reporting-roadmap.aspx), Datazen will be fully integrated with SSRS 2016 for delivering mobile reports, alongside paginated (traditional SSRS reports), and Power BI Desktop reports. To follow up on my blog announcing Datazen, I want to share some additional notes now that I had to chance to install it and take it out for a spin. Pros Easy installation experience – Installation can't be easier either although I'm not crazy about the Datazen decision to make the Publisher available in the Windows Store. I just don't like Windows Store applications, not the mention that they don't work by default with Windows Server (you need to enable the Desktop Experience feature and use a non-admin account). To share your dashboards, you can publish them to a server component (Datazen Enterprise Server). The server installs as an ASP.NET application running under IIS (no surprises...

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

    Implementing Conditional Formatting in Tabular

    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 »

    DAX Editor Adds Support for Tabular Default Members

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

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