Tag Archives: linkedin

Business Intelligence – 3 Years of Agile

Last year at the PASS summit, I ran into someone and was discussing project management and Business Intelligence. They were so adamant that agile couldn’t work. And I had to correct them, as I have been doing agile now for three years at Trek in our BI group.

Yes, some things don’t exactly parallel to software development, but many things do. Sprints, Standups, estimations, stories, points, scrum master, releasing/delivering value on your iterations. And now even more with process like unit testing of SQL code and more, things are getting closer to software development in that regard.

I have an entire blog category dedicated to agile – more concepts but also talking about Business Intelligence teams in some of them.

Just remember, you can do BI and Agile, it works, and you can deliver a ton of value to the organization. Someone who might argue with you, well, they don’t know what they are doing in BI or in Agile, or the organization isn’t willing to change, which of course then it wouldn’t work.

Advice. Make sure you hold retrospectives, and make sure you make adjustments from whatever comes out of them.

Tools You Should Use: Tasklist

Day to day we all use many different systems and tools. Windows, Mac, iOS, Android, Twitter, Facebook, Powershell, Visual Studio, Sublime Text, iTunes, and the list goes on and on. There are so many cool utilities and tools out there, and many people aren’t aware of the majority of them, but at one point or another, they could benefit from using them..

The first tool/utility I’d like to tell you about is: tasklist (http://technet.microsoft.com/en-us/library/bb491010.aspx)

tasklist is a cmd line utility built into windows that you can use to see running processes. With any utility, run “tasklist /?” to see options. You can
run it with /S and pass in a remote system name, pretty cool. Also authenticated using /U and /P. You can see many other options as well. Format, Verbose, etc.

You can use this to get the running processes on a remote system. Want an easy way to see who might be remoted (RDP) into a machine?

tasklist /s COMPUTERNAME /v /FI "ImageName eq Explorer.exe"

Pretty cool huh? Just think of the other possibilities. Log the running tasks to a csv and track over time with memory usage. Check remotely for a process running. tasklist should be able to help. And used in conjunction with other tools, you can do a lot of remote admin work.

I’m hoping to highlight on more utilities and turn those posts into a series, so stay tuned.

Reset your “Lost” Password in Ubuntu

I have a few VM’s setup at home. One is Ubuntu. I haven’t been on it for a while. Figured I knew what the password was when I set it up months ago, but for some reason it wasn’t work. So how to change?

1. Reboot into “recovery mode”

Reboot, hold down shift and the boot menu will come up, choose the option with (recovery) at the end.

2. Mount as root

Once through that, choose the option to mount as root.

3. Reset Password

#passwd username (username is the username you want to reset)..

enter the password twice, and then

#reboot now

Bonus: If you get “Authentication token manipulation error” as I did, you need to remount your file system as read/write at the root prompt.

#mount -rw -o remount /

Bonus 2: Once you actually get into the system, it is going to probably complain about your keyrings password not matching. Goto your home folder (make sure you have all folders shown) and goto .gnome2/keyrings and delete and files and reboot.

Overall not that bad, but yeah, don’t lose your passwords. Keep them in a password manager! (I didn’t with this one of course, didn’t keep it in my head either!)

#msteched TechEd 2012 Conference Highlights

Last week I was in Orlando at Microsoft TechEd 2012. It was a great conference, especially for me, since I am all over the place with technology. There was DBA, SysAdmin, Developer, etc sessions.

What did I take away? Here is a quick overview.

Azure. The cloud. It is here (well it already was) but now with IaaS and persistent VMs, the game is changed for Azure. Also with websites and media streaming and other stuff Azure is becoming *the* platform.

SQL 2012 - While I was away, the BI team upgraded to 2012 at work. While I have been playing with 2012 for a while, it just got real. BISM stuff and SharePoint/Kerberos stuff came to light this week. Good stuff

System Center while I didn’t focus much on this, System Center is where it is at. You can now monitor your Azure cloud, your on-prem VM (HyperV or VMWare) and do tons of other stuff. Sky is the limit with this product.

There is so much more but it was a great time. Good people, good sessions, smart presenters, and just information oozing out of every room. Orlando in early June is like a sauna though :)

I am really pumped to get back in the saddle and start implementing some of this stuff in day to day solutions. Was so excited I actually moved my blog to an Azure website this weekend. It moved fine and I had to do some MySQL stuff to get my content moved, but it is basically sitting there idle as I can’t change the DNS over without pay more than I am willing to. More to come in this arena though.

To the cloud! (and the Tabular cube, and the System Center, and so on and so forth)…

Adding Computer to Windows Domain and Logging In with Domain Account over VPN

I have done this enough times, but just for documentation sake.

So, you get a cool new OS (Windows 8 Release Preview maybe?) and you repave your machine. You want to add to your domain over VPN and then login with your domain creds. Now, you must have an account that has access to add computers to the domain, of course, but this is what you do.

Why would you want to do this? Well, if you are telecommuter, it might be something you run into. If you want to redo your machine at night and not waste time during the workday, is another reason. If anything, it isn’t that complicated, but it could be confusing if you have never done it before. If you have a basic understanding of windows networking and Active Directory you should be able to get what is going on here.

1. Repave your machine!
2. Set up machine with new name
3. Install VPN, connect to your network.
4. Add your machine to your domain as you would when on the LAN network.
5. DO NOT REBOOT. I repeat, DO NOT REBOOT. Even though Windows wants you to!
6. Go to user management and add the domain user you want to login as to the local admin group.
7. Now, feel free to reboot.
8. Drink a beer.
9. When the login screen comes up, login as your local machine account that you setup when you repaved your machine.
10. Connect to VPN.
11. “Switch Account” back to login screen (VPN is still connected!)
12. Login as the domain account from step #6.
13. Profit.

Dynamic Sorting Using Parameters in SSRS

The other day, someone requested that a report in SSRS be sorted differently by default. While that might make sense if everyone wants it that way, more than likely you might have people that want a report sorted differently by default. How to do it?

There are probably a few ways, but this is what I did.

First, I added two parameters. “SortByDefault” and “SortOrder”

The “SortByDefault” will be a drop down of your columns you want to sort by for your dataset (or group, or table/tablix)

The “SortOrder” is simply Asc (1 to N, A to Z) and Desc (N to 1, Z to A)

Now, here is how mine look:

SortByDefault (I have two columns I want to allow sorting by, PointsLeft and StackRank):

SortOrder:

Now comes the fun stuff: Making it work.

Make sure you remove any “ORDER BY” in your dataset (you don’t have to but this makes it easier).

I also have every column in the report set up for interactive sorting based on the column header/column it shows, but not sure that is necessary here, I just wanted to put that out there just in case

You want to get to your sorting options. So in my case, I have a tablix, so get to your tablix property window and the sorting option:

Now you can see, my “Sort By” and “Then By” are expressions. It is kind of weird here. Also you can’t set expressions for “Asc” or “Desc” so what I had to do was trick it somewhat.

the first is to handle the asc option:

=IIF(Parameters!SortOrder.Value=”Asc”,Fields(Parameters!SortByDefault.Value).Value,0)

the second is to handle the desc option

=IIF(Parameters!SortOrder.Value=”Desc”,Fields(Parameters!SortByDefault.Value).Value,0)

You can see, some magic. If the order by is XYZ then use the field, otherwise 0. If you notice from the screenshot, first one is A to Z (Asc) and the second one is Z to A (Desc). So we are basically telling SSRS to sort by the param or not based on the order by option and it chooses the right order by (ASC/DESC). I think this was easier in SQL 2000 SSRS :)

Well now you should be able to test your report and try to sort orders. What I did next is make my params hidden. The defaults are what I wanted for the existing report (Order By PointsLeft DESC), and what I did next is create a linked report and set the hidden parameters int he report options in Report Manager to (Order By Stack Rack, ASC)

Now I have one report, with hidden sorting params and I can create linked reports with different sort options without having to create a new report. I could add all columns to the choices, or even let users choose as parameters (but they already have interactive sorting in this case).

Happy Report Buildin’!

SQL Server 2012: Data Quality Services

With the release of SQL Server 2012, I am looking more into Master Data Services (MDS) and Data Quality Services (DQS). A brief overview of DQS.

You install DQS with SQL, and you have to configure it. The server configuration is a cmd line process that runs to create some databases on your server (DQS_MAIN, DQS_PROJECTS, DQS_STAGING_DATA).

I ran into one issue with the running of the configuration, not sure if this happens everywhere, I am running Windows 8, but nonetheless, I ran into. After running the tool and getting error after error, and trying as admin, etc. I dug deeper into the error message and found that I there were some security/permission issues I had to resolve. It ended up being that I had to change permissions on

C:WindowsMicrosoft.NETFramework64v4.0.30319Configmachine.config

to allow write access. Once I did that, the configuration tool worked and I could get into DQS.

DQS gives you a “Data Quality Services Client” to work with. When you open it, connect to the database where you configured the three databases I talk about above. Once you, you have 3 panes.

You have Knowledge Bases, Data Quality Projects, and Administration.

Knowledge Bases: datasets of known data that you can use in your Data Quality Projects. You get a default Knowledge Base – state names and some other data similar to that.

Data Quality Projects: Here is where the magic happens. You can choose some source data (Excel xls – xlsx wouldn’t work or SQL table) and then apply your knowledge base on it. Then you can reimport your data at the end back into SQL or export it, and update your Knowledge Base with learned values.

Administration: Not a ton of options, but you can set some thresholds, and also setup your Azure data market settings.

Azure Data Market https://datamarket.azure.com/browse/Data?Category=dqs – Lots of data you can use to combine with your Knowledge Bases. Much more here and I won’t go into detail – it could be its own post in itself.

As a test, I took an excel file, added a few records with columns first, last, city, state (I actually imported into a staging SQL table to work with it) – But in the state field I put different variations of state, WI, Wis, Wisconsin, MN, Minn, Minn., etc.

I then ran the file through creating a new data quality project and ran it against the default Knowledge Base, and it corrected the values it could. Got a weird error clicking next on the project, it seems the button is touchy. Hopefully they come out with a fix soon.

Once you build up and get your Knowledge Base stable, you can use from SSIS packages or in Master Data Services. I see many useful applications for DQS. Either around your corporate data or pulling in data from Azure data market to cleanse existing data you might have (think: looking up gender from first/last name).

This post is a brief look at DQS and how it works, but there is so much more. I hope to get more in depth in the near future.