Browsing & exploring a “big” #kindle library, part II, now with #Excel2013 #PowerView

Well just recently updated my Kindle orders & highlights data from Amazon (see my previous post here), and just remembered that would also make a very good case for some cool (and useful, imo) Excel 2013 PowerView dashboards. There’s an image available for each books, fits perfectly with PowerPivot and PowerView.

image

Valuable Data & Insights

Aside from the usual visual bells & whistles, being a kind of data “playground” and a very cool demo for friends, our team, partners & customers, there are actually some very real scenarios where this data and analysis are of great value to me:

  • remember/recap my favorite books just by checking the number of highlights I’ve made (a very reasonable indicator for that)
  • to check & control my book “budget” (unfortunately it is not, yet…, real time… )
  • check savings from free kindle books promos, just for fun (& profit ) :)
  • to choose a book to read next, very usual, based on books I haven’t read yet, ie with no highlights from me, that have good ratings (either avg stars or number of reviews, or my “calculated” overall score)
  • much better browser (PowerView or PivotViewer) from my library that the Kindle or any Kindle iPad, the native apps clearly were not developed to make it easy to browse your own kindle library when you have a few hundred books…actually one of the reason I gave up on using my Kindle device, inability to slice & dice and help me choosing what to read next (honestly, it could be awesome to have this on kindle& kindle apps… don’t get it really how it’s still missing)

Brief Excel 2013 & PowerView Walkthrough

I won’t get into much detail how to get something like these going on Excel 2013 PowerViews, there are plenty of other good resources to learn the basics on PowerPivot and PowerView, but honestly for something like this it’s  very intuitive  indeed with the new Office & PowerView.

We’ll need a main table:

image

The usual “Date” utility table…

image

Check you have both tables on the PowerPivot data model:

image

image

Create the relationship between these two tables (by my order date in this case):

image

Fine tune the default behavior of the main table in order to get a better user experience, ex: title images:

image

image

Define a Default field set:

image

Save, back to Excel and Create a new PowerView Dashboard:

image

And be creative! :) 

Ex: 2012 highlight (PowerView data “highlighting” it’s really the killer feature)image

Final thoughts

Now I have two very interesting browsers for my kindle books, both PowerView and PivotViewer , fun they’re both running SilverLight, who would figure that ?

Nevertheless, some things I am already missing in PowerView:

  • exact copy/paste/duplicate PowerView sheets… as it insists on changing display positions
  • automatic date handling, someone better to solve this soon, lets skip the needed date table shall we?  I think that would allow much wider adoption of Excel analytics. Getting really tired of date/time tables, honestly… no wonder end users don’t get them easily… they are smart
  • analyze in excel option, jumping to a native Excel Pivot Table maintaining  the specific value/context- much like SmartPivot detailby feature or Office 2012 QuickExplore , that would be killer

btw-How can you build a very reasonable Kindle library like the above just with “minimal” budget? That, I will cover soon on my personal development blog :)

Anyway…$400 in books, I really hope my wife is not reading this. :)

Take care!

Rui

 

Related Posts

Exploring saved #Kindle Highlights with #SQL2012 Semantic Search

Kindle Highlights

I have said it before, archiving book highlights is one of the best features available on Kindle. You highlight your books on Kindle or any Kindle app and  then you can review them online whenever you want, and with a little effort… you can (you should actually, who knows what amazon will decide tomorrow…) save them locally for personal reference.

Anyway, I was reading about the new SQL 2012 Semantic Search feature and I thought it could be interesting  to run the new semantic search through my Kindle highlights.

There’s an amazing sample by Roger Doherty on codeplex called MySemanticSearch. I don’t know any other good app to explore (as in freely explore…) my highlights, so this seemed to be a very good start to explore the new SQL 2012 semantic search.

Prerequisites

Be sure to :

Getting my Kindle Highlights

First of all, cranking some PowerShell and using the kindle site, I got my very own highlights for several of my Kindle books :

image

Example:

image

Loading Streams into SQL using SQL 2012 FileTable feature

Using FileStreams and the new SQL 2012 Filetable I quickly loaded these into SQL:

— Create Database
CREATE DATABASE MySemanticSearch
ON PRIMARY (Name = FileTableDB, FILENAME = ‘D:\SQL2012\MSSQL11.SQL2012\MSSQL\DATA\MySemanticSearch.mdf’),
FILEGROUP FTFG CONTAINS FILESTREAM
(NAME = FileTableFS, FILENAME=’D:\SQL2012\MSSQL11.SQL2012\MSSQL\FileStream\MySemanticSearch’)
LOG ON
(Name = FileTableDBLog, FILENAME = ‘D:\SQL2012\MSSQL11.SQL2012\MSSQL\DATA\MySemanticSearch.ldf’)
WITH FILESTREAM (NON_TRANSACTED_ACCESS = FULL,
DIRECTORY_NAME = N’FileTableDB’);
GO

Then the FileTable

CREATE TABLE MyKindleHighlights AS FileTable
WITH
(FileTable_Directory = ‘MyKindleHighlights’);

GO

Loading files into BLOBs using SQL FileTable shared folders

Then just copied all my text files into the newly created folder for the FileTable (created automatically by SQL):

image

image

The table updates automatically, content/streams up and running!

image

Creating the full text/semantic index

You can do this with SQL of course (see here), I choose the lazy way, through Management Studio UI :)

image

image

image

image

image

image

image

Using semantic search

It’s now possible to use semantic queries to explore my highlights:

image

Well…. not so great, I don’t know exactly why, but my text files (I think apostrophes and some non printable chars) caused some confusion in the indexer, I get keywords like “isna”, “whata”… not good.

Reparsing the text files was not an option for now, I opt for adding some more stop words to the already existing in the system stop words list. For that I listed all the words in my semantic index:

 

image

I loaded that list in PowerShell and with a very rude dictionary check script borrowed from the web I listed  the words that were unknown nor anywhere in the files. With that I could now create a SQL Full text custom stop word list based on the already existing system stoplist:

CREATE FULLTEXT STOPLIST [HighLightsStopList]
FROM SYSTEM STOPLIST
AUTHORIZATION [dbo];

Using the PowerShell script to produce something like:

image

Configured the Fulltext index to use that stop list instead:

image

image

Run the query again, and as expected those noise words were now removed from my semantic queries:

image

Loading into MySemanticSearch codeplex sample

Time to load that FileTable into MySemanticSearch! Made a few visual adjustments, and a few others  so that it could work on my dev laptop (some strange problem with joining stream_id and document_key, I used path_locator instead of stream_id).

I opted for building MySemanticSearch from the source code, using VS 2010, so that took me some additional time to set up.

But finally all running! :) Connect to my sql 2012 instance:

image

It scans for available FileTables and finds MyKindleHighlights immediately:

image

Some interesting insights (remind you that these are from my biased highlights only, a very small subset of the complete books):

image

image

 

image

 

Final thougths

There seem to be some shortcomings (seems to handle unigrams only and does treats plurals/singular as completely different keywords, ex cat and cats… ). From a “semantic” search point of view I would expect a little better. May be I’m missing something, as this was a very initial test.

Nevertheless,  as an easily configurable and completely automated unstructured text mining tool now available in SQL Server, it is a very valuable addition to SQL Server.

Reference:

 

Rui

Fixing Amazon Kindle Daily Review page usability on touch enabled tablets – a case for “url site scripting” ;)

In a previous post (see Kindle eBooks as a great Learning/Self Development platform) I’ve shown how to get the best from Amazon’s Kindle Highlights feature. This feature and all the amazon highlight personal store features are probably the main reasons for not buying physical books anymore, except for a very few cases.

Anyway, I was using the daily review page occasionally on my laptop. Then I got the chance to start using a tablet to review my notes, and the page suddenly appeared much more interesting and useful.

Useful indeed, but not usable! as the small preview/next buttons are only at the top, and touching the flashcard or the page don’t jump to the next page as it should…

image

Waiting for amazon to fix this… well, good luck! So, I was pleased to see that there were actually a pretty workable solution: just fixing the code inline, injecting an additional click handler directly! Amazon surely is using jquery right? Right indeed, they are!

How do you inject custom javascript behavior on a site? Well, easily through the address bar, just prefix your code with javascript: and you’re done! or off course you can try to spot a cross site scripting  flaw on amazon site… but  I doubt it would be an easy task. :)

So, go to the daily review page and just paste the following code in the address bar:

javascript:$(“.reviewContentWrapper”).click(function(e) {$(“.nextReviewArrow”).click();});

Nothing happens right? Well, just click the flashcard, your next flashcard should load automatically! Much better now! My fingers can finally rest from that “humongous” effort. :)

image

Sure it works on iPad, just add a bookmark with the link. Every time you open /reopen the daily review page just select the bookmark. You won’t go anywhere, but the page will become far easier to use. As long you don’t close the page it will keep working, even when you choose to review another book.

image

Not a permanent fix but a very usable one from my recent experience! ;)

 

Have fun!

Rui

How to save 3000$ in ebooks :) ? Don’t have a kindle yet? Well…you should get one (…not necessarily buy one :) )

Yes, I bet the title caught your attention right on. :) Well, anyway I thought today was a good time to recap that you can get all sorts of interesting ebooks from amazon, for free. Yes, I would also say that an actual Kindle device is a great tool IMO, but you have also the kindle free apps from amazon: ipad, iphone, cloud reader, pc, android… You just need and amazon account to get started.

btw, Please be advised that I am an enthusiastic amazon fan & customer. I do have a kindle keyboard, And, Yes, I regularly buy ebooks!

I actually find it difficult to have a good reason for not starting your own digital library right now…

Currently there is some kind of market place visibility war going on… authors & publishers need better rankings, visibility, one of the fastest ways to do that is to promo the books regularly… so regularly we monitor (thanks powershell, iftt & alerbox). :)

A few days ago I finally set up some powershell & ie automation to be able to compare all my orders (free and not free ones), with current prices and some other book information.

The results are interesting… (even taking into account that several of these books are clearly over priced… ) Got the results into Excel, and I was pleased to see, that at least, my book addiction didn’t reach my wallet! :)

Another side note, I usually follow only two/Three kindle categories, so this is just a small glimpse of the free content regularly available.

image

Hoping that I didn’t mess anything up (and always remembering that data, like perception, is not reality!), but I double checked several items. Bet it’s at least approximate.

Off course Excel is a great tool for the stats but I couldn’t resist to get this data on a small tool I’m using for quick dataset analysis & visualization (based on ms pivotviewer). Maybe we can get this published through SmartPivot, how knows? :)

So, Right click my scrapped CSV where I collected order prices, current prices, stars, reviews, & book covers…

 

image

And we get a much more expressive & interactive view of my digital library:

image

Slice & dice, or kind of  :)

image

Zoom it!

image

Detail it:

image

Unfortunately time to read is much harder to get these days, so most of this content still waits on my beloved kindle. But I can also say that I’ve read amazing books (at the time free), just a glimpse:

Critical Thinking Strategies for Success (Collection)

Do the Right Thing

Wired to Care: How Companies Prosper When They Create Widespread Empathy

image

 

Final note, crossing my fingers that this won’t be the post that shuts the free content down! :)

By the way, this week pick. Bet it will last to next Monday only. ;)

image

 

 

Now, go grab some reading! :)

 

Rui

Kindle eBooks as a great Learning/Self Development platform

Almost a year now since I got my first Kindle. :) Being a somehow frenetic reader way before ebooks, and although I still like to read “regular” books, I must confess that when it comes to learning&self development, ebooks & kindle are hard to beat!

Why is that? Well… lots of reasons, but for now I’ll focus on the  highlights & notes feature. It’s amazing as it allows you to really extract so much more value from ebooks & reading in general. Curious thing, I had never been an “highlighter”… and always left my books in a pure & “pristine” state.

So for the basics, you highlight & add notes on any kindle reader (I prefer the real Kindle to do this , although I sometimes use the free mobile reader, as I’m always switching between them both).

You can then return to the ebook, on any reader (mobile, cloud,etc) and see all the hightlights & notes you’ve made. It all syncs to your amazon kindle profile, so you also have a great backup.

 

photo (13)

Kindle Mobile Reader – Book Highlights

 

photo (8)

Kindle Cloud Reader – Book Highlights

image

 

You can also review all your notes & highlights directly on the Kindle device, selecting  the My Clippings book (you can see it like a dynamic ebook on your device, collecting all you highlights & notes).

photo3

My Clippings on Kindle Device

There’s also a .txt file available with the content if you connect the kindle to the desktop with usb.

But there’s a minor catch here, the “My Clippings file only saves the highlights you do on that specific kindle device. If you highlight on the android/iphone the notes won’t make it to the file. That can be annoying, specially when you switch kindle devices.

Anyway, there’s much more to that. All your highlights are also available online at your kindle profile page here. Like this:

imageKindle Home Page

From there you can go to any book and recover your highlights&notes, and also share them publicly.

 

image

 

In one page you can see all you highlights & notes. A great starting point for building a quick personal  ebook. ;)

image

 

with a little copy & paste, and minor editing you can get a pretty good ebook with all you preferred content, like this one wich contais all my notes, ever!:

 

 

photo

I even created a table of contents with one chapter for each book I’ve read. For periodic & quick recaps that’s amazing.

 

Amazon also got it right that we often buy books for learning & changing, not just reading, and there’s a specific feature that helps you to review & remember  your books highlights, its called the daily review. It will cycle trough all your finished books, periodically, and show all your highlights, one at a time.

image

The daily review

You can also check other books highlights & posts… (Ferriss “on fire”, as always! :) )

 

image

image

And follow some interesting fellows :)

image

 

BTW, don’t miss this week free books. Just create your account and buy them “free” . :) Then, read them on free cloud/mobile readers. You don’t need to have a Kindle!

clip_image001

clip_image001[5]


So, that’s it for now, keep reading, and learning! :)

 

Rui