Mini rant. Too much to write in one tweet. To everyone out there who has an iPhone (and now iPad): Do you not realize that even if you go into your mail settings, and remove the signature that will add
“Sent from my iPhone”
that we still know you sent it from your iPhone (mostly). Example: Outlook. Default now in Outlook is Calibri size 11 and black/blue for to and reply. And then we know you have have an iPhone, and we know you aren’t on your laptop 24/7, and then we receive an email that has jacked up all the formatting in Outlook and you are sending it with Times New Roman (or whatever font it comes in as) and it looks just an email that would have “Sent from my iPhone” at the end, yet that isn’t there.
Who are you trying to fool? Do you think we don’t know you are on your phone? Why care about letting us know – there is nothing “wrong” with sending an email from your iPhone. It might even make us think, “hey, so and so is working from their phone – so I won’t attach a 20 MB PDF back, or maybe I will give them a quick shout back, etc.
To all of those out there that removed the signature… why? And if you have, I think you should put it back!
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.
Under database engine, make sure “Enable OPENROWSET and OPENDATASOURCE support” is checked under Ad Hoc Remote Queries option.
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!
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. 🙂
I am writing this from my iPad. What do I think of it? Pretty awesome. More apps will make it even better.
Playing “board” games is pretty fun. Crosswords, Sudoku, Words with Friends and others.
IMDB and Wikipanion are nice too. News and magazine apps make total sense. Of course this device is just completely built for consuming media but you can still create as I am now.
I like email in landscape mode, maps is awesome too.
Biggest thing i wish i could do? SMS. Other than that i am pretty pleased.
Next versions of the iPad will be better (as usual) so I am pretty excited.
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..
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.
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!
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.
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.
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.
Figured out today that in SSRS, if you add a filter to your dataset, and you want to use “Like”, that the operand for everything is * instead of % like you might be used to in SQL, sometimes it is just the little things 🙂
Steve Novoselac's Blog 