Some of my favorite code comments

Shamelessly stolen from http://stackoverflow.com/questions/184618/
what-is-the-best-comment-in-source-code-you-have-ever-encountered

// I dedicate all this code, all my work, to my wife, Darlene, who will 
// have to support me and our three children and the dog once it gets 
// released into the public.
/// <summary>
/// This returns true if this control is supposed to be doing anything
/// at all for this request. Richard thought it was a good idea to load
/// the entire website during every request and have things turn themselves
/// off. He also thought bandanas and aviator sunglasses were "gnarly, dude."
/// </summary>

//When I wrote this, only God and I understood what I was doing
//Now, God only knows
// 
// Dear maintainer:
// 
// Once you are done trying to 'optimize' this routine,
// and have realized what a terrible mistake that was,
// please increment the following counter as a warning
// to the next guy:
// 
// total_hours_wasted_here = 42
// 
/**
* For the brave souls who get this far: You are the chosen ones,
* the valiant knights of programming who toil away, without rest,
* fixing our most awful code. To you, true saviors, kings of men,
* I say this: never gonna give you up, never gonna let you down,
* never gonna run around and desert you. Never gonna make you cry,
* never gonna say goodbye. Never gonna tell a lie and hurt you.
*/
stop(); // Hammertime!
// Replaces with spaces the braces in cases where braces in places cause stasis 
   $str = str_replace(array("\{","\}")," ",$str);
/*
 * You may think you know what the following code does.
 * But you dont. Trust me.
 * Fiddle with it, and youll spend many a sleepless
 * night cursing the moment you thought youd be clever
 * enough to "optimize" the code below.
 * Now close this file and go play with something else.
 */ 
 
// I don't know why I need this, but it stops the people being upside-down

x = -x;

Cubum autem in duos cubos, aut quadratoquadratum in duos quadratoquadratos, 
et generaliter nullam in infinitum ultra quadratum potestatem 
in duos eiusdem nominis fas est dividere cuius rei demonstrationem 
mirabilem sane detexi. Hanc marginis exiguitas non caperet.

..and translated into English:

(It is impossible to separate a cube into two cubes, or a fourth power into two fourth powers, or in general, any power higher than the second into two like powers. I have discovered a truly marvellous proof of this, which this margin is too narrow to contain.)

doRun.run();  // ... "a doo run run".

 /**
   * This method leverages collective synergy to drive "outside of the box"
   * thinking and formulate key objectives into a win-win game plan with a
   * quality-driven approach that focuses on empowering key players to drive-up
   * their core competencies and increase expectations with an all-around
   * initiative to drive down the bottom-line. I really wanted to work the word
   * "mandrolic" in there, but that word always makes me want to punch myself in
   * the face.
   */
private void updateFileCountLabel() {
 
options.BatchSize = 300; //Madness? THIS IS SPARTA!
 

SSAS Tabular Differential Processing (Process ADD)

In SSAS Tabular you have several options for getting close to real time processing. like ROLAP SSAS Multidimensional. ROLAP really is a query that uses the OLTP database for querying vs MOLAP were data is pulled in to SSAS and pre-aggregated.

SSAS 2012 Tabular is in memory(excluding direct query) so you have several choices. Full Processing(All Dimensions and facts are processed causing SSAS to make a copy of your Tabular database for current users and refreshing the tables (dimensions and facts) to be refreshed in memory. Your users don’t notice a thing provided that you have the memory to double your SSAS Tabular Database and that you have the processing power to handle current MDX queries and calculations during build of new Cube/ Tabular Database.

In a dedicated server environment where your users want a daily,hourly, 5 min  refresh and you have a small SSAS cube you will be fine as long as you take these 2 considerations in.

Anything else you need to use partitioning and/or differential processing

Partitioning is breaking the same dataset into segments (Similar to SQLS Partitioning) or Differential processing (Process ADD) or both. Partitioning (All SSAS Tabular have) involve taking the table definition query and breaking that query into subsets that use the same connection and columns by ranges. They have multiple  queries behind them that have a range based on date, transaction #. Differential Processing is exactly the same but there is a performance gain. For a partition to be processed and viewable requires a full processing on that partition and it recalculates if you have calculations across all other processed partitions in that table.

Process add creates a partition but  SSAS joins it in the MDX query eliminating the need for any full processing (recalculating across all partitions in the Tabular table). The gain is instant inserts of new data without processing. The drawback is this is only easy when your data never is updated and you are putting the work on SSAS to “merge” the data causing slower MDX queries. Lucky there is a merge process where you can merge all differential partitions back into a daily monthly.

For a database that never is updated differential (Tabular process add) is easy. Get the max record id from your tabular table and get all data greater.

For updating database you need a datamart or at least one that gives you old value and new value of column so  you can insert a delta value old-new value.

In this example I am demonstrating a no update. I’m using data from ASTrace Tool that captures all SSAS Activity from multiple servers.

What I do is processed is a bit where I update it after  I have staged and imported the data into my Tabular cube. my start and end transactions # are  max tabular and last current in SSIS.

I have a suffix (Table Name) and Prefix _YYYYMMDDSSSS

Here is how I do it in SSIS Script Task

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using Microsoft.AnalysisServices;
#endregion

namespace ST_4c592bd54c674fb5a16c0569f836f12e
{

[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{

public void Main()
{
Int32 iRowCount = Convert.ToInt32(Dts.Variables[“User::RowCount”].Value);
if (iRowCount != 0) //Skip in row rows are ready to process
{
bool fireAgain = true;
int iStartRow = (int)Dts.Variables[“$Package::iStartRow”].Value;
int iEndRow = (int)Dts.Variables[“$Package::iEndRow”].Value;
String sServerConn = Dts.Connections[“Zenox.TraceDataSSAS”].ConnectionString;
Server svr = new Server();
Database db = new Database();
Cube cb = new Cube();
DataSourceView dsv;
MeasureGroup mg;

try
{
svr.Connect(sServerConn);
db = svr.Databases.FindByName(“TraceData”);
dsv = db.DataSourceViews.FindByName(“Sandbox”);
cb = db.Cubes.FindByName(“Model”);
mg = cb.MeasureGroups.FindByName(“TraceData”);

String sPartName = “TraceData_” + CreateSuffix();
Dts.Events.FireInformation(0, “Partition Name”, sPartName, “”, 0, ref fireAgain);
Partition part = mg.Partitions.Add(sPartName);
part.Source = new QueryBinding(dsv.DataSourceID, “SELECT [dbo].[PermTraceData].[RowNumber],[dbo].[PermTraceData].[EventClass],[dbo].[PermTraceData].[NTUserName],[dbo].[PermTraceData].[ApplicationName],[dbo].[PermTraceData].[StartTime],[dbo].[PermTraceData].[CurrentTime],[dbo].[PermTraceData].[Duration],[dbo].[PermTraceData].[DatabaseName],[dbo].[PermTraceData].[ObjectName],[dbo].[PermTraceData].[Error],[dbo].[PermTraceData].[CPUTime],[dbo].[PermTraceData].[NTDomainName],[dbo].[PermTraceData].[RequestParameters],[dbo].[PermTraceData].[RequestProperties],[dbo].[PermTraceData].[TextData],[dbo].[PermTraceData].[ServerName] FROM [dbo].[PermTraceData] WHERE [Is_Processed] = 0”);
part.StorageMode = StorageMode.InMemory;
part.Update(UpdateOptions.ExpandFull);
part.Process(ProcessType.ProcessData);
part.Process(ProcessType.ProcessAdd);
}
catch (Exception ex)
{
Dts.Events.FireError(0, “Process Cube Script”, ex.Message + “\r” + ex.StackTrace, String.Empty, 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}
}

Dts.TaskResult = (int)ScriptResults.Success;
}

private String CreateSuffix()
{
String sPartName = DateTime.Now.Year.ToString();
//pad month
switch (DateTime.Now.Month.ToString().Length)
{
case 1:
sPartName += “0” + DateTime.Now.Month.ToString();
break;
default:
sPartName += DateTime.Now.Month.ToString();
break;
}
sPartName += DateTime.Now.Day.ToString();

//pad day
switch (DateTime.Now.Day.ToString().Length)
{
case 1:
sPartName += “0” + DateTime.Now.Day.ToString();
break;
default:
sPartName += DateTime.Now.Day.ToString();
break;
}
//pad hour
switch (DateTime.Now.Hour.ToString().Length)
{
case 1:
sPartName += “0” + DateTime.Now.Hour.ToString();
break;
default:
sPartName += DateTime.Now.Hour.ToString();
break;
}
//pad hour
switch (DateTime.Now.Hour.ToString().Length)
{
case 1:
sPartName += “0” + DateTime.Now.Hour.ToString();
break;
default:
sPartName += DateTime.Now.Hour.ToString();
break;
}
//pad min
switch (DateTime.Now.Minute.ToString().Length)
{
case 1:
sPartName += “0” + DateTime.Now.Minute.ToString();
break;
default:
sPartName += DateTime.Now.Minute.ToString();
break;
}
//pad sec
sPartName += DateTime.Now.Second.ToString();

return sPartName;
}

#region ScriptResults declaration
/// <summary>
/// This enum provides a convenient shorthand within the scope of this class for setting the
/// result of the script.
///
/// This code was generated automatically.
/// </summary>
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion

}
}

Added Processing SSAS Tabular Cube with Powershell

Powershell is perfect for simple processing of Tabular Cubes. Here is how to do it

Book review

I just got my copy of Instant MDX Queries for SQL Server 2012 by Nicholas Emond. I did a book review and its a great way to jump into SQL 2008 on SQL 2012 Mutidimensional

SQL 2012 Tabular

I just added a new page on SQL 2012 Tabular . Check it out!