Powershell 5.0 on Windows 10

power01.jpgPowerShell version 5 is installed by default on Windows 10 systems.  However, if you had a previous version, say version 1.0 installed, the new version 5.0 gets installed beside it.  Having both PowerShell version installed can be a problem.  The key is know where each version installed and how to verify which version you are actually running.  Now that PowerShell is available “Everywhere” it may become more of a candidate for managing all systems across Windows, Linux and Mac.

Determine Version

To determine the PowerShell version you must open PowerShell and run the following command

  $PSVersionTable.PSVersion

If you are running Version 5.0 of PowerShell you should see something along the lines of the screen below, anything else is probably not Version 5.0.

power02.jpg

 

Where is Version 5.0

Version 5.0 is now located in this directory

power03.jpg

Since most of the stuff you do with PowerShell is admin work you may want to set the shortcut to run in admin mode automatically.

This is what the non-admin version of PowerShell looks like

power06.jpg

So go to wherever your shortcut is located and right-click and get to the “Properties”.

power07.jpg

Click on Advanced button to get to the super-admin stuff.

power08.jpg

The check the Run as administrator, with great power comes great responsibility.

Responsibility.gif

 

Now when you click on the PowerShell you will be prompted with a Windows pop-up asking you to confirm your new power.  After you accept the responsibility PowerShell will look like.

power09

You may want to consider giving the PowerShell ISE some of that Spidy Admin love as well.  I do most of my work in the ISE so it makes sense to give it the “power” too.

 

Big Data in Healthcare Made Simple — DZone Big Data Zone

Big Data in Healthcare Made Simple – DZone Big Data Knowing how to use big data to improve patient care is beneficial for those working in the healthcare industry.  Big data is valuable to the healthcare industry in dozens of ways. Physicians can use specific data about their patients taking a type of medication and their reaction to the medicine. Data can also be used to determine high-risk groups based upon common factors. Knowing how to use big data to improve patient care is…

Read on to learn more.

via Big Data in Healthcare Made Simple — DZone Big Data Zone

CDATA go from GoogleSheets to a ODBC data source

cdata_odata.pngOne of the nice things about Google Forms is that you can create a survey or quiz and collect the result into a Google Sheets Spreadsheet.  The problem is that Google sheets are not easily consumable as a data source.  In most cases, folks just export it to XLSX format to do additional work on it.  If you have an ongoing survey or a set of long quizzes this requires a lot maintenance and great care when moving the Results around.  A better way would be to consume the Google Sheet as an ODBC Data Source as a dynamic dataset.  CDATA has such a utility which I am going to review and document.  Disclaimer:  My copy of the CDATA driver was donated for me to review.

 

Installation

This is pretty straight forward and just takes a few minutes.  My suggestion is to load the 32 and 64 bit ODBC versions as well as everything else.  After the install is complete it automatically opens up the CData web page to guide you through configuring an ODBC data source.

This review is going to use Microsoft Access 2010 to “Link” to the Google Sheet. The sheet contains survey results from a group of testers, all using a Google Form to enter the results of their testing efforts.  The testers are distributed in multiple locations and are using different laptops including some ChromeBooks.

By “link”ing rather than “Import”ing we should never have to export the Google sheet to XLSX before every reporting session.  On some days we might have 3-5 reporting sessions.  The reports generated from Microsoft Access 2010 provide status, feedback, and progress for the testing team.

Google Drive

In order for this process to work,  you are going to need a Google account with some Google Drive space.  This space will contain both the Google Form, which can be accessed by most modern browsers, and a Google Sheet where we will choose to have the results saved.

GoogleDrive.png

Here is a sample of what the Google Form looked like.  Google Forms is very powerful and has some code capabilities as well as some add-ons to make it a very useful web data collection tool.

GoogleForm.png

Share the Google Form

Since the folks using this form are all over the place, we need to share a link with them that allows them to add new testing results.

Admin Mode

An admin user who can edit the form can (Send) the form to a user via e-mail or create a link that can be shared with users or generate embed code to include on another web site.  We are going to share the link with the reviewers.

Start.png

We want to (1) generate a link that can be shared, (2) shorten the link, so it doesn’t look scary and (3) copy the link so we can distribute it to the testers.

SendForm.png

User Mode

A user who has the original link to the form can create a link to share with another user.

Share.png

Private.png

In this example, the reviewers include both internal Phoenix Workgroup and external users, so we selected the “Anyone with the link” option

LinkShare.png

Google Sheet

The responses that are collected can optionally be put into a Google Sheet.   A nice touch is that Google Forms adds the first column with a timestamp that can be used as a unique identifier.  This is the Google Sheet we want to connect, via an ODBC Data Source,  to Microsoft Access.

Sheet.png

Create ODBC DSN

In this example, we are going to create a Data Source Name (DSN) using the 32 Bit ODBC driver with these steps:  (1)  Select System DSN, to create a connection that can be used by any user of this workstation, (2) Choose Add tocreate the new DSN, (3) Select the proper driver, in this example, it is the CDATA ODBC for Google spreadsheets and finally (4) Finish.

CreateODBC2.png

ClientLogin (username/password authentication) has been officially deprecated since April 20, 2012, and is now no longer available. Instead, use the OAuth 2.0 authentication standard. To authenticate to Google Spreadsheets, you will need to obtain the OAuthClientId and OAuthClientSecret by registering an app with Google Spreadsheets.

See Connecting to Google Spreadsheets to connect to Google Spreadsheets from different types of accounts: Google accounts, Google Apps accounts, and accounts using two-step verification.

OAuth

OAuth 2.0 requires a bit of fiddling, but it only needs to be done once for the connection.

Open up the Google Admin web site for your Google Account and go to security.  This is where we can configure the API’s

GoogleAdmin.png

API.png

api reference2.png

Developer Console for API’s

Now we need to go to the Developers Console for API Access to add a new project.

api manager.png

CDATA-ODBC is a known selection, start typing it and select the option when it displays, the project name will be displayed, in this example it is cdata-odbc-140816.

New Project.png

Click on the (Create), then (1) Select credentials, (2) Click on Credentials tab, (3) Click on Create Credentials button then click on the (4) OAuth Client ID.

creditientials.png

ConsentScreen.png

(1) Enter a product name, in this example cdata-odbc, (2) Save your work so far.

ProductName.png

Getting closer to creating that Client ID we need. (1) Select Other, (2) Repeat the project name (cdata-odbc), (3) Click on (Create)

CreateClient.png

Now comes a critical part, copy this stuff someplace secure because you’re going to need it in the next steps. (1) Client ID, (2) Client Secret and finally (3) (OK)

oauth client.png

All that just to get to this point.

tada.png

I would suggest that you download the JSON file with all the top-secret API stuff and save it in the same secure spot you saved the other stuff in.

json download.png

The file downloaded is a standard formatted JSON file with all the top-secret goodies in it.  Save it someplace secure, don’t let it out of your sight.

json.png

I use LastPass for keeping my secrets, passwords, and girlfriends’ phone numbers.

lastpassa2.png

Data Source Name properties

Transfer the OAuth 2 information into the Data Source Name on the ODBC management form.  The DSN should NOT have embedded spaces or special characters; this has been known to cause connection issues.

DSN01.png

We are so close now I can taste it; we need to identify the single Google Sheet this DSN will reference.

dsn02.png

Click on the (Test Connection) and cross your fingers.  The first time you use this ODBC-DSN, a browser window will open.  Why?  Because the very last step is you authorizing the cdata-odbc API to talk with the CDATA ODBC driver.

Auth01.pngauth02.png

We are sooooo close now. We have gotten this far, now (1) Allow cdata-odbc to manage the spreadsheets in your Google Drive.

auth03.png

OMG!!! It worked.  I need a shot of tequila.

auth04.png

Microsoft Access 2010

Create Linked Table

Either open or create an Access database.  (1) We need external Data; (2) it will come from an ODBC source. I wonder where that would come from?

accessExternal

Now we need to “Link” not “Import”. (1) Link, not Import, (2) Looks OK to me

Link.png

Now we select the DSN (1). The system DSN we created is under the (Machine Data Source) tab, (2) Select the DSN we created then (3) Again, looks OK to me.

selectDSN.png

We now need to select the (Responses).  (1) Is the responses (I don’t know what the item under it is) then (2) Still looks OK to me.

linkedtables.png

The next step takes a few seconds while the Google OAuth is completed and the Google Sheet is turned into a Linked Table.

linked.png

View and edit Linked Table

Now by double-clicking on the linked-table Access will display the contents.  The Linked Table will have a new column called ID added to it; there is a unique identifier in this column that helps make the CDATA-ODBC capable of editing that row.

ViewTable.png

Updating information on the table can be done from the table viewer

update.png

As the testers add new testing results to the Google Form they will automatically appear here, there is no need to export or copy-paste anything.  If we add a row to the Linked Table inside of Access, it will automatically appear in the Google Sheet in the Google Drive.

Linked Table Structure

The CDATA ODBC Linked Tables data types for each of the columns are selected by the ODBC driver based on the format of the Google Sheet column.  You may not like the data types selected.

TableStructure.png

If you attempt to change these you will be greeted with a message which translated means “Tough Luck, live with it”.  There are some Access tricks to handle this but that’s another post.

failed.png

Now we can use the Power of Microsoft Access to produce forms to maintain the Google Sheet or create reports.  Most Forms and reports start off with a query to drive them.

query.png

This query helps to drive the Microsoft Access form used to update the information from the Google Sheet.

Access form.png

The Microsoft Access reporting capability provides reports from the simple list report all the way to data analytics with business charts.

report.png

Summary

“See the world as a database” and not like a spreadsheet.  I am a database person; I visualize things into tables or collections, rows or documents and SQL or JSON.  I’m weird like that since it seems like most other folks use spreadsheets for doing just about everything.

The ability to take a Google Spreadsheet and see it as a database is very powerful, especially considering other Google add-ons like Google Forms, etc. use Google Sheets as their data store.

CDATA has all sorts of drivers to connect to all sorts of data sources that are not typically considered a database, and offers drivers that make it less complicated to connect sources that might otherwise be difficult to connect to as a database.

CDATA isn’t free or open-source and is limited to a Windows environment, but that doesn’t restrict how useful tools like this can be in some customer solutions.  One of my mentors, Dr. John Weiner, University of Buffalo, told me “Sometimes free is not the best buy, or even the only buy; be prepared to spend some cash.”

This post was an actual solution done for a customer; some of the details are masked out. The customer has decided to purchase the CDATA driver.  I can see additional solutions of this type and a training class or web lecture in the future.  You don’t really know something until you teach it.