Friday, November 04, 2011

Problems Browsing Codename “Social Analytics” Collections with Popular OData Browsers

Casual users of Microsoft Codename “Social Analytics” datasets probably will prefer to browse the data before importing it to Excel PowerPivot worksheets or writing .NET applications to display it. Browsing large datasets is one key to implementing the Holy Grail of “Agile BigData.” My Using the Microsoft Codename “Social Analytics” API with Excel PowerPivot and Visual Studio 2010 post of 11/2/2011 describes how to use these two data access approaches.

The Getting Access to the Social Analytics Lab post’s “Using the Social Analytics API” section contains the following:

Note: You may see an “Explore this Dataset” option on the DataMarket offer page. This explorer is not compatible with the Social Analytics source and should not be used to explore the data.

The above note led me to try other popular OData browser applications.

Following are the three free OData browsers listed as OData Consumers by OData.org that I tested with the Windows Azure Marketplace DataMarket’s VancouverWindows8 dataset:

Note that Tableau Public is free. The related Tableau Desktop edition offers a 14 day free trial.

When and if I get an invitation to SQL Labs’ Codename “Data Explorer”, I’ll add a section for it.

Update: 11/6/2011: My Querying Microsoft’s Codename “Social Analytics” OData Feeds with LINQPad post describes using LINQPad to browse Codename “Social Analytics” data. According to Microsoft’s Rich Orr, only Excel PowerPivot and LINQPad are supported Social Analytics browsers today.

Sesame Data Browser

The desktop (out-of-browser) version of Fabrice Marguerie’s Sesame Data Browser was one of two browsers I tested that were capable of displaying collection members. OData Explorer from the Silverlight team was the other.

Note: If you use the online (in-browser) version, you receive the following error message when clicking a collection to display:

image

To give Fabrice’s browser a test drive, do the following:

1. Open the Sesame Data Browser at http://metasapiens.com/sesame/data-browser/preview/.

2. Right-click the Web page and choose Install Sesame Data Browser on this Computer to open the Security Warning dialog:

image

image2. Click Install to create a Start Menu choice and desktop icon, and open the Silverlight application on the desktop.

3. Open the New Connection list, select OData, type https://api.datamarket.azure.com/Vancouver/VancouverWindows8/ in the Address text box, select Azure DataMarket, and paste your Account key in the text box:

image 

4. Click OK to populate a list of collections for the Social Analytics dataset in the left pane and click ContentItems to display an initial 15 items:

image

Note: The Social Analytics “Ingestion Engine” adds approximately 3,000 items per day to the VancouverWindows8 dataset. See my Using the Microsoft Codename “Social Analytics” API with Excel PowerPivot and Visual Studio 2010 post of 11/2/2011 for more details about the “Ingestion Engine.”

If your Windows LiveID that you used to obtain your Codename “Social Analytics” invitation isn’t cached, you won’t see ContentItems in the right pane.

5. Scroll the columns to expose ImageUrl, which (unlike Excel PowerPivot) displays the linked image:

image

image6. Click the Filter (funnel) button to toggle filter text boxes and scroll to a searchable field, such as Published on. Click the small grid symbol (highlighted) to open a date picker control and select a date suited for use in <, <=, >= or > comparison:

image

image7. Click the filter icon adjacent to the date picker button to open a list of comparison operators and choose the one you want (IsGreaterThanOrEqualTo for this example):

image

image8. Click the gold filter button to apply the filter and update the list.

image

image9. Click the Load More button to load 15 more rows to the list:

image

10. Click the Toggle Column Chooser to open a checked list box of column names:

image

11. Clearing a column check box to hide a column throws the following fatal exception:

image

Clicking OK displays the following error message:

image

The only way I’ve found to recover from this error is to click the toggle button again to close the list box without hiding columns.

12. Click the XML tab to display the AtomPub-formated XML document and the Query String button to display the query that retrieved the data in a text box.

image

13. Click the Link button to display text boxes with links to the current selection:

image

Warning: Clicking another Collection link in the left pane in an attempt to change the data source for an existing connection throws a fatal exception that empties Sesame’s window and often prevents closing it. Use Task Manager to close the window in this case.

14. Scrolling over the arrow icons in column 1 opens a list box with buttons that offer to Open to Open in New Query View the selected collection:

image

Selecting a collection results in the same fatal error of the preceding step.

16. Creating a new connection and clicking the LatestThreadId link at the bottom of the left column opens a text box in which you can paste a GUID copied from the ContentItems grid’s first column:

image

17. Clicking Open displays the following error message:

image

If you’re careful, you can browse collections much more quickly in the Sesame browser than Excel PowerPivot worksheets. What’s missing is the capability to execute custom OData queries.

OData Explorer

imageOData Explorer is a Silverlight Web application that, like the Sesame browser, you can run from as an out-of-browser application on your desktop. After installing Explorer on your desktop, do the following:

1. Launch OData Explorer from its desktop shortcut and click Add New to open the Add New OData Workspace dialog.

2. Type a Workspace Name and https://api.datamarket.azure.com/Vancouver/VancouverWindows8/ in the textboxes:

image

3. Click OK to display the Social Analytics Collections list:

image

4. Click ContentItems to open the Windows Security dialog and shortly thereafter fill the grid with columns for Edit[ing] and navigation properties (in blue), as well as scalar property values (black, not visible below):

image

5. Typing your Windows Live ID user name and password doesn’t work, so click OK to dismiss the dialog and scroll to the right to view the property values:

image

6. Click next to scroll the next batch of items. Hovering over the Next button displays a text box with the SkipToken GUID value:

image

The OData query to obtain the next group of rows, which later appears in the text box at the top of the page is: https://win8.social.azure.com/VancouverAuthenticatedApi.svc/ContentItems?$skiptoken=guid'8d3b3517-7e8e-445f-8b19-04040bd6627a'.

7. Clicking Edit in the 1st column opens a ContentItem edit dialog which lets you update or delete the selected item in the collection:

image

8. The underlying collections are read-only, so you receive 502 Forbidden errors if you click Save Changes or Delete. However, edits appear to be saved in the local dataset. Click Close to cancel the operation.

9. Attempts to sort the collection on date fields fail with username/password requests, followed by this mysterious information message:

image

10. OData queries (filters) built with the Query Builder feature, such as /ContentItems?$filter=CalculatedToneId eq 3 don’t appear to work.

Despite the annoying credentials requests, this OData browser probably is more useful than the current Sesame version because the errors that occur aren’t fatal. It remains to be seen why custom sorts and queries don’t work.

Tableau Public

imageTableau Software offers Tableau Public as a free application targeted at bloggers. You can download Tableau Public here. The Windows Azure Marketplace DataMarket site mentions Tabeau Software in its Microsoft Codename "Social Analytics" - Windows8 listing and has a Using Marketplace Datasets with Tableau Public page, but its instructions don’t appear to be valid for v6.1 or apply to secure datasets.

1. Launch Tableau Public 6.1 from the desktop icon it installed and click the launch window’s Open Data button to open the main window and Connect to Data dialog. Select Windows Azure Marketplace DataMarket:

image

2. Click OK to open the OData Connection dialog, paste the Vancouver Windows 8 URL in the Step 1 text box, mark the Use and Account Key option, and past your Account Key in the Step 2 text box:

image

3. Click Step 3’s Connect button to attempt to connect to the dataset. You receive the following error message:

image

4. Click Show Details to display the less-than-useful error message:

image

Pending workaround details from Tableau Software, my conclusion is that Tableau Public isn’t suitable for browsing Codename “Social Analytics” data sets.

0 comments: