Dimensional Modeling isn't that scary.

by Anthony 13. July 2014 13:31

I know for me for the longest time in my career Dimensional Database Modeling seemed like a mystical science.  But then I started reading The Data Warehouse Toolkit by Ralph Kimball and Margy Ross.  Ralph Kimball is pretty much the father of Data Warehousing and authored the first edition of the aforementioned book.  While the book goes into deep details about dimensional modeling and database warehousing once it clicked the concept was quite simple.  It comes down to 3 questions:

  • What data do I want to see?  (This is your fact.)
  • How do I want to see it (Time/Date, Region, Customer)? (This is your dimension)
  • What is the smallest unit I want to track? (This is your grain)

Once you have answered all three of these questions you have a very good base for what model you are going to build.  To give an example, I am an avid video game player.  Recently there was a huge summer sale on games at Steampowered.com (please note I am not endorsing any game retailers, this just happens to be one of the retailers my model is based on.)  I spent an exorbitant amount of money on this sale, but I purchased about 25 games.  That should last me a good 6 - 12 months of game playing.  Well needless to say I wanted to make sure I was actually being cost effective, not just jumping ant every purchase, so I decided to model all of my game purchasing over the past 5 years.  So I ask myself aforementioned questions:

  • I want to see purchases. FACT
  • Based on date and retailer. DIMENSIONS
  • The smallest unit to track is a single purchase not a transaction (which could be several purchases). GRAIN

So I begun the task of creating my model.  My Fact table was pretty simple, a purchase record which is purchase price, location/retailers and date.  My retailer/location dimension was pretty simple as well Location Name and Location type (online/retail).  However, time can be somewhat of a sticky wicket.  So I cheated and found a very cool Excel sheet (from the Kimball Group no-less) for generating a well defined date dimension.  I have attached my updated Excel sheet, but the original can be found here

Now that everything was defined I just needed to fill in the purchases.  The part of the Date Dimension I really liked was the key was an integer in the format of a sortable date.  This made population quite easy from an excel sheet.  Now while I like all of you I still don't want you to see my actual purchase data, as it is a bit embarrassing so I created a randomly generated data for the uploaded example.

Once all of the data was generated I used Power Pivot in Excel 2013 to slice and dice it and voila.  A small but effective dimensional model complete with reporting.

 

Pretty sweet, thanks Kimball.  All of the files with the exception of the powerpivot (as it is really easy to create) are included below.

DateDim.xlsx (819.90 kb)  This is the date dimension generator excel sheet

script.sql (10.18 kb) This is the database create script

GameSpendingFake.xlsx (30.56 kb)  the fake fact table

The following will insert the values for the location dimension:

INSERT INTO [Dim].[Location] ([LocationKey] ,[LocationName] ,[LocationType]) VALUES (1, 'Steam' ,'online')
INSERT INTO [Dim].[Location] ([LocationKey] ,[LocationName] ,[LocationType]) VALUES (2, 'Gamestop' ,'retail')
INSERT INTO [Dim].[Location] ([LocationKey] ,[LocationName] ,[LocationType]) VALUES (3, 'Origin' ,'online')

Tags: , , ,

Dimensional Model | Kimball | SQL Server | Star Schema

I apologize to my blog readers

by Anthony 13. July 2014 13:27

All,

After several weeks of back and forth with my hosting company I am unfortunately unable to restore the content for some of my older posts.  I am really sorry for the inconvienince.

Thanks for reading if you have an you particular questions on any of the posts in question, please let me know and I will answer them as soon as I can.

Thanks,

 

Anthony

Tags:

Why haven't I been using Type Script?

by Anthony 24. May 2014 10:13

In this line of work things come and go.  I like the change, but sometimes it can be a pain in the rear (who remembers Silverlight RIA services?).  So I usually let things bake for a bit before I start working with them.  Recently we have been exploring LESS and TypeScript as a way to make our CSS and JavaScript more manageable in my group.  I think LESS is awesome, but I haven't done a deep dive into it as of yet.  However, TypeScript is what JavaScript has been missing in my opinion.  A while back I wrote the article Please stop pushing JavaScript down my throat several of the items mentioned in that article are addressed by TypeScript:

  • Polymorphism
  • Encapsulation
  • Interfaces
  • Inheritance

Is all expanded upon in TypeScript.  While it is true several of these concepts do exist in JavaScript they are a bit obfuscated.  Type script brings them to the forefront. For example Interfaces do not exist in JavaScript (you can say they can be achieved but they still don't exist.) Interfaces are a simple matter in TypeScript with the  "implements" keyword classA implements InterfaceA.

Polymorphism does exist in JavaScript, I would say otherwise, but again it's implementation is not {classX implements classY}.  TypeScript handles this exactly nicely with the "extends" keyword  {classX extends classY}.

I could continue to site language references but I prefer to show rather than pontificate. So to start learning type script I set out to recreate the fractal Koch's Snowflake.  The project is using MVC, but I also created a plain vanilla HTML page that performs the same functionality as the MVC View. So the first core item of a 2D vector graphics is the point:

In java script it could be implemented like this:

var Point = (function () {
	function Point(x, y) {
		this.x = x;
		this.y = y;
	}
	Point.prototype.draw = function (htmlcanvas) {
		var drawcontext = htmlcanvas.getContext("2d");
		drawcontext.strokeStyle = lineColor;
		drawcontext.fillRect(this.x, this.y, 10, 10);

		drawcontext.stroke();
	};
	return Point;
})();

But with TypeScript we have much more type control:

interface IPoint {
	x: number;
	y: number;
	draw(htmlcanvas: HTMLCanvasElement);
}

class Point implements IPoint {
	constructor(public x, public y) {
	}
	draw(htmlcanvas: HTMLCanvasElement) {
		var drawcontext:CanvasRenderingContext2D = htmlcanvas.getContext("2d");
		drawcontext.strokeStyle = lineColor;
		drawcontext.fillRect(this.x, this.y, 10, 10);
	   
		drawcontext.stroke();
	}
}

Two points to make:

  1. I didn't save a ton of typing (in some cases it is more)
  2. I did however increase the maintainability of this code.

All of my variables are typed and I created a loosely coupled Point object.  If I need to create a special point that does something else down the line I can.  An example of this is Line to Curve.  Now this is a pretty lame example, because it isn't really something you would do in the real world.  BUT THIS ISN'T THE REAL WORLD THIS IS MY WORLD KNEEL BEFORE ZOD!!!  Ok Sorry about that back to our program already in progress.  So lets take a look at the implementation of line and curve:

interface ILine {
	start: IPoint;
	end: IPoint;
	draw(htmlcanvas: HTMLCanvasElement);
}

class Line implements ILine {
	constructor(public start: Point, public end: Point) { }
	draw(htmlcanvas: HTMLCanvasElement) {
		var drawcontext: CanvasRenderingContext2D = htmlcanvas.getContext("2d");
		this.drawByContext(drawcontext);
		drawcontext.stroke();
	}

	drawByContext(drawcontext: CanvasRenderingContext2D) {
		drawcontext.moveTo(this.start.x, this.start.y);
		drawcontext.lineTo(this.end.x, this.end.y);

		drawcontext.strokeStyle = lineColor;
		
	}

	drawpoints(htmlcanvas: HTMLCanvasElement) {
		this.start.draw(htmlcanvas);
		this.end.draw(htmlcanvas);
	}
}

class Curve extends Line {
	draw(htmlcanvas: HTMLCanvasElement) {
		var drawcontext: CanvasRenderingContext2D = htmlcanvas.getContext("2d");
		drawcontext.beginPath();  
		drawcontext.moveTo(this.start.x+10, this.start.y+10);
		drawcontext.arcTo(this.start.x+100, this.start.y+10, this.end.x, this.end.y+400,60);

		drawcontext.strokeStyle = lineColor;
		drawcontext.stroke();
	}
}

Pretty straight forward.  Now lets look at the JavaScript version:

var __extends = this.__extends || function (d, b) {
    for (var p in b) if (b.hasOwnProperty(p)) d[p] = b[p];
    function __() { this.constructor = d; }
    __.prototype = b.prototype;
    d.prototype = new __();
};
var Line = (function () {
	function Line(start, end) {
		this.start = start;
		this.end = end;
	}
	Line.prototype.draw = function (htmlcanvas) {
		var drawcontext = htmlcanvas.getContext("2d");
		this.drawByContext(drawcontext);
		drawcontext.stroke();
	};

	Line.prototype.drawByContext = function (drawcontext) {
		drawcontext.moveTo(this.start.x, this.start.y);
		drawcontext.lineTo(this.end.x, this.end.y);

		drawcontext.strokeStyle = lineColor;
	};

	Line.prototype.drawpoints = function (htmlcanvas) {
		this.start.draw(htmlcanvas);
		this.end.draw(htmlcanvas);
	};
	return Line;
})();

var Curve = (function (_super) {
	__extends(Curve, _super);
	function Curve() {
		_super.apply(this, arguments);
	}
	Curve.prototype.draw = function (htmlcanvas) {
		var drawcontext = htmlcanvas.getContext("2d");
		drawcontext.beginPath();
		drawcontext.moveTo(this.start.x + 10, this.start.y + 10);
		drawcontext.arcTo(this.start.x + 100, this.start.y + 10, this.end.x, this.end.y + 400, 60);

		drawcontext.strokeStyle = lineColor;
		drawcontext.stroke();
	};
	return Curve;
})(Line);

Again not a ton of typing saved, but the TypeScript method is much easier to follow.

Finally what I wanted to accomplish was a fractal called Koch's Snowflake with the canvas object.  Putting Point, and Line together I was able to accomplish this fairly easily with some internet help on the algorithm.  Thank you Nathan Bronecke for your Java Applet code.  The result looks like this and was created completely with TypeScript.

 

You can download the full project here: KochSnowflake GitHub

You can read more about TypeScript here

Tags: , , , , ,

The power of FUNC<T>. Not a new George Clinton song.

by Anthony 9. March 2014 09:32

So this post has been a long time coming.  I have been busy with life in general so it has been a while since I posted.  But, this post in particular was something I have wanted to get around to for about 6 years now.  So why so long?  I'm not really sure; I have been using this particular technique for a long time and I just never got around to writing about it.  Now I know many people reading this already know the benefits of Func<T> and Action<T>, but I wanted to show a practical example of its use.  

So recently at work I was presented with a task to create a server side BB Code parser, the why is not important.  I found one that was helpful, but it was a bit too rigid for me.  So I decided to modify it with a factory pattern.  Everything was going great and for the most part it was an easy translation until two tags completely wrecked my world.  OK, maybe that is a bit of an exaggeration, but they did pose a problem.

Most tags in BBCode map directly to HTML ([b]=<b>, [u] = <u>, [url=www.google.com] = <a href="www.google.com">, etc...) however the [list] and [size] tags do not. To specify an ordered uppercase roman numeral list in BbCode it is [list=I] in HTML it is <ol style="list-style-type:upper-roman;">.  So what to do?  Func<T> to the rescue.  Func<T> allowed me to include the functionality of my translation with out sacrificing encapsulation.  So lets look at the code.

My first implementation of my Tag class for BB Code was pretty simple:

/// <summary>
/// Name of the BB Code tag
/// </summary>
public string BbTagName { get; set; }
/// <summary>
/// Name of the corresponding Html Tag
/// </summary>
public string HtmlTagName { get; set; }
/// <summary>
/// The property/style attrib the BB Code links to 
/// </summary>
public string HtmlProperty { get; set; }
/// <summary>
/// If true links to a HTML style attribute not a property  
/// </summary>
public bool IsStyleReplacement { get; set; }
/// <summary>
/// Initializes the formatting tag /// </summary> public void Init(); /// <summary> /// formats the tag /// </summary> /// <param name="Data">The data that will be converted to HTML</param> /// <returns>The converted string</returns> public string Format(string Data)

This allowed me to create a factory like this:

public class BbCodeParser
{
    private List<Tag> _tags = new List<Tag>
    {
        new Tag
        {
            BbTagName = "list",
            HtmlTagName = "ol",
        },

        new Tag
        {
			//li maps directly to html li no translation needed
            BbTagName = "li",
        },
        
        new Tag
        {
            BbTagName = "size",
            HtmlProperty = "font-size",
            HtmlTagName = "span",
            IsStyleReplacement = true,
        },
        new Tag
        {
            BbTagName = "font",
            HtmlProperty = "font-family",
            HtmlTagName = "span",
            IsStyleReplacement = true,
        },
    };

    public void Init()
    {
        //init all tags
        _tags.ForEach(t => t.Init());
    }

    public string Format(string data)
    {
        //runformat on all tags
        _tags.ForEach(t => data = t.Format(data));
        return data;
    }
}

 

Easy Peasy. Next came the dreaded regex for BBCode (if you would like to see it explained go here it is an awesome regex site):

//{0} will be replace with the BBCode

private const string BbCodeRegEx = "\\[(?:\\b{0}\\b)(?:\\s*)(=?)((?:.|\\n)*?)(?:\\s*)\\]((?:.|\\n)*?)\\[\\/\\b{0}\\b(?:\\s*)\\]";

This was a generic regex that captures pretty much all BB code tags (my full implementation required some additional chicanery.)   So now for the format class:

return _regReplace.Replace(Data,htmlReplacement);

Everything was happy then came my [list=[a,i,I,A,1]] problem.  I had a few options:

  1. Allow the factory creator to use their own regex.  (I know as a library consumer I would hate this option).
  2. Create a special tag for list. (only if there was no other option).
  3. Tell my boss we can't do it :) (not really an option)
  4. Allow the factory creator to implement their own translation 

In .Net 2.0 the last option would require a delegate that pointed to a method in a separate class, not very fun or supportable.  But since 3.5 introduced Func<T> and Action<T>, it is actually a pretty easy change:

Firstly I added the property:

/// <summary>
/// Allow the consumer to provide a custom translation
/// </summary>
public Func<string, string> PropertyConversion { get; set; } 

Then I modified the RegEx replacement to support the new custom code:

/// <summary>
/// formats the tag
/// </summary>
/// <param name="Data">The data that will be converted to HTML</param>
/// <returns>The converted string</returns>
public string Format(string Data)
{
	return _regReplace.Replace(Data,MatchReplacer);
}

private string MatchReplacer(Match m)
{
	string htmlReplacement;
	//this will be the value after the '=' in bbcode
	string convertedValue = m.Groups[2].Value;
	
	//if a conversion exists use it
	if (PropertyConversion != null)
	{
		convertedValue = this.PropertyConversion(convertedValue);
	}

	//in indexed groups index 0 is the entire find so we need to start at 1
	if (m.Groups[1].Value == "=")
	{
		htmlReplacement = IsStyleReplacement ? _htmlReplacementForStyle : _htmlReplacementForAttrib;
		htmlReplacement = Regex.Replace(htmlReplacement, "\\$2", convertedValue);

	}
	else
	{
		htmlReplacement = _htmlReplacementBasic;
	}
	htmlReplacement = Regex.Replace(htmlReplacement, "\\$3", m.Groups[3].Value);
	return htmlReplacement;
}

I highlighted the important code.  And that was it.  Now the consumer can do all kinds of crazy stuff.

So my new list tag implementation looks like this:

new Tag
{
	BbTagName = "list",
	HtmlProperty = "list-style-type",
	HtmlTagName = "ol",
	IsStyleReplacement = true,
	PropertyConversion = s =>
	{
		switch (s)
		{
			case "1":
				return "decimal";
			case "A":
				return "upper-alpha";
			case "a":
				return "lower-alpha";
			case "i":
				return "lower-roman";
			case "I":
				return "upper-roman";
			default:
				return "decimal";
		}
	},
},

So no matter the translation I pretty much have it covered.  This has been a useful technique for me for years, I just always forget to post it.  Hopefully it helps someone.

I have attached the full code below:

BbCodeToHtml.zip (4.39 mb)

Happy Coding. 

Tags: ,

Linq | OOD

Calendar

<<  October 2014  >>
MoTuWeThFrSaSu
293012345
6789101112
13141516171819
20212223242526
272829303112
3456789

View posts in large calendar

Page List

RecentComments

None