Wednesday, September 11, 2013

You cannot specify alternate security options in Tabular mode

I tried restoring a SQL Server Analysis Services (SSAS) tabular model using the SkipMembership security option (overwrite new roles, but don't overwrite role members) and got the following error:

You cannot specify alternate security options in Tabular mode. (Microsoft.AnalysisServices)

I had not heard that this was a limitation and was not able to find anything in Microsoft's TechNet Library documentation to suggest that alternate security options are not supported.

I have a project where I need to publish a tabular model backup file to a reporting server that lives on a different domain than my processing server, with completely different security settings. I've ruled out synchronization because I basically only have one-way communication, and the destination (reporting) server actually pulls from the source (processing) cube server during a synchronization and uses Windows authentication, which won't work for me due to the different domains and firewall settings.

So basically my only option is to do a backup, copy, and restore to publish my cube to the reporting server. Here's what I ended up doing for now. Not sure I like it, but it works for what we need.

1. Backup cube on processing server.
2. Copy .abf backup file to reporting server
3. Restore .abf backup file (with invalid security and all)
4. Run an XMLA command on the reporting server to update SSAS role membership

Let me know if you have any better ideas in the comments!

Monday, June 3, 2013

SQL Server 2014 Announced

It's official - Microsoft announced SQL Server 2014 at TechEd today. According to the company's data sheet, here are the key BI features to look out for:


  • Project Codename "Data Explorer"
  • Windows Azure Marketplace
  • Windows Azure HDInsight Service & Microsoft HDInsight Server (preview)
  • Parallel Data Warehouse with Polybase
  • Excel in Office - PowerPivot & Power View
  • Project Codename "GeoFlow" in Excel
  • Data Mining Add-ins for Excel
  • StreamInsight
  • Data Quality Services
  • Master Data Services
  • Analysis Services
  • BI Semantic Model

Project Codename "Data Explorer" and "GeoFlow" will now officially be part of the SQL Server product offering, and there will likely be at least incremental enhancements or improvements to all of the other features mentioned.

For OLTP systems, the new in-memory OLTP (I assume this is Project Codename "Hekaton", which was discussed last year at PASS Summit) offers up to 10x performance gains over disk-based storage, according to Microsoft, and appears to support some interesting hybrid scenarios where some database objects can be in-memory and others on disk in the same database. Very cool.

Microsoft is also focusing more heavily on hybrid cloud scenarios with Windows Azure and how customers can take advantage of cloud storage for disaster recovery, backups, BI reports, etc. while keeping primary application databases on-premises.

At first glance, I'm glad to see that GeoFlow and DataExplorer are officially coming into the fold. It has been a lot of fun playing with these so far, so I'm excited to see these at work in real-life scenarios with some of my clients.

Polybase looks very cool for companies who are running PDW or plan to make the switch. It's incredibly powerful to be able to expose data in a Hadoop cluster to us lowly SQL analysts and developers so we can run SELECTs and JOINs and GROUP BYs without needing to care how or where the data is actually stored.

Update: Here is a thorough write-up on today's TechEd announcements from Mary Jo Foley at ZDNet.

Sunday, May 19, 2013

The Big Data Bubble

I love Tableau. As a data geek, I'm incredibly enthused by Tableau's ability to recognize and capitalize on the overwhelming trends toward fact-based decision making and data-hungry decision makers. Plus their visualizations and ease of use are pretty hard to beat.

But the company's 64% one-day gain following their IPO on Friday and the corresponding, nearly $2 billion valuation on $127.7 million 2012 revenue has the cautious economist in me hoping this isn't an example of 'Big Data' hype setting its roots for a forthcoming bubble and its inevitable burst.

Just to reiterate previous rants on the subject, the term 'Big Data' is overused and has creeped out well beyond the scope of its original definition. To data professionals, big data has gone from a short-hand way of saying 'massively parallel, typically batch-oriented, usually read-only stores of data distributed, stored and processed on large clusters of commodity hardware', to an annoying term used by our managers, software and services marketers, and now evening news journalists to denote anything from 'Big Brother' to just plain old data.

I recently interviewed a young developer starting out in his or her career who was 'obsessed with big data.' When pressed for examples, he or she talked about an incredibly simplistic SQL database application. I realized he or she wasn't talking about 'big data' - they were just talking about data.

Clearly, the term has taken on so many various meanings and connotations that any useful meaning and connotation have become lost. Which is I think why it's such a catchy term - people use it to explain things they don't understand. Like 'cloud computing', 'cyberspace', and others before it, the meaning behind the words 'Big Data' has gone the way of the dodo. I bet there is an amazing data visualization waiting to be designed that shows the inflection point between a technical term's ubiquity on one axis and its complete and utter lack of meaning on another. There must be an inflection point there that shows when a term leaves its technical jargon nest, has a short-lived but blissful first flight of proper use and conveyance of complex ideas to inquisitive minds, then crashes to the ground in a bloody, feathery mess of misuse and overuse.

If you want to use the term 'Big Data' around me or other grumpy data professionals like me, here are some ground rules:


  1. Big Data is not Big Brother
  2. Big Data is not just regular old data
  3. Big Data is not always unstructured
  4. Unstructured data is not always big
  5. Semi-structured data is not unstructured
  6. You will comply immediately with any request to define what is so 'Big' about the data you're talking about
  7. Just because a company uses data doesn't make them a big data company


Should you violate any of these rules, your credibility with me will be completely shot and I will likely tune out anything you say for the rest of the conversation after using the term 'Big Data'.

To investors: Please, please, please don't buy the hype. Tableau is a great company, with great products, strong financials, and solid growth. But keep in mind, data companies are not special. They are subject to the same risks and downturns as any other industry. Don't believe me? Next time you're reading an article touting the trends of 'Big Data', mentally replace 'Big Data' with 'cyberspace', and remember that bubbles can happen to all of us.

Thursday, April 18, 2013

Why I Still Use VirtualBox Over Hyper-V Manager in Windows 8

I love that Hyper-V is built in to Windows 8. But I have some odd use cases, which still make Oracle's Virtual Box my go-to for launching virtual machines.

I am both a Mac and a PC user, so I need to share large files between the two, which requires formatting my external hard drive as exFAT. (ExFAT was developed by Microsoft to address incompatibility between Mac and Windows file systems - NTFS, the default Windows file system, is read-only on Mac; HFS+, the default file system for Mac OS can't even be read by Windows; and FAT32, Windows' predecessor to NTFS, had a file size limitation of 4GB.)

The only problem is that Hyper-V currently only supports mounting VHDs stored on NTFS drives. This means I can't mount any of the VHDs on my exFAT drive. So either I still have to carry around 2 drives and copy VHD files back and forth to sync them, or I can just continue to use VirtualBox. So, as much as I'd like to switch over and start using the wonderful features of Hyper-V, I'll have to wait until Microsoft supports exFAT, or someone invents a new file system.

As a side note, if you do use Hyper-V Manager to create virtual hard drives that you'd like to share with non-Hyper-V VM clients, be sure to set the virtual hard disk format to VHD. The default for Hyper-V, VHDX, will only work with Hyper-V, so you won't be able to mount the VHD on your Mac, VirtualBox, VMWare, etc.

Sunday, March 31, 2013

Visio 2013 Fail - Microsoft Removes Key Database Modeling Features

I complain enough as it is in my personal life, so I try to keep things pretty positive here. But Microsoft really screwed up this time. They removed all of the core database modeling features in Visio 2013.

Alberto Ferrari says it best in the comments:

C'mon guys, I wonder if you ever *really* listen to your customers.

Here's why this is a problem. Serious BI practitioners have used Visio for years to design and manage their data models. I'm a BI consultant and I have used Visio on an almost daily basis for as long as I've been working with data. One of the first things I do on a new client project is open up Visio, configure the drivers to connect to the client's database, and reverse-engineer the entire schema. As I meet with business and technology stakeholders, I use Visio to capture requirements as logical data models. I can manage attributes, change their data types, and even change drivers to change all of my data types from, say, SQL Server to Oracle.

But no more. Not with Visio 2013. Here's the rundown.
  • No ability to generate a diagram from existing code or a database definition (no reverse-engineer)
  • No ability to go from diagram to code or database (no forward-engineer)
  • No database drivers or data types

According to Microsoft, with Visio 2013 they "place a strong emphasis on diagramming and sharing instead of rigorous modeling." In my opinion, they took that too far. "Rigorous modeling" - you mean like defining data types? That doesn't seem all that "rigorous" to me. I was mad enough when Microsoft removed forward-engineer - not to mention alarmed at the potential implications, which have now gone from potential to quite real.

What this means, effectively, is that data professionals who upgrade to Office 2013 lose the ability to:
  • Design or manage a physical schema
  • Document existing data structures in any sort of automated way
  • Manage data types
  • Create indexes
  • Create database constraints
  • Create primary key/foreign key constraints
  • Manage drivers or custom data types
  • Do anything worthwhile with data models whatsoever, except make slightly prettier pictures of tables and column names... by hand

I know I'm a little late to the party in finding out about this - I only switched over to Windows 8 and Office 2013 on my primary laptop a few weeks ago, but I'm actually not sure what I'm going to do about it. ERwin and Embarcadero aren't cheap and there's a bit of a learning curve with both of them. Going from Visio to CA or Embarcadero for data modeling is about the same as going from MSPaint to Photoshop - great for seasoned pros, but too specialized and too expensive for a lot of us. With Visio, I could always count on clients and colleagues being able to collaborate and review files with me. If they have to cough up a couple grand per license plus yearly maintenance fees, it greatly diminishes the utility of the tool for me.

You screwed up, Microsoft. Please, come to your senses and bring this feature back. People use it. A lot.

P.S. If you use Visio 2010 and miss the forward engineer feature, I recommend you check this out: http://forwardengineer.codeplex.com

Wednesday, March 20, 2013

Sound Data Advice From a Fortune Cookie

Put the data you have uncovered to beneficial use.

Pretty good advice, if you ask me...

Tuesday, March 19, 2013

Chrome Console Error

I came across an error while testing a website tonight using the Google Chrome JavaScript Console. (Using Chrome? Hit Control - Shift - I for Windows or Command - Option - I for Mac to see the Chrome Developer Tools!)

Every time I loaded the page in Chrome, I got this error. Not in other browsers, only in Chrome. Here's the error:

Port error: Could not establish connection. Receiving end does not exist.

I thought there was something wrong with the site, but after scratching my head and tinkering around a bit I couldn't track down the cause. Luckily, someone else beat me to it and thanks to a quick web search (see the helpful links below), I disabled the Chrome to Phone extension and VoilĂ ! the error message disappeared. I guess now it's back to debugging my own error messages...

https://groups.google.com/forum/?fromgroups=#!topic/easyxdm/gVigio5TYiw

https://code.google.com/p/chrometophone/issues/detail?id=316

https://code.google.com/p/chrometophone/issues/detail?id=451