Categories
Geeky/Programming

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.

Categories
Business Intelligence Geeky/Programming SQLServerPedia Syndication

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’!

Categories
Business Intelligence SQLServerPedia Syndication

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.

Categories
Geeky/Programming

Using Windows 8 Consumer Preview on an Active Directory Corporate Domain

I have been using Windows 8 for a while now, here and there, the developer preview, on the tablet I got at build. Pretty cool, rough around the edges. I was really waiting for the next iteration.

Last week the Windows 8 Consumer Preview was released. My computer at work was getting “crufty” so I decided to install Windows 8 on it fresh and see what I could see.

Well, first off, if you run Windows 8, you can associate your login account with a Windows Live Id Microsoft account. This is pretty cool for a couple of reasons. Most services on the OS then automatically know who you are, and second, your settings and Windows Store apps will sync between devices.

But what happens on domain? I was as curious as I could be as the info out there is/was sparse as of now from what I could see. So I set up the machine fresh on Windows 8. Logged in as my Microsoft account and got into the OS. Once on the corp network, got it on the domain, as normal, and then I could log in with my domain account. Great.

But then what? Do I lose all the cool Microsoft account stuff? Well, it depends. What you can do is go to the new “Settings” in Metro land on the machine and find the account stuff, you can then associate your Microsoft account with your Domain account. This is pretty cool. Then your settings on your domain machines (if you have multiple) will sync, but you still get the domain stuff of the corporate network. I have two Microsoft accounts anyways (One for personal, one for work – MSDN, etc, etc).

There is one article out there that tells admins how to restrict the Windows Store on domain, or at least they will be able to, so I am guessing most things will be configurable or restrictable by domain admins and group policy, but at least there is some cohesiveness between Microsoft account and Domain account now. Good stuff. More to come on my Windows 8 Experience, as I am and will be putting it through its paces.

Categories
Business Intelligence Geeky/Programming

Yamanalysis: Analyzing Yammer and Using PowerPivot on MySQL

I have blogged before about we use Yammer. Some interesting data can be gleaned from the usage of Yammer. One thing though is that the data and usage stats are limited in the Yammer area, but you can get all the data and take a look at things. I ran into Yamanalysis and decided to give it a try.

After getting Ruby, Rails, MySQL, curl/curb, GraphViz, IBM WordCloud and whatever else configured, I finally got it working. (FYI – MySQL 5.0 – you need to run the config wizard as administrator on Windows 7 or it just hangs at the end).

Pretty cool data and analysis from a higher level. Of course after getting everything working, I wanted to hit the data with PowerPivot. This sounds like an easy feat, but yet seemed to be a complicated task.

I first got the ODBC connector 5.1 for MySQL (Since PowerPivot doesn’t natively connect to MySQL,and 5.1 since that is the only one I could find reliably and get to work.), and set up an ODBC source. Tests fine.

In PowerPivot, I would run through the wizard and it would get architecture mismatches, and catastrophic failures, trying to test the connection. Ignoring that and moving forward, running a query would just hang on import forever. I tried different DSN’s, User/System DSNs, etc, to no avail.

What I ended up doing was firing up my local Microsoft SQL instance, and creating a linked server through a system DSN to the MySQL instance, then I could query the data fine from SQL. I opened up PowerPivot, connected to SQL local and then ran the query to MySQL and it work. What a workaround, what a hack, but at least I can hit the data in PowerPivot locally, which was my goal here.

Of course I could take what Yamanalysis is doing and dump to SQL, or do something similar in C# and dump to SQL, that might be a project for another day.

Categories
Geeky/Programming Product Reviews

Windows Phone 7 App Gap




I am pretty into tech and gadgets, all kinds, mobile too. I have an iPhone (I’ve had all since 2G), Android (had a few, Dell Streak, Atrix 4g, now a Samsung Galaxy 4G), and Windows Phone (Samsung Focus WP7). People ask me about the differences between apps and devices and OS’s. Big thing with WP7 (which the OS is great), is the “App Gap”. I went through my iPhone and looked at the important apps, minus games, I use, and if there are equivalents on WP7. I downloaded them, etc, but made a list of ones that aren’t there yet. (interesting enough, I took notes for this post on OneNote from my WP7 device)




Apps Missing

  • Cisco VPN (yes, it’s a service, but it’s not really there on WP7)
  • Disqus – has api http://disqus.com/api/docs/
  • Flipboard
  • Google Plus
  • Hipporemote
  • Intonow
  • LogMeIn
  • Mint
  • Oink
  • Pandora
  • Path
  • PayPal
  • Posterous – has api http://posterous.com/api
  • PS express
  • Skype
  • Sportacular

Apps There But Not Official or Website only

  • Instagram (instacam)
  • Untappd (website)
  • Pinterest (Pinspiration)
  • YouTube (it is native but MSFT made and mobile website wrapper)
  • Tumblr (not free 3rd party)
  • LinkedIn (3rd party app)
  • Google Voice (GoVoice, GV for Windows Phone, etc)
  • Digg (Digg7, Digg Reader, etc)
  • Dropbox (some readers, etc)
  • Google Authenticator (some 3rd party apps)
  • Google Latitude (some crappy 3rd party apps)
  • Google Reader/reeder (some crappy 3rd party apps)
  • RDP Client – some 3rd party clients
  • VNC Client – 3rd party clients
  • TFS Clients – couple 3rd party clients
  • Google Translate – some 3rd party apps, Microsoft Translator, etc

As you can see, pretty big list. Many of the 3rd party apps just don’t cut it. Even some of the apps that are there, they just don’t compare to iOS or Android. Microsoft needs to overcome this App Gap to gain and/or stay relevant, in my eyes.

Categories
Geeky/Programming Life

Cool Things I Have Been Doing On the Computer Lately

In the past couple of months, weeks, whatever (time flies) I have been doing some pretty cool things on the computer, in a wide range of areas. Just want to get them down on paper (you know what I mean)…

1. Yammer – working hard on growing Yammer community, external networks, just getting engagement and showing the benefits. It’s fun.

2. Kinect – did some Kinect hacking. On my own then with the group, got some cool stuff to show. It is crazy how easy it is to get something up and running with Kinect and the SDK. I see this stuff taking off in the coming months/years.

3. Azure – dorking around with Azure, looking at what it can and can’t do, what it could do well, how it would fit in with everything.

4. SQL 2012/Power View – been playing around with SQL 2012 since “Denali”, but now its got an official launch date (March 7th) and things are getting real. Power View demos online, trying to figure out how SQL 2012 is going to fit into our infrastructure and just learning as much as I can about it.

5. Ruby – been getting into Ruby and Ruby on Rails on my Mac, git, sqlite3, heroku, etc. Trying to learn more things that just the .NET ecosystem.

6. Ubuntu – same here, set up a VM, been trying to use it consistently, trying to get the other viewpoints from Windows and Mac and where things are at. Keep up with the joneses so to speak.

7. Android – I picked up a Samsung Galaxy (Verizon 4G) a few weeks ago and have been using it. I still love my iPhone, but getting more into Android. Ice Cream Sandwich (ICS) is a pretty good OS, there are still quirks, but its better. Verizon sucks around where I live btw.

8. Google+/Picasa Web Albums – been getting this into my photo workflow, for sharing and backup. Liking it so far.

9. SharePoint 2010/FAST – been researching and reading FAST server like crazy trying to see how it will fit in with a potential project. I think it could be amazing. more to come.

10. SMS – been playing around with different frameworks, and seeing how they compare, trying things out. Using Voice and SMS is all the rage these days. (Hall and Oates thing anyone?)

Bonus: Nothing with computers, but I have been really getting into brewing beer/homebrewing. I think we have made 5 batches now, and the ones I have tasted so far are really good. It is a fun hobby and breaks up the constant technology I am involved in. More to come here too.

And much much more. Time is limited, time to post is limited. Getting out there and doing cool things is fun, and sharing them is fun too. Gotta find the right balance. I hope everyone is having a cool 2012 so far.

Categories
Business Intelligence Geeky/Programming

Emergency Broadcast: Cursors in SQL are Bad.

Emergency Broadcast:

Last week, I stumbled across a “legacy” system. Small system, few tables, few stored procs, few web pages that let users manage it. The main table had about 40k rows in it, nothing huge. This system gets used sparingly.

Reports of the main page of the system taking 5+ minutes to load, or just timing out. Really? Dug through the code and lo and behold. A cursor.

Looping through 500+ items, and running 8 or so queries in each iteration. The queries were very similar:

SELECT some stuff INTO Cursor

WHILE LOOPING THROUGH CURSOR

SELECT STUFF 1

SELECT STUFF 2

..

..

END

I ended up re-writing the query to just grab all the data and did some case statement in the SELECT. They query went from 5+ minutes to less than 1 second.

Yes, CURSORs are bad. Avoid them. Rewrite your SQL. It amazes me that with all the good info out there on the web around SQL that people still write CURSORs in their queries. I asked the DBA to look at all procs on all systems and find all the CURSORs so we can root them out once and for all.

End of Emergency Broadcast

Categories
Geeky/Programming

Sites and Services Hacked: Use a Password Manager

With the recent hack on Zappos.com where usernames and “cryptographically encoded” (not encrypted?) passwords were taken, it’s time to take warning. Other sites have gotten hacked in the past years as well, and there are ones you don’t even know about. It is kind of scary.

I, like many have been guilty of using same passwords for different services, throwaway services, etc. My most important services I used different passwords, but no more. I have been using 1Password for a couple years to store passwords, but now I am taking it to the next level. I went through the majority of the services and changed password and created a randomly generated password in 1Password. Yes it is a pain in the butt to have to look up password or whatever, but I think at this point, the best password is one you don’t even know.

1Password syncs across iOS, Android, all browser extensions, Mac and Windows, so it suits me well. Even after a few days I am getting used to the new workflow with any passwords I might have to look up or auto login to. All I know is the security and authorization on the net has to improve, it shouldn’t be this tough.

Categories
Product Reviews

Waze – Social GPS Navigation

This last week I have been using Waze on my iPhone. What is Waze? Well, it is a free “social” GPS turn by turn app. Since iOS doesn’t have built in turn by turn, it is left up to the market to fill the gap. There are the big players out there TomTom, etc but then Waze comes along. I have heard/read about them for a while now, but never got around to trying it. Pretty cool. Nice interface, and it’s social.

How is it social? Well, people can report slowdowns, accidents, police, etc and it will show up on your map as you drive. You can see other wazer’s on the map as you drive. They also gamify it by giving you points for miles and little rewards when you hit milestones, pretty cool. Works for me. The product becomes better as more people use it, and I assume as your friends or groups you are in use it more.

Check it out at waze.com Looks like they have apps for more than iOS as well.