Tag: linkedin

Categories
Business Intelligence Geeky/Programming

Microsoft Silverlight PivotViewer: Getting Started and Business Case

I have been reading about Microsoft’s PivotViewer lately, and decided to try to get it going for myself. What is PivotViewer? Think of it as visual data slicing through a web page.

What you do is take some data, and then tie records to images, and then publish out your “collection” and you can consume it via a webpage using the Silverlight PivotViewer control. One awesome example of this is here http://netflixpivot.cloudapp.net/. But what I have been really trying to wrap my head around is how to use the as a “business” tool. Because, it is easily technically doable, but you have to have a *reason* to do it.

Working with widgets and customers and locations – what do you do? There are two things I could think of quickly. One – peruse your “master data” very fast and visually. The other is looking at some kind of metrics for your widgets, or logos of your customers you might sell too, or ? ..Well, you could..

  • Master Data/Catalog

    • Show pictures of your widgets, and create filters (they call them facets) for things like size, color, weight, model, etc. You have “one” of each and you just want to see what you offer. Almost could be a pretty sweet online catalog browser

  • Sales/Metrics

    • Do the same as a master data catalog but allow filtering by some kind of metric. Shipped items over a given time or something.

  • Something Else I Haven’t Thought Of?

Anyways, the first thing you should do before anything is get some kind of data feed. Run a query, get some data from somewhere. Start small to test. 500-1000 records.

Then the fun begins. Starting from absolute scratch..

Ok, yeah, tons of setup. Biggest thing is in IIS you need to set some MIME types: .cxml, .dzi and .dzc need to be “text/xml”

Once you have all that setup, you can do 2 things.. create your collection, and create your app. Create a blank silverlight project first:

Once you have that, there isn’t a ton you have to do to get things going with PivotViewer.

  1. MainPage.xaml

    2. Add in your MainPage.xaml, a namespace line for Pivot, and add the control

    Your end MainPage.xaml should look like this:
  2. Reference Assemblies

    3. For good measure, just reference them all, located here: C:Program FilesMicrosoft SDKsSilverlightv4.0PivotViewerJun10Bin

  3. Load Collection:
    4. pvWidgets.LoadCollection("http://localhost/SilverlightApplication1.Web/MyWidgets.cxml", null);

Note, you have to make your web part of your project IIS based instead of the build in web browser. Why? Because the .cxml HAS to be hosted on a web server, it just works that way.

Now, you need to create your collection.. you can use a cmd line tool they offer on the PivotViewer site, they also have a c# library for automating things, but it is best to first just do it manually. So I used the tool they have as an add on for excel. It adds a nice little “Pivot Collections” tab

You can use this to put some data in, you probably want to add more columns than what they give you by default. For my test I just used the same image for all records to get started. I have a feeling that the biggest barrier to entry to corporate BI teams and developers is going to be the imagery. You usually don’t have someone on your BI Team that knows how to use photoshop well and do all the high res imagery, so you are handcuffed there.

For testing sake, I Published my collection to the root of my website, with the name “MyWidgets”.

I loaded up my webpage, and I can slice and dice by all my columns I had in my collection, visually.. pretty dang awesome. (Note, I just made some fake data based on attributes I am used to seeing and with an image of a bike to see what it would look like – the goal was figuring out how would this work in conjunction with current BI offerings (cubes/Pivot Tables, SSRS, PowerPivot, etc))

Now, if you think where you could take this. Each “image” is clickable and brings up the image right in front of you. You could have all the specs of that widget there, and a link to “buy”, or deeper analytics for that widget.

Some other things I found out.. using the Excel tool for Pivot Collections is dog slow. Especially with a ton of records. It has to process the images for the “deep zoom” technology and it just takes a while. Like, hours.

There are tons of possibilities here with PivotViewer, both for an external website and also internal corporate business intelligence. It will give people another way to delve into the data and turn it into information.

Categories
Geeky/Programming

Application Shortcuts in Google Chrome – Awesome!

I use Google Chrome as my primary browser in Windows (it is blazingly fast and minimal). (I use Safari on my Mac’s) .. one awesome thing I have started to utilize is Application Shortcuts in Windows 7 and pinning them to the taskbar..

Which are apps and which are just browser app shortcuts? Pretty sweet. I set up Google Apps (Gmail), Reader and Unfuddle as my shortcuts and I love the one window no hassle that comes along with it, allows you to focus on the app instead of the browser/tabs. Pretty nice feature.

Categories
Geeky/Programming

VMWare Converter Windows 7 P2V and Lost Ethernet Connections

Wow, so tonight, I took a laptop that was having some screen issues. It was a Dell Inspiron, and the screen would go black randomly (not completely, you could baaarely see the screen). Tested power settings, etc, etc. Only thing I can think of is a short somewhere. You close the lid, open it, and it works. Well since that is a pain, I decided to take VMWare’s free converter (http://www.vmware.com/products/converter/) and do a Physical to Virtual (P2V) image, and then run it on a beefy dev box I have with 8 GB’s of RAM.

Started the P2V after registering and installing the converter, everything was going good. I noticed that the converter said it didn’t support Windows 7, but ok, I gave it a go. As it was converting (I wrote the VM image out to a USB drive), the screen went black, so I had to do about 50% of the conversion barely being able to see the screen, but it worked.

After the P2V conversion, I took the USB drive with the image, hooked it up to my beefy machine and copied the image over. Fired up VM Workstation 6.5 and started up the VM. Worked. Noticed though that the OS was set to “other” and the VMware tools wouldn’t install. Powered down the VM. Changed to “Windows Vista” since VM Workstation 6.5 doesn’t support Windows 7 either. Started it up again. Cool, installed the VMware Tools. Changed resolution. Everything cool… except… no network! Both ethernet devices (lan and Wifi) were showing up as no drivers. WTF?!?

Downloaded drivers from Dell, installed, wouldn’t find them yet even after installing. Thought maybe I needed VMWorksation 7.1, so upgraded and still nothing. Uninstalled devices, changed numerous settings, still nothing. Plugged in a USB wifi, and that worked, but that was just a test.

Digging around mutliple forums and what not, found some things about editing the .vmx file with notepad and adding the line

ethernet0.virtualDev = “e1000”

So I tried that, and it worked! From what I can gather, since the converter doesn’t support Windows 7, it sets the OS to other, and then doesn’t grab the Ethernet settings correctly. Changing from other to Windows7 (in VM Workstation 7.1) you can install the VMWare tools. Adding that line to your .vmx file (and ethernet1 if you have multiple adapters) should do the trick.

Categories
Business Intelligence Geeky/Programming SQLServerPedia Syndication

Exporting Data from SQL Server to Excel Directly

Had a request to export data out from SQL Server to Excel directly. There are a few ways you can do this. BCP is one, another is OPENROWSET. SSIS, etc.  Here is the OPENROWSET method, using Jet (I think this only works on 32bit servers as well)

First you need to enable a setting on SQL, so.. Start->Programs->Microsoft SQL Server 2005->Configuration Tools->SQL Server Surface Area Configuration, then click on the “Surface Area Configuration for Features” at the bottom.

image
Under database engine, make sure “Enable OPENROWSET and OPENDATASOURCE support” is checked under Ad Hoc Remote Queries option.

image

Then, you need to create an empty excel workbook, with the columns of the query you want to export out. Create one on C:Testing.xls with “Name” and “Rating” columns

Then run this query:

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:testing.xls;',
'SELECT Name, Date FROM [Sheet1$]')
SELECT 'Steve','1'
Union
SELECT 'Joel','2'
GO

And if everything worked, your xls should have 2 rows in it.

 

Now, if you want output to other spreadsheets, you could dynamically set the filename in the statement, or other trickery!

Categories
Business Intelligence SQLServerPedia Syndication

Where does PowerPivot Fit?

Now that SQL Server 2008 R2 is out, and Excel 2010 is out. You can get PowerPivot (http://powerpivot.com/) and create your own in memory cubes!

…. Or something like that.

I still haven’t figure out where PowerPivot fits in a business scenario. Why?

Well first you have what are now being called the “old school” BI users, that use Excel to connect to an SSAS cube and create fancy pivots and reports, maybe convert to formulas and create some nice reports/dashboards.. analytics.

Then you have people who only consume canned/standardized reports, through SSRS mostly, or maybe Excel Web Services… but they don’t create. Just consume.

You might even have power users, who take Report Builder and create those SSRS reports for other users. Awesome.

But then, you have this tool, PowerPivot. What can you do? Hit databases (mostly… cubes and other sources as well), bring back data, relate it, and create pivot reports/graphs off of it.

But you better be pretty dang advanced as a business user to use PowerPivot. I could count on one hand the users (that I have dealt with over the last 10 years) I would feel comfortable giving it to and not ending up with more of a headache.

What do I think is still missing from the Microsoft BI toolset? Looking at Business Objects, the Web Intelligence. Universes. You create a Universe off of a datasource and expose it out to the user, they can create reports/ad-hoc whatever off of it.

Kind of like the ever elusive “Report Model” in the Microsoft stack that no one ever uses, ever will use, or has no reason to use. But in BO, they make it useful.

I don’t see PowerPivot taking the place of a Report Model/Universe, so where does it fit? IT Analysts making “pre” cubes before you actually make cubes for your users that just want to hit it with Excel and not care about anything else?

Or people who just want to create their own cubes in silos. Tell me how that lends itself to “one version of the truth”?

Either way, we will see how it evolves and hopefully find some good use for it. 🙂

Categories
Business Intelligence Geeky/Programming SQLServerPedia Syndication

SQL Schema Source Control (CodePlex)

Source Control. In my eyes, one of the best inventions of development.

Software Developers have used it for years, and it allows them to easily develop in a team environment, and be less scared they will lose a change or not be able to see things they did historically.

I have blogged a few times about source control before..

SQL Server Schema Automatic Revision History using DDL Triggers and SVN
MSFT BI In a Team Environment
Visual Source Safe Sucks/
Source Control at Home with Subversion

But this was mostly for code. Developers. The SQL Community has kind of been shafted with source control. Yea you can tie in VSS to Management studio, and others. I have never found one that works, and just wanted something to work in the background.

I originally started doing this with DDL triggers as my <a href="“>post in November kind of outlined. It worked, but wasn’t reliable enough for what I wanted and was too much setup.

So I did what developers usually do, I wrote an app. SQL Schema Source Control http://sqlschemasourcectrl.codeplex.com/

At first it worked with one database, one server, everything was hardcoded. And then it progressed, and now it is all configurable for multi server/multi database, etc. I decided to put it up on CodePlex because I think it can be improved and made to work with other source control providers, like TFS.

The code itself isn’t anything crazy, some file operations and SMO operations to get the DDL and then some functions to add/update/delete and commit to source control.

For info on how to get it working, check out the documentation page on CodePlex, I can also answer any questions here, or on CodePlex.

I have been using the app for a few months now and it has saved headaches, accidental deletes, wanting to see changes over time by developers, etc.

The feature I like the most is that it logs the SQL Agent job changes, so if someone changes a job, you can see the history…

Now, there are competitiros out there. Redgate (http://www.red-gate.com/products/solutions_for_sql/database_version_control.htm) and others. But I wanted something free and open, so that is why I am putting this out there. I would be perfectly fine not putting anything out and just using it, but I think (and hope) others could benefit from using this app

So if you are looking for semi-easy way to get revision control on your SQL Schemas and SQL Agent jobs, check out the app. There is minimal setup, but once you have it working, it just runs.

Note at this time it works with SVN and SQL 2008. Also the solutions is VS2010. I originally had it working with 2005, but no need on my end anymore for that, someone could easily make a version for SQL 2005. VS2008 solution could be created pretty easily as well. Have fun!

Categories
Business Intelligence Product Reviews

Redgate SQL Search – Free Download

I have always wanted a good way to search all the db’s on a server, find procs, views, whatever that have something in their DDL so i could do what I want with it. There are ways using DMV’s or other things in SQL Server, but it just becomes a pain.

Then came along SQL Search (http://www.red-gate.com/products/SQL_Search/index.htm) – from Redgate. And it is free! Works like a charm. It eliminates the need to fire up some query and change what you are looking for. It works well. Check it out the next time you are trying to find every object that has “whatever” in its T-SQL DDL.

Categories
Product Reviews

Reeder, a Google Reader App for iPhone

I have tried many iPhone Google Reader apps, and nothing has come close to working well for me but the web app as a web clip shortcut. That changed this week when I checked out Reeder (http://reederapp.com/2/)

I read a ton of Google Reader (usually 10,000+ posts a month) so I need something good. Reeder has actually replaced my Web Clip Reader link on my second iPhone page as my go to Google Reader app.

If you read a lot of feeds, from Google Reader, I suggest checking out Reeder.

Categories
Business Intelligence Geeky/Programming SQLServerPedia Syndication

SQL Server 2008 – Intellisense – Update Local Cache

Since we recently upgraded to SQL 2008, now I have some small tidbits that I can share! First one is this: Intellisense updating.

In SQL 2008, it has built in Intellisense, pretty awesome. Until you add new objects, then everything is red underlined. What to do?

Well if you are writing some T-SQL in SQL Server Management Studio (SSMS) and run into this issue for newly created objects, just use this command

CTRL+SHIFT+R

and you are all set, the local Intellisense cache will be rebuilt and your new tables/procs/whatever will show up in Intellisense, cool!

Categories
Business Intelligence Work

Agile in Business Intelligence? Of Course!

About 3.5 years ago I was introduced to Agile at the Agile 2006 Conference. After that, and implementing it in a software dev environment, I found that it just works. Sprints, Scrums, Stories, Backlog, Velocity, all the pieces fit and work.

Now that I am managing a Business Intelligence group, which when I started wasn’t doing *anything* as far as a method, I had to ask myself if doing Agile would work (I knew it could, but it is different than software dev in many ways, similar in others).

Back in October, my group went Agile. We set up a board, got some index cards, and just started Agile. A big paradigm shift at work for IT, but we needed to do something.

With Business Intelligence, we really don’t have *code* to work on, but more “objects” (Cubes, Dimensions, Reports, etc). As a team we needed to figure out – what is a story? What is a feature/enhancement/task. What is an epic? How are we going to score things, etc.

The first few sprints (2 week sprints – Wednesday’s to Tuesdays) our velocity was lower and/or we just scored things a little weird. But since then we have learned our “zone” of scoring stories and we got into a groove of releasing our BIG cube every 2 weeks, and releasing the smaller changes when completed.

We do the daily *scrum* for 15 minutes, and track burndown on stories, which lets us make some cool burndown charts that we tack up on our board, and we have some other cool bullet charts to track velocity by sprint, to our original, and final goal, and more.

What has Agile brought our group? Confidence, Stability, Ability to Meet Expectations. Agility. Results. and more..

Do we run into issues yet? Of course. Can we adjust and handle them. You bet! Are we continuously learning and changing our process to make it better? Yep. Always room for improvements.

What else does Agile bring us? Visibility to our customers, and to our peers in IT. Eventually the “BI” stuff should just run, over and over, iteratively.

Trek BI Agile Story Board

Agile isn’t a silver bullet though. It isn’t easy. You still need to work to keep things organized and on track. You have to fight that organizational gravity that sucks teams back in and people in as well, and throws that scope creep back onto stories and projects. You also have to fight to get rid of your technical debt, which depending on how long things have been running before you started even thinking about Agile, might take you a while.

This post is more of a high level “Yes We Can” type post about Agile in BI. I haven’t decided yet, but my guess is I might have some more detailed posts on how I like to run an Agile project, and what we are doing as a team to handle situations that come up, and just how we do things.

In the end though, just remember, have fun!