Cool Visualizations Coming To You This Summer

/0 Comments/in /by

I welcome every news about better visualizations in Microsoft BI so I rejoiced to read this blog by Ariel Netz, Group Program Manager in the Power BI Designer team.

“The Power BI Designer team is working on our first update for the summer. That update will be significant, and will signal a new phase in our product development. To date we have mostly invested in the modeling and query capabilities, but starting with the first summer release users will see heavy investments in the Data Exploration & Reporting capabilities in the product. While it might be bit premature to go into detail, it’s not premature to provide a teaser… ”

And the teaser screenshot in the blog shows more visualization gadgets with the ability to change chart series colors. I hope they thrown in conditional formatting as well.

Confused which visualization tool to use? You probably are with so many vendors and tools out there. But as I said many times, your focus should be on data integration and not on the front end. If you have a solid architecture and trusted data, it should be easy to plug in whatever visualization tool is the course de jour. Yesterday it might have been QlikView, today Tableau might enjoy the spotlight, but tomorrow it might very well be the Power BI Designer.

May you leave in interesting times – Chinese proverb.

Where Are My Storage Pool Disks?

/0 Comments/in /by

I’ve been doing quite a bit of SQL Server performance work lately, helping customers embrace the cloud. One great feature of Windows Server 2012 and above is the ability to implement a storage pool combining multiple disks. This greatly simplifies disk striping to distribute I/O and deprecates RAID configurations, as explained in more details in the “Performance Guidance for SQL Server in Windows Azure Virtual Machines” whitepaper from Microsoft.

In this case, the client has an A7 Azure VM which allows attaching up to four 1TB data disks. Another great Azure feature is that you only pay for what you use. What it means to you is that you cut cost because you have all of this storage available but you are charged only for the actual storage and not for the capacity.

In the process of configuring the storage pool, you might find that none or some of the disks show up preventing you from creating a new storage pool although you’ve attached them all right and they are there. How do you fix this horrible issue? Delete the disk partitions (also known as volumes). That’s because you can only create a storage pool of uninitialized disks only.

  1. Right Windows Disk Management.
  2. Right-click on each disk and click Delete Volume.

Now you can create your storage pool. In this case, I’ve created one virtual disk that has three physical disks for a total combined storage of 3 TB.

 

0246.2015-06-06_17-07-15.png-500x0

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

/0 Comments/in /by

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

  1. Process partitions within a Tabular table in parallel. Previously, partitions within a table were processed sequentially.
  2. New DAX functions. The join-related ones, such as NATURALINNERJOIN, NATURALLEFTOUTERJOIN, UNION, will be useful.

SSRS

  1. 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.
  2. Report Builder for SQL Server 2016 supports High DPI

SSIS

  1. Incremental Package Deployment – ability to deploy individual packages to the SSIS catalog.
  2. AlwaysOn support – ability to host the SSIS catalog on a database configured for AlwaysOn for high-availability

MDS

  1. Improved Manageability – reuse entities across models!
  2. Improved performance – overall performance enhancements for larger models, as well as performance improvement for the Excel MDS add-in.
  3. Improved security
  4. Improved troubleshooting

More exciting features to come later on so stay tuned.

 

 

Power BI 2.0 User Provisioning

/0 Comments/in /by

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. there is a tenant admin), tenant admin can disable the user in O365 when the user leaves the company. However, if this is an unmanaged tenant (i.e. no admin yet), the company administrator needs to “Take Over” the tenant, as described here. To make this easier, you can do DirSync which will do this automatically or extend your AD to Azure.

What’s New in SQL Server 2016 BI

/0 Comments/in /by

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

/0 Comments/in /by

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

/0 Comments/in /by

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.

042315_2143_PlottingGoa1

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.

051315_1219_PlottingGoa1

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.

042315_2143_PlottingGoa2

First Look at Datazen

/1 Comment/in /by

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

  1. 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 here). The server supports custom authentication, where users are stored locally or integrate with Active Directory.
  2. Easy authoring experience – Designing dashboards with Datazen is a no-brainer. The tool supports the basic visualization blocks, including filters, charts, gauges, three maps, maps (customization with ESRI shapes is possible) and data grids. I’d like the idea to snap visualizations to a grid. When you expand the visualization height or width, the Datazen Publisher realizes that it has more real estate and adds additional elements to the visualization.
    042015_0153_FirstLookat1
    To see how this work, take a look at this video.
  3. Designed for mobile – Everyone wants mobile rendering nowadays but it’s important to agree on what the desired experience should be. Mobile devices range from tablets, which have larger displays, to phones which are much smaller. Responsive web design seems to be compromise but it’s typically limited to reflowing content based on the display capabilities of the device. Rightly so, Datazen allows the designer to optimize the user experience by offering options for Tablet and Phone. The designer doesn’t have to start from scratch when targeting another device. Instead, the designer can use the visualizations already added to the dashboard and just resize and rearrange, or don’t add them at all. The designer supports pre-defined themes. There are native viewer applications for Windows, Windows Phone, iPhone, and Android.042015_0153_FirstLookat2
  4. Good data support – Out of the box, Datazen supports the most common data sources, including Excel files, SQL Server, Analysis Services, Oracle, ODATA, Web Services. Additional data sources can be plugged in via ODBC drivers. The designer can decide to import the data and refresh it periodically or create a connection to the data source and execute live queries. Another feature that I like is that during the design phase the designer auto-generates data so the dashboard can be designed entirely offline. Data can be plugged in later. Live queries can be parameterized. When SSAS is targeted, the user identity can flow to the data model for row-level security using Kerberos or EffectiveUserName. Live queries are configured by implementing server-side views which are conceptually similar to SSRS shared datasets. I didn’t see an option to import SSAS KPIs as PerformancePoint allows you to do.

Cons

  1. No data exploration – Similar to SSRS, the query is fixed at design time. Therefore, the user cannot change the report layout or add/remove fields. In this age of interactivity, I consider this a major shortcoming. True, the out-of-the-box dashboards are effective and there is more interactivity then SSRS reports, such as the user can slide the finger to select multiple date periods and the page doesn’t repost. Other than that though there is nothing that Datazen can do than SSRS can’t.
  2. Great front, weak back– It’s obvious that the Datazen developers have focused exclusively on the presentation layer. I didn’t see alerts, subscriptions, printing, exporting, execution log, etc.
  3. Limited customization – I didn’t see options for expressions, conditional formatting, localization, etc.
  4. Drillthrough capabilities are limited to initiating a drillthrough on the entire row instead of more granular cell-level drillthrough.
  5. Confusing integration story – Honestly, I’m not sure why Microsoft acquired this tool and this acquisition is a surprise to me. I’d prefer Power BI v 2.0 on premise with shorter release cycles and more features than yet another visualization tool that has a problem fitting in. I really hope it gets integrated with Power View at some point to reduce confusion.

If your primary visualization goal is implementing basic dashboards that target mobile devices with minimum design effort, Datazen might do the trick. For demos, take a look at demos.datazen.com (use datazen for user name and password).

Seeking Oracle Reloaded

/0 Comments/in /by

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 Oracle driver installation (not that it’s hard to do) but the registry settings were off. How do we fix this horrible issue?

  1. Fix the Oracle driver installation.
  2. Remove and reinstall the Attunity drivers. It could be that in this particular case, the customer had both the Attunity 1.2 and 2.0 drivers and they removed the 1.2 version.

Note that you must install both 32-bit and 64-bit Oracle and Attunity drivers. That’s because SSDT (being a 32-bit application) users 32-bit drivers, while the SSIS runtime uses the 64-bit drivers unless you specifically configure the job to run as 32 bit.

Microsoft Acquires Datazen to Bring On-premise Mobile BI

/0 Comments/in /by

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 additional cost. This means millions of people around the world will now be able to visualize and interact with data on their mobile devices, using the native mobile apps available at no charge at the respective app stores.”

The Datazen architecture is server-based and installs on Windows Server IIS.