Web Analytics/Media Tagging on ASP.net AJAX Pages

Posted by & filed under Code, Development, Javascript, Work.

More often than not a site build is deployed with little focus on analytics or any kind of measurement in mind- or, if it was; it was built against a moving target which is now little help to the current needs for site-tagging to facilitate your marketing goals.

A common scenario I see regularly is a checkout process and/or registration with a form and confirmation all at the same page location (“Checkout.aspx” or “Registration.aspx”).  How are you supposed to get in later without going back to the site build in order to get your conversion tags on just one page or another? (Un?)Fortunately for you, it was built in ASP.net and no matter how poorly it may or may not have been done, there’s a good chance with minimal code you’ll be able to deploy your tags to the page-states they need to be.

Enough with the blah:

  • You have a tag that you can load arbitrary Javascript on the page
  • Can’t distinguish by page URL alone
  • Tag fires either once per process, or each step in the process.

Any ASP.net site I’ve seen since ASP.net 2.0 and later have the decently-rich ASP.net AJAX API loaded by default (I don’t think you need to be utilizing any actual ASP.net AJAX functionality, as the validators all use this code too).  There are many objects and methods within the Sys.Application javascript object, that ASP.net has so nicely exposed for the rest of us – however lesser known unless you’re doing some client-side hacking.

The two events/handlers/methods that will be most useful are the pageLoad, and pageInit.

Init: http://msdn.microsoft.com/en-us/library/bb397532.aspx
Load: http://msdn.microsoft.com/en-us/library/bb383829%28loband%29.aspx

There are two ways to utilize these client-side events:

Override with your own methods
If you are familiar with the ASP.net page lifecycle events, you’ll see a simliar structure. And you can bet the same or similar structure exists on the client-side as well. By creating functions with specific names that map to the ASP.net events, you can run specific code when each happens, and have access to the javascript representation of the event itself, and event sender.

    function pageLoad(sender, eventArgs){
        //code to be triggered client side
        //synonymous to ASP.net Page_Load
    }

    function pageInit(sender, eventArgs){
        //code to be triggered client side
        //synonymous to ASP.net Page_Init
    }

The unobtrusive way
The ASP.net AJAX javascript API provides nice methods to attach and detach handlers to their ASP.net page lifecycle events.

    function myInit(){
        //my pageInit code
    }

    //register handler
    Sys.Application.add_init(pageInit);

    //using anonymous functions
    Sys.Application.add_load(function(){
        // my pageLoad code
    });

In this solution below, I have a two-step process (form, confirmation) all within one full page load. A parital page post-back occurs updating the form to be the confirmation page, and only loads my tag that I’m loading the javascript through once. On the confirmation page there is a div that gets loaded with an ID of “distingushingID” into the mark up that does not exist on the registration form markup. In this scenario I’m either too late or can’t utilize Page_Load, so I have to rely on Page_Init which loads each postback, partial or not. I imagine you’ll need some trial and error to find the right event.

Sys.Application.add_init(function(){
    var newFrame = document.createElement('iframe');
    newFrame.style.display='none';
    newFrame.style.width='1px';
    newFrame.style.height='1px';
    if(document.getElementById("distinguishingID")){
        newFrame.src = document.location.protocol + '//fls.doubleclick.net/activityi;src=1234567;type=type123;cat=cat456;ord=1;num=' + Math.random();
    }else{
        newFrame.src= document.location.protocol +  '//fls.doubleclick.net/activityi;src=1234567;type=type345;cat=cat789;ord=1;num='+ Math.random();
    }
    document.getElementsByTagName('body')[0].appendChild(newFrame);
});

EASILY Bulk Load files into a table with different columns

Posted by & filed under Code, Development, Java, SQL, Work.

with NO FORMAT FILE!

I consider this a gem just hiding on the MSDN docs :

Using BULK IMPORT on a View

The following example creates the v_myTestSkipCol on the myTestSkipCol table. This view skips the second table column, Col2. The example then uses BULK INSERT to import the myTestSkipCol2.dat data file into this view.

In the SQL Server Management Studio Query Editor, execute the following code:

CREATE VIEW v_myTestSkipCol AS
SELECT Col1,Col3
FROM myTestSkipCol;
GO

USE AdventureWorks2008R2;
GO
BULK INSERT v_myTestSkipCol
FROM ‘C:myTestSkipCol2.dat’
WITH (FORMATFILE=’C:myTestSkipCol2.xml’);
GO

Say what? You can just write a view and not deal with a nasty format file? This is useful any time you have an additional column you want on your staging table, but it’s not in the file you’re importing, and want to add it as part of your processing. Either its a flag on where the file came from, the file name, the date loaded etc…

Lets pretend, as part of a larger ETL process I just want to import names of people that belong to different teams.

  • I get a file daily for each team with new members
  • I want to transform the data for all teams as one operation after bulk-loading each team file (preferably into the same staging table)
  • Each file has the same name, I get it from an API based on the team name so they look no different when downloaded
-- person staging table
Create Table StagePerson(
    first varchar(50) NULL,
    first varchar(50) NULL,
    team varchar(50) NULL
)

-- a view to match whats in the file
CREATE View vImportStagePerson as
 select first, last
 from person
go

-- my ETL process will download each file, then bulk load into the table
-- ETL loop: for each team
BULK INSERT vImportStagePerson
FROM 'C:[team]members.csv'

UPDATE StagePerson
Set team = [team]
where team is null

-- end for each team

-- do your merge of all new team members from your staging table WITH team added

This way I save additional hops just to add that pesky team name to my staging table. And your final operation can take advantage of a larger set per-transaction.

Get Value from XMLA result file in NAnt with xmlpeek

Posted by & filed under Code, Note To Self, Work.

If I got paid for every hour spent wrangling with XML…

Oh wait, I do

Problem:

  • Read a scalar-result out of an XMLA response file from an MDX query run against a cube.
  • Store it in a NAnt property.

Jiest of my XMLA file:

<return xmlns="urn:schemas-microsoft-com:xml-analysis">
  <root xmlns="urn:schemas-microsoft-com:xml-analysis:mddataset" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
	<CellData xmlns="urn:schemas-microsoft-com:xml-analysis:mddataset">
      <Cell CellOrdinal="0">
        <Value xsi:type="xsd:double" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">1.09</Value>
        <FmtValue>1.09</FmtValue>
      </Cell>
    </CellData>
  </root>


Surprisingly I’ve had good luck parsing and reading XMLA files from Java, so I figured this would be a quick solution using NAnt’s xmlpeek.  Taking a look I can just create an XPath statement of “/return/root/CellData/Cell/FmtValue”. Guess again!  After much banging my head I found a cheating solution using the XPath: “//*[local-name()=’FmtValue’]/text()” which just says “get me the local node named FmtValue anywhere in the xml doc”.  Not pretty, plus it worked on every online tester I found, but still returned nothing in NAnt – even though it says “1 node found matching…”.  I had popped a couple namespaces in to the xmlpeek task, but I still don’t really understand them…

Somehow this xml/xpath evaluator makes it all very clear (toggle the “XPath” option), it gives a handy Namespace Reference:

Namespace Reference
Prefix Namespace
a urn:schemas-microsoft-com:xml-analysis
b urn:schemas-microsoft-com:xml-analysis:mddataset
xsi http://www.w3.org/2001/XMLSchema-instance
xs http://www.w3.org/2001/XMLSchema
c http://schemas.microsoft.com/analysisservices/2003/engine

a,b,c??  I don’t know either, but if it doesn’t have an obvious prefix I guess you just make it up?  At any rate, I added the above namespaces verbaetum to my xmlpeek namespaces, then I was able to construct a “proper” XPath to my liking: “/a:return/b:root/b:CellData/b:Cell/b:Value/text()”.  With NAnt it does the text() evaluation on it’s own so you only need “/a:return/b:root/b:CellData/b:Cell/b:Value”.  With that unlocked, you should be able to peek and poke any item within an XMLA statement spit out from Analysis Services.


	<xmlpeek
	file = "result.xmla"
	xpath = "/a:return/b:root/b:CellData/b:Cell/b:Value"
	property="your.property"
	verbose="true" >
	<namespaces>
		<namespace prefix="a" uri="urn:schemas-microsoft-com:xml-analysis" />
		<namespace  prefix="b" uri="urn:schemas-microsoft-com:xml-analysis:mddataset" />
		<namespace prefix="xsi" uri="http://www.w3.org/2001/XMLSchema-instance"  />
		<namespace prefix="xsd" uri="http://www.w3.org/2001/XMLSchema" />
		<namespace prefix="c" uri="http://schemas.microsoft.com/analysisservices/2003/engine" />
	</namespaces>
</xmlpeek>

Sticky/Squealing Ratchet Starter Briggs and Stratton

Posted by & filed under DIY, Home.

It’s taken me far longer than it should, but I’ve finally gotten my McLane Edger craigslist score in a proper working order. The original issue had always been an awful squealing noise that would totally un-wind the pull cord in disastrous fashion. A secondary issue was that the cutter arm was totally frozen, would not turn, rotate or telescope. I thought maybe the cutter arm was the issue as it seemed to happen as soon as I put pressure on the engine pulley. Turns out they were un-related, after failed attemps to free the cutter arm I ended up just replacing it with a new one – best $75 spent.

I knew then it had something to do with the starter pulley, which is a hard thing to google if you have no idea what it’s called. Common official terms appear to be “Ratchet Starter”, or “Ratchet Clutch”. What also makes it hard are the specs for this engine; a 2hp Briggs and Stratton horizontal shaft 4-stroke, I think they haven’t made them since the mid 90’s but they are bulletproof little engines used in everything from snow blowers to a favorite for go-karters.  If I knew something about engines, probably wouldn’t have needed to google anything…

The best resources I found we’re Canadians or upper mid-west folk’s youtube videos on repairing their snow blowers/reel mowers, this was by far at the top:
http://www.youtube.com/watch?v=S2Kn6RiTA7U

And this guy has nothing but time to dink with small engines:
http://www.youtube.com/user/pimpinpenz#p/u/39/Fz7RUU_-YSc

To repair:

Edger with pull starter housing on, you’ll pull this off to get to the ratchet starter.

This is also an example of how to change the oil, plus stuff on blocks looks cool no matter what it is.

 

 

 

 

 

Ratchet starter assembly after removing pull-cord housing

This picture shows the ratchet assembly in the center after removing the pull-cord housing.

 

 

 

 

Inside of ratchet starter

This shows the inside of the ratchet starter.  I’d actually been to this point about three times, oiled and cleaned all of the ball bearings with no luck solving my problem.  Wasn’t until I watched that youtube video I realized that inner shaft was the problem.  I took that off, was able to clean the entire inside of the ball-bearing housing, and cleaned and lubricated the engine shaft and ratchet-post thing.

My two go-to’s for cleaning/lubrication have always been some WD-40 Multi-Use Spray with Smart Straw, 12 oz. and 3-IN-ONE 10138 Multi-Purpose Oil, 8 oz.

Now it runs like a champ, along with my new cutter arm it’s like new.  And yes, my yard looks better than yours.

 

Install OTA HD Antenna – cut the Comcast Cord

Posted by & filed under DIY, Home.

I won’t rant for too long about the great Sham that is Comcast, but remember the big HD TV transition?  With Comcast you “didn’t need to do anything”, right – nothing except get some set top boxes for each TV in your home if you wanted to retain the same programming.  Oh – and you only get one for free with most cable plans.

Long story short, I found out with this set top box I didn’t get HD channels, yet my other tv with no set top box did get HD channels.  THE COMCAST BOXES BLOCK HD CHANNELS BY DEFAULT!  Why?  So they can up-charge you $$$ to upgrade, essentially, pay extra money to remove a filter on their boxes.

As an irate customer, I bought an HD antenna and let it sit in my garage for about 18 months, then I decided to install it:

Installed HD Antenna

Parts:

The Antenna I bought was cheap, outdoor, and had a nice swivel mount for direct mounting to the side of a house, on top of something, or pole mounting.  Turns out 1 1/2″ EMT conduit is really cheap, light, and sturdy.  I decided to go big and get it just above my power service mast.  Location was chosen based on the existing cable entering the house at that location, easy to mount by getting onto the garage roof, and it will be with all the other tall stuff attached to my house.  As soon as you try to home run your own cable more than once, you’ll desire good tools, so you can be slick like the Comcast man.  That Paladin set has been worth every penny, because did you know you can use Coax for signal cable too? Now you’re a home theater master.

 

Home entry point with Comcast service line

 

In a passing conversation about HD Antennas, someone at work clued me into the fact I want to make sure I ground it properly.  Something I figured the coax cable would take care of, but once you bolt a 8′ pole to your house that is now the highest point of your roof, its a good, easy idea.  I just bought a length of 14AWG bare copper wire that runs from the top of the pole with the cable to the house, then takes the most direct route to my existing home electrical ground conveniently placed near where my cable enters the house.

The Comcast cable already had a single ground block for the coax, which had a ground wire also connected to the home ground.  This was an easy swap out for a dual ground block.  From there the cable enters the garage to where it gets Amped and distributed through the house.

Cable entering the garageComcast also installed a signal amp to help with my internet and TV signal.  This is also helpful and usually recommended for HD Antenna installs.  So now in the garage, I have a Comcast line, and my Antenna line coming in the same spot.  This makes it really easy to plug and play between the two for troubleshooting, and whenever else in the future.

 

The results were OK, most channels come in fine, however a few don’t come in well at all. I have a feeling some alignment is probably necessary, that and getting my wife sold on the nuances of OTA cable.  Overall, its a success and if I had more time id figure out the alignment, or buy a better antenna.  Also, http://www.antennaweb.org/ is actually an awesome resource with the information you’d need and want about OTA Stations, strengths, and locations in order to determine optimal antenna, placement, and alignment.

Flying high and proud, like a symbol of freedom from Comcast

Get current domain from NAnt

Posted by & filed under Code, Work.

I use NAnt for a build and deployment tool for databases and OLAP. I wanted to be able to add only the accounts needed per-environment to certain objects on the fly through the script, as they are run on whatever workstation they are being run from.

I’m using the “Primary Dns Suffix” of the machine – which for me indicates the domain we’re running in. (Dev/Test/Prod). I’m accessing this using ipconfig /all, saving the output to text, getting the value I want as a property for use later in the script.

<exec program="ipconfig" output="ipconfig.all.txt">
    <arg value="/all" />
</exec>
<loadfile file="ipconfig.all.txt" property="ipconfig.all" />
<delete file="ipconfig.all.txt" />
<property name="envrionment" value="dev" />
<regex pattern="(?<=Primary Dns Suffixs(s.){1,7}s:s)(?<environment>w{1,})" input="${ipconfig.all}"/>
<echo message="CURRENT ENvIrONmENT: ${environment}" />

Regex Master.

Analysis Services Add role members programmatically in C#

Posted by & filed under Code, Work.

First, I don’t know why; but it seems like as soon as you add in the word OLAP or Analysis Services to your problem things just stop working as you would normally expect.

Problem:

  • I’m using NAnt as a deployment tool for Database versioning and MS OLAP deployment in multiple environments.
  • My service accounts aren’t standardized across environments (don’t get me started with THAT one…)
  • I want to apply different accounts, and ONLY the accounts for those environments to my cube roles.

The “should work out of the box solution”:


Server server = new Server();
server.Connect("mah-olap-server");
Database db = server.Databases.FindByName("MahOLAPDB");
Role role = db.Roles.GetByName("Role");

RoleMember r= new RoleMember("Domain\Account");
role.Members.Add(r);
role.Update();

What’s the big deal right? Well, there are a few problems:

  • Adding it this way un-like through the GUI does not map the user account to an SID
  • Even after you’ve mapped it to an SID it will still add a duplicate role member.

First:

Server server = new Server();
server.Connect("mah-olap-server");
Database db = server.Databases.FindByName("MahOLAPDB");
Role role = db.Roles.GetByName("Role");
RoleMember r= new RoleMember("Domain\Account");
if(!role.Members.Contains(r)){
    role.Members.Add(r);
    role.Update();
}

That is hopeless, I thought at first because of the SID thing – but NO… After the next solution, Role.Members.Contains() does not work very well even though in debugger I have the EXACT same role member I’m adding, and exists in the role. Not only was it not finding it, but it was still adding a duplicate. This along with the not mapping to proper SID – I deemed to be a future-issue I should avoid now. So…

List members = new List(); // I fill this with all the members I need to add
Server server = new Server();
server.Connect("mah-olap-server");
Database db = server.Databases.FindByName("MahOLAPDB");
Role role = db.Roles.GetByName("Role");
//oh wtf, I can't seem to Linq to the role.Members so I'll do it this way, plus case insensitivity on .Remove() doesn't exist
foreach(RoleMember rm in role.Members){
   string er = (from m in members
   where m.Equals(rm.Name, StringComparison.InvariantCultureIgnoreCase)
   select m).FirstOrDefault();

   if(er!=null){
       members.Remove(er);
    }
}

foreach(string m in members){
    //you'll want to wrap in try catch in case the account doesn't exist, you'll get a IdentityNotMappedException
    NTAccount acct = new NTAccount(m);
    //conver it to SID - check if its even real
    SecurityIdentifier sid =   (SecurityIdentifier)acct.Translate(typeof(SecurityIdentifier));
    //convert it back from SID  - this will get us proper account Name format/casing
    acct = (NTAccount)sid.Translate(typeof(NTAccount));
    //create my wholesome role
    //already done my "contains" magic, add that monkey
    role.Members.Add(r);
    role.Update();
    }
}

That final solution actually works out pretty well, and accomplishes a lot of items I would have skipped had the first one just “worked”:

  • Does the mapping of user accounts prior to adding to the role.  Make sure name format, and SID is added.
  • Creates the same behavior as when you add it through VS or SSMS GUI (if you add one that exists it does nothing)
  • Wasted countless hours
  • No over-engineered minute step of my deployment

And it did solve several problems along the way:

  • Creating RoleMembers with just the name does not map it to AD or a proper SID
  • Using the Role.Members.Contains() doesn’t seem to work well
  • You can’t make .Remove() on a List<string> case insensitive

I will try to write a follow up soon regarding how to determine what environment (primary domain your machine is connecting to) you’re in from NAnt