This post is really a collection of field notes and some lessons learned from recent project experience. I’ve done plenty of SSAS Tabular projects over the past few years – usually starting with a Visual Studio project rather than Power Pivot. I’ve also done a bit of Power Pivot work for clients. These projects were either delivered to an analyst using Excel on their desktop or some business users through SharePoint. But, authoring in Excel Power Pivot and deploying to a server-hosted Tabular model has been mainly theoretical up to this point so I thought I’d share my experience. Read the rest of this entry »
Comments : 2 Comments »
Categories : BI Industry, BI Projects, Excel BI, PowerPivot, Self-service BI, SQL Syndication, SQLServerPedia, SSAS Administration, Tabular Models
I’m pleased to share the first two articles in a series for SQL Server Pro Magazine. Here’s a short excerpt from each with a link to the full article:
Getting Started with Datazen, Microsoft’s New Mobile Dashboard Platform (Part 1)
Microsoft Datazen is a new tool from Microsoft built on a mature and time-tested foundation of enterprise services and visualization components from Component Art. Interactive, touch-friendly, analytic dashboards created by data professionals are available to users on desktops and practically all modern mobile device platforms.
Getting Started with Datazen
The story you are about to read is going to sound too good to be true but it is true and it’s awesome.
About two years ago, I was helping a big consulting client evaluate mobile BI dashboard options for their Microsoft BI solution. A friend on the BI platform team at Microsoft asked for my thoughts about a new mobile dashboard tool called Datazen. My initial impression was that the product needed to mature to meet my client’s needs but the interactivity and visual experience was stunning. Half joking (in a Freudian sort of way), I told him that Microsoft should buy the company and finish integrating it with their BI platform. Imagine my delight when I learned last month that it actually happened.
Comments : Leave a Comment »
Categories : Articles, BI Industry, DataZen, Microsoft BI Platform, SolidQ, SQL Server, SQL Syndication, SQLServerPedia
Last month Microsoft announced that they had acquired the DataZen mobile BI platform and were adding it to their enterprise BI product suite. This is very exciting news that rounds out the Microsoft BI capabilities of their entire product portfolio. What’s more, is that they plan to make if free for SQL Server Enterprise customers who have Software Assurance agreements.
Watch for a series of articles about getting started with DataZen in SQL Server Pro Magazine online.
I presented a session at the PASS Global Summit in 2013 showcasing DataZen called “New York, London, Paris Munich; Everybody’s Talking About Mobile BI”. A product team leader in the Microsoft BI Visualization team had introduced me to DataZen and recommended I take a look. This led me to recommend that we evaluate the product for one of our enterprise BI clients and to talk about my experience in the conference session. My response to the Microsoft team was to recommend that they acquire DataZen. Whether I actually had anything to do with this product acquisition or not, it does give me a sense of satisfaction to know that it is now part of the Microsoft family.
So, what, exactly is DataZen and what are its capabilities and challenges? Read the rest of this entry »
Comments : 2 Comments »
Categories : Articles, BI Industry, BI Projects, Business Analytics, DataZen, Microsoft BI Platform, Self-service BI, SolidQ, SQL Server, SQL Server Pro Magazine, SQL Syndication, SQLServerPedia
It was a great honor to be asked to join my associates from SolidQ at the Microsoft Virtual Academy Studios in Redmond and talk about how to upgrade to SQL Server 2012 and 2014. These recordings, which are also on my YouTube Channel, include the material I covered in these sessions. The entire series of studio presentations are hosted on Channel 9 and here at the MVA with accompanying assessment surveys and resources.
In these studio sessions, I am joined by my fellow authors of the 429 page SQL Server 2014 Upgrade Technical Guide; Richard Waymire, Ron Talmage and Jim Miller from SolidQ. Jim and I were responsible for the Business Intelligence content. In our sessions Jim covered SSIS and SSAS Multidimensional, and I covered SSAS Tabular, BI tools and SSRS. In this edited portion of the SSAS session, Jim begins with a brief summary of multidimensional upgrade options and I continue to discuss opportunities to implement SSAS Tabular solutions. BI topics apply equally to SQL Server 2012 and 2014 upgrades. Read the rest of this entry »
Comments : Leave a Comment »
Categories : SolidQ, SQL Server, SQL Syndication, SQLServerPedia, SSAS Administration, SSAS Design, SSRS Administration, SSRS Design, Tabular Models
I’ve created a series of five tutorial videos for a set of workshops I’ll be doing at several events this year. The workshop is available from an hour-long quick demo to a full-day, hands-on workshop with a hosted lab environment in the cloud. The series teaches how to create a complete business intelligence solution using the components of Power BI in Excel 2013. These video tutorials, although complete, are a subset of the entire workshop. Each tutorial is about 10 minutes long.
Power BI Complete Demonstration
Part 1 – Power Query
Part 2 – Power Pivot
Part 3 – Power View
Part 4 – Data Correlation
Part 5 – Publish to Office 365
Comments : 4 Comments »
Categories : Data Visualization, Microsoft BI Platform, Power BI, Power View, PowerPivot, Self-service BI, SQL Syndication, SQLServerPedia
Some exciting announcements were made today about the new Power BI cloud-based business analytics service. Today Power BI is an add-on service for Office 365 that requires an enterprise-level license – about $50 per user per month. Data transformation queries, data models and dashboard visualizations are all created using add-in tools for Excel 2013. All of the content is managed in SharePoint Online. The new Power BI designer and hosted dashboard elements won’t require any Excel add-ins and use a streamlined cloud service with a list of mobile options. The best part is that the price also went way down – about $10/month for designers and it’s free for anyone who just runs reports and dashboards. Yea… free!
James Phillips, Microsoft General Manager of Data Experiences posted “Today is an incredibly exciting day as we unveil the new Power BI – a service we believe will fundamentally transform the “business of business intelligence.” Power BI can help every company adopt a data culture with easy-to-use, accessible tools and innovative technology that lowers the barrier to entry, for all.” Read his entire post here.
The new Power BI Preview is available to try out for free to anyone with a US email address. It’s actually been available to limited audiences for a couple of months but this is the first official announcement. Just go to the Power BI site and get started using the preview.
This new direction raises many questions about the investments many have already made into Microsoft BI tools and the options we’ll have to continue to use those tools or migrate to the new platform. We are seeing a lot of open dialog from the Microsoft product teams and leadership to address these questions in advisory councils and forums like the MVP Program.
I’ve been playing with the preview and it’s quite impressive for being so new. Several features aren’t quite as capable as the full Excel-based suite in Office 365 today but the tiled dashboards & new visualizations are very nice. The designer is lean and simple. It contains a light version of Power Query, an ultra-light modeling tool in lieu of Power Pivot and a very robust visualization experience that in many ways is ahead of Power View. In all it’s a nice tool that is being updated rapidly. I’ve provided quite a bit of feedback using the “smile” and “frown” buttons in the designer and the product teams have responded very quickly with bug fixes and answers. They’re obviously committed to getting this right. The preview is probably not something you’ll want to use for real business BI projects right now but certainly a tool to take very seriously in the months ahead.
Comments : Leave a Comment »
Categories : Business Analytics, Microsoft BI Platform, Power BI, SQL Syndication, SQLServerPedia
This post actually applies to both multidimensional (cube) projects and Tabular projects which manage data source connectivity in a similar fashion. The multidimensional design experience is different but the mechanics of the SSAS engine are essentially the same.
When loading data from a SQL Server data source into a Tabular model project, chances are very good that you’ll see an error on the first attempt. I’ve been creating SSAS projects for over over 15 years and, even though this is a relatively simple process, I still usually work through the initial configuration in trial-and-error mode. So, let’s call this a normal part of the process.
A Word of Advice
In Tabular model design, a common practice is to select all the tables and columns, rename objects and apply filters before finalizing the import step. If something goes wrong and you can’t connect to the data source, you’ll lose all this work and will need to start over. Test connectivity with one table before going through this aggravating cycle.
Here’s an example of an “ouch” moment after taking a half hour to make all the table/column selections. Note the lack of a “Back” button!
When connecting to a SQL Server data source in the SSAS project designer, there are two different identities used at different times. When you initially create and test the connection in the designer, you will authenticate as yourself – the user logged into the local computer. However, when you actually load data into the table or process the model, even in the designer, Analysis Services authenticates using the identity of the SSAS service. If the SSAS instance is installed on your local development machine and you just used the default settings during setup, this is an account generated by SQL Server setup. If you’re using a production-like SSAS server, your system administrator may have configured the service to run under a specifically-defined domain principal. If the latter is the case, you’ll need to get your DBA or sys admin involved. If you have an isolated development environment, with everything install on one computer, the following instructions will take care of it.
I’m running all my SQL Server services on my local development machine and created a Tabular project that will use the AdventureWorksDW2012 sample database. The SSAS instance I plan to use for my workspace database is the local instance. First, let’s get the service account for the SSAS instance. The easiest way to view local services is to right-click “My Computer” or “This PC” in Windows Explorer and select “Manage”. Navigate to the Services node like so:
I have two SSAS instances installed and the default instance is called MSSQLSERVER. Double-click to view the Properties dialog and select the Log On page:
As you can see, the default service account is “NT Service\MSSQLServerOLAPService”. I’ve selected this text and copied it to the clipboard. I’m done with the Services and Properties windows.
Now, I open SSMS and view the Logins for the SQL Server instance my SSAS project uses for the data source. After connecting to the relational instance, expand the object explorer, Security and Logins. Two logins are needed; one for myself and one for the SSAS service account. Since this is my development machine and had installed SQL Server, I already have a login for my Windows user account and I’m a member of the sysadmin server role which grants me access to every database on the server. If you’re not a privileged user on this SQL Server instance, you will need a login created for your Windows credential and will need to at least be in the db_datareader role for the target database (example below).
To create the service account login, right-click Logins and choose New Login… Paste the service account name here and move to the User Mapping page. Select the target database and then check the box next to the db_datareader database role.
Click OK to save changes and you should be good to go.
Back in the Tabular model designer, I’m going to import only one small table to check connectivity. Once verified, I’ll click the Existing Connections toolbar icon, select all my tables, select and de-select columns, apply filters and then load the data with successful results. As soon as I see record counts, I know I’m in the clear…
Just One More Thing
On a different but related topic since you’re getting your development environment ready… If you’re not a local administrator on the SSAS machine you’re using for the Tabular workspace database (typically a development machine), you need to make sure your Windows account is in the the Server Administrators role for the SSAS instance. If this isn’t the case, you’ll get an error when you process the model. To do this, connect to the SSAS instance in SSMS, right-click the server and open Properties. On the Security page, use the Add button to add your Windows account.
Comments : 1 Comment »
Categories : BI Projects, SQL Server, SQL Syndication, SQLServerPedia, SSAS Design, Tabular Models