Tips for Using Azure Data Catalog

It seems there's a lot of interest in Azure Data Catalog from the customers that I work with. Since I've been discussing it a lot recently during projects, I thought I'd share a few thoughts and suggestions.

Register only Production data sources. Typically you won't want to register Development or UAT data sources. That could lead to users seeing what appears to be duplicates, and it also could lead to inconsistency in metadata between sources if someone adds an annotation to, say, a table in UAT but not in Production. 

 
 

Register only data sources that users interact with. Usually the first priority is to register data sources that the users see-for instance, the reporting database or DW that you want users to go to rather than the original source data. Depending on how you want to use the data catalog, you might also want to register the original source. In that case you probably want to hide it from business users so it's not confusing. Which leads me to the next tip...

Use security capabilities to hide unnecessary sources. The Standard (paid) version will allow you to have some sources registered but only discoverable by certain users & hidden from other users (i.e., asset level authorization). This is great for sensitive data like HR. It's also useful for situations when, say, IT wants to document certain data sources that business users don't access directly.

Use the business glossary to ensure consistency of tags. The business glossary is a capability of the Standard (paid) version and is absolutely worth looking into. By creating standardized tags in the business glossary, you'll minimize issues with tag inconsistency that would be annoying. For example, the business glossary would contain just one of these variations: "Sales & Marketing", "Sales and Marketing", or "Sales + Marketing".

Check the sources in the "Create Manual Entry" area of Publish if you're not seeing what you're looking for. There's a few more options available in Manual Entry than the click-once application.

Use the pinning & save search functionality to save time. For data sources you refer to often, you can pin the asset or save search criteria. This will display them on the home page at AzureDataCatalog.com so they're quicker to access the next time.

Use the Preview & Profile when registering data when possible. The preview of data (i.e., first X rows) and profile of data (ex: a particular column has 720 unique values that range from A110 to M270) are both extremely useful when evaluating if a data source contains what the user really wants. So, unless the data is highly sensitive, go ahead and use this functionality whenever you can.

 
 

Be a little careful with friendly names for tables. When you set a friendly name, that becomes the primary thing a user sees. If it's very different from the original name, it could be more confusing than helpful because users will be working with the primary name over in tools such as Power BI.

 
 

Define use of "expert" so expectations are clear. A subject matter expert can be assigned to data sources and/or individual objects. In some organizations it might imply owner of the data; however, in the Standard (paid) version there is a separate option to take over ownership. Usually the expert(s) assigned indicates who knows the most about the data & who should be contacted with questions. 

Be prepared for this to potentially be a culture change. First, it's a culture change for DBAs who are responsible for securing data. The data catalog may absolutely expose the existence of a data source that a user didn't know about--however, remember that it only exposes metadata and the original data security is still in force. The other culture change affects the subject matter experts who know the data inside and out. These folks may not be used to documenting and sharing what they know about the data. 

You Might Also Like...

Overview of Azure Data Catalog in the Cortana Intelligence Suite <--Check the "Things to Know about Azure Data Catalog" towards the bottom this post for more tips

How to Create a Demo/Test Environment for Azure Data Catalog 

Setting Up a PC for Azure Cortana Intelligence Suite Development

Some development aspects of the Cortana Intelligence Suite can occur in the Azure Portal. There are also some additional client tools which are helpful, or potentially required, to fully create solutions. This is a quick checklist of tools you probably want to install on a development machine for purposes of working on the analytics, BI, and/or data warehousing elements of Azure.

1. SQL Server Management Studio (SSMS)

The latest version of SSMS is recommended for compatibility with Azure services, as well as backwards compatible with all SQL Server versions back to 2008.

Download SSMS:  https://msdn.microsoft.com/en-us/library/mt238290.aspx


2. Visual Studio 2015

The latest 2015 version of Visual Studio is recommended for full functionality for the newest components. If you choose to do a customized VS installation, be sure to select the option to install Microsoft Web Developer Tools. (If you don't, when you try to install the Azure SDK later it won't install properly because prerequisites are missing. Yeah, yeah, I've been there.)

If you don't have a license available, look into using the VS Community edition.

Download VS 2015: https://www.visualstudio.com/downloads/download-visual-studio-vs 

Note: in addition to "Visual Studio 2015," there's also a "Visual Studio 15 Preview." The 15 Preview is *not* the same thing as Visual Studio 2015, even though 15 is in its name. So, just watch out for that in terms of naming.



3. SQL Server Data Tools (SSDT) for Visual Studio 2015

Here's is where you gain the ability to create BI projects: SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), and SQL Server Reporting Services (SSRS). These SQL Server BI projects aren't considered part of Cortana Intelligence Suite, but if you're creating an analytics, BI, and/or data warehousing solution you may need at least one of types of BI projects as part of the overall solution.

With the latest version of SSDT for VS 2015, you'll also be able to interact with all versions of SQL Server, as well as Azure SQL Database and Azure SQL Data Warehouse.

Example of what an Azure SQL Data Warehouse cloud resource looks like from within Visual Studio (SSDT):


4. Azure SDK

The Azure SDK sets up lots of libraries; the main features we are looking for from the Azure SDK right away are (a) the ability to use the Cloud Explorer within Visual Studio, and (b) the ability to create ARM template projects for automated deployment purposes. In addition to the Server Explorer we get from Visual Studio, the Cloud Explorer from the SDK gives us another way to interact with our resources in Azure. 

Example of what the Cloud Explorer pane looks like in Visual Studio (by Resource Group, and by Resource Type):

Example of what you'll see related to Azure Resource Groups after the Azure SDK is installed:

Example of various QuickStart project types available (none of these are directly related to Cortana Intelligence Suite, but might factor into your overall solution):

Download Azure SDK: https://azure.microsoft.com/en-us/downloads/ <--The file called "VS 2015" is the Azure Pack


5.  Relevant Visual Studio Extensions

These are important extensions for working with Cortana Intelligence Suite at this time:
-Microsoft Azure Data Lake Tools for Visual Studio 2015 <--Automatically installed as part of the Azure SDK
-Microsoft Azure HDInsight Tools for Visual Studio 2015 <--Automatically installed as part of the Azure SDK
-Microsoft Azure Data Factory Tools for Visual Studio 2015

At the time of this writing (June 2016), Azure Data Factory Tools are not automatically installed with the Azure SDK. That will probably change at some point I would guess.

Example of the Cortana Intelligence Suite projects you'll see after the Azure extensions are installed (a U-SQL project is associated with the Azure Data Lake Analytics service):

Download: https://azure.microsoft.com/en-us/downloads/ <--Tthe file called "VS 2015" is the Azure Pack


6.  Microsoft Azure Storage Explorer and/or AzCopy

I really like the new standalone Azure Storage Explorer for uploading and downloading files to Azure Storage. AzCopy is another alternative - AzCopy is a command line utility instead of a graphical UI, so it's better suited for automation and scripting purposes. 

Example of what Azure Storage Explorer looks like:

Download Microsoft Azure Storage Explorer: http://storageexplorer.com/ 
Download AzCopy: https://azure.microsoft.com/en-us/documentation/articles/storage-use-azcopy/

Note: There is a different Azure Storage Explorer on CodePlex with an almost identical name to the one I'm referring to.


7. Azure PowerShell

Even if you don't tend to utilize scripting and automation tremendously, chances are you'll need PowerShell for something. Installing Azure PowerShell adds the Azure management cmdlets to Windows PowerShell.

Download: https://azure.microsoft.com/en-us/documentation/articles/powershell-install-configure/


8. Azure Feature Pack for SQL Server Integration Services

In step 3 above we installed the BI projects including SSIS. There's also a feature pack for SSIS which adds connection managers, tasks, and components related to Azure.

Download: https://msdn.microsoft.com/en-us/library/mt146770%28v=sql.130%29.aspx

Depending on what you need to do, there might be additional items to install (such as the Azure CLI), but the above list should be a good start for tools related to development of analytics, DW, and/or BI solutions. 

You Might Also Like...

Building Blocks of Cortana Intelligence Suite in Azure

What is the Cortana Intelligence Suite?

Building Blocks of Cortana Intelligence Suite in Azure

I've been really enjoying learning more and talking with customers about hybrid analytics and data warehousing solutions. The services which are evolving as part of the Cortana Intelligence Suite are really amazing. I've put together a new presentation called the Building Blocks of Cortana Intelligence Suite.

I'll be presenting it for the first time at SQL Saturday Atlanta in May, followed up by the Charlotte BI Group and Carolina IT Pro Group in June. This will be a fast-moving introductory session wherein I'll introduce each component of the suite and discuss its purpose and use cases. I'll also call out important requirements for expertise, such as key tools and languages.

Each section will wrap up with an example of the 'building blocks' to formulate a solution. Although these 'building blocks' examples are greatly simplified, my hope is it will generate ideas for how the different Azure components can fit together for formulating hybrid solutions. 

The materials will changing over time as I refine the presentation and as new capabilities in Azure evolve and mature, so check back later for updates.

You Might Also Like...

What is the Cortana Intelligence Suite?

How to Create a Demo/Test Environment for Azure Data Catalog

Azure Data Catalog is a Software as a Service (SaaS) offering in Azure, part of the Cortana Intelligence Suite, for registering metadata about data sources. Check this post for an overview of Azure Data Catalog key features. (I'm a big fan of what Azure Data Catalog is trying to accomplish.)

There are a couple of particulars about Azure Data Catalog which make it a bit more difficult to set up a Demo/Test/Learning type of environment, including:

  • You are required to sign into Azure Data Catalog with an organizational account. Signing in with a Microsoft account (formerly known as a Live account) won't work for Azure Data Catalog authentication, even if that's what you normally use for Azure.
  • One Azure Data Catalog may be created per organization. Note this is *not* per Azure subscription - if your account has access to multiple subscriptions, it's still one catalog per organization.

These restrictions are because the intention is for Azure Data Catalog to be an enterprise-wide sole system of registry for enterprise data sources.

Summary: Creating a Demo/Test Environment for Azure Data Catalog

Because of the above two restrictions, we need to create a Demo/Test/Learning sort of environment in a particular way. For the remainder of this post, the objective is to create a Data Catalog outside of your normal organizational Azure environment - i.e., associated to an MSDN account for instance. 

With some very helpful advice from Matthew Roche (from the Azure Data Catalog product team at Microsoft), the best method currently to create a Data Catalog test environment is as follows:

  1. Sign into the Azure portal with a Microsoft account (not with your organizational account). You should be the administrator of this subscription. For instance, my subscription is associated with my MSDN.
  2. In your Azure Active Directory (AAD), create a new AAD account. This cannot be associated to a Microsoft account; it needs to be a native AAD account. A native AAD account is recognized by the Data Catalog service as an organizational account. 
  3. Allow this new AAD account to be co-administrator of the subscription. This will permit the AAD account to provision the new Azure Data Catalog service.
  4. Go to the Azure Data Catalog portal at www.azuredatacatalog.com and sign in with the new AAD account. Provision a new Azure Data Catalog from here. You will continue to do all of the work in Azure Data Catalog with this separate AAD ID (and additional AAD IDs if desired).

The objective of this is to leave the Azure Data Catalog in your 'real' organizational Azure subscription free of test or temporary use data sources - i.e., you wouldn't want users in your environment to discover something like an AdventureWorks sample database in the catalog (loophole: if you are paying for the standard version of Azure Data Catalog, rather than the free version, you do get security capabilities and could restrict a data source to just yourself so others can't find it).

Sidenote: One additional important thing to be aware of with Azure Data Catalog is that a data source may be registered in the catalog only once. This is to prevent duplicates which could be really confusing to users of the system.

Below are further details about how to make this approach work.

Details: Creating a Demo/Test Environment for Azure Data Catalog

Step 1: Sign into Azure portal for which you are an administrator.

First, sign into the Azure portal with your Microsoft account (such as user@outlook.com). As of the time of this writing (April 2016), Azure Active Directory is still managed in the old portal not in the new portal yet. The old portal can be found at https://manage.windowsazure.com/

For our demo/test purposes, this should not be your organizational account (such as user@companyname.com). And of course, you need administrative privileges for the Azure subscription.

Step 2: Create a Native Azure Active Directory Account.

Go to the Active Directory menu, then select your default directory:

On the Users page, select Add User at the bottom:

Create a new user with the name you prefer:

 
 
 

Be sure to jot down the temporary password assigned by Azure.

At this point, you should see your new user on the AAD Users page. The key to making this all work is the account is sourced from 'Microsoft Azure Active Directory' and is *not* a Microsoft Account. (An account sourced from your organization's Active Directory works too...but we're trying to create a demo outside of the organizational Azure tenant.)

Next let's reset that temporary password now. 

Open up an InPrivate or Incognito browser window and go to https://login.microsoftonline.com/. By using InPrivate or Incognito, the login screen will reliably accept any type of account (otherwise it makes assumptions based on the type of account you're logged onto your machine with currently). You'll want to either use a different browser, or close the Azure portal, before this step so that it doesn't sign you in with the account you're logged into Azure with.

Sign in with the new AAD account we just created. When prompted, put in the current temporary password and reset to a new password. Close this browser window when finished resetting the password.

Login.jpg
 

Step 3: Provide Co-Administrator Permissions to the New AAD Account.

Back in the Azure portal (we're still using the old portal at https://manage.windowsazure.com/ since this functionality isn't yet exposed in the new portal). Here you sign in with your Microsoft account again (just like step 1). Go to the Settings menu, then the Administrators page, then click Add:

AzureAdministrator.jpg

Input the e-mail address of your new AAD user. You'll see a green check when it's validated.

AzureAdministrator2.jpg
 

At this point you should see your native AAD account listed on the Administrators page. Now we know that account has permission to create the Azure Data Catalog service. (I can be more liberal with this sort of setting because the Azure tenant I'm working in only contains demo data, not any real data.)

AzureAdministrator3.jpg

Go ahead and close the Azure browser window as we are finished with the Azure portal.

Step 4: Provision a New Azure Data Catalog from the Data Catalog Portal.

Now it's time to provision the Azure Data Catalog using our AAD account.

Launch a new browser window using InPrivate or Incognito (this will ensure you'll be able to seamlessly sign in with your AAD account) and go to the Azure Data Catalog portal at https://azuredatacatalog.com. When prompted, sign in with your AAD account. 

If everything with the AAD account is set up correctly, you should see a page which prompts you to create a new Azure Data Catalog:

CreateCatalog.jpg

Tip: Remember you can only have one catalog per organization, so be sure to give it a pretty broad name.

You can go ahead and add any other users and administrators for the Catalog as appropriate, provided they are not personal Microsoft accounts. 

Troubleshooting Access to Azure Data Catalog

This account does not have permission to access Azure Data Catalog

When signing into the Azure Data Catalog portal, the message "This account does not have permission to access Azure Data Catalog" is generated when you have signed in with an appropriate kind of account, and a catalog already exists somewhere, but your account doesn't have permission to access it. 

ADCPortal_RequestAccess.jpg

To figure out more info about the existing data catalog, first check if you see any catalogs in the new Azure portal at https://portal.azure.com/. If no catalogs are listed, this means the catalog resides in a subscription which you don't have permission to see in the Azure portal. Since there's one catalog per organization - if there's a subscription you cannot see, it's possible that's where the catalog is at. 

ADCPage.jpg

To get more information try to create a new catalog. You'll see a message "Only one Azure Data Catalog is supported per organization. A Catalog has already been created for your organization. You cannot add additional catalogs." Click the link under that message to "Access existing Azure Data Catalog." 

ADCPortal_RequestAccess2.jpg
 

Under User, you should be able to see the name of the catalog which may give you a hint as to which subscription it resides in. In any case, you need to talk to your Azure service administrator if this happens to determine if the catalog that is set up is really what/where you want it to be.

ADCPage2.jpg
 

You've logged in using a personal Microsoft account

When signing into the Azure Data Catalog portal, the message "You've logged in using a personal Microsoft account" is generated when you're not using an organizational account recognized by the Data Catalog service. Here's where you want to refer to the instructions above in this post to create a native Azure Active Directory (AAD) account to use for logging into Azure Data Catalog.

ADCPortal_PersonalAccount.jpg