Thursday, August 7, 2014

SQL Server Case Sensitivity and SSIS and SSAS

Currently SQL Server default is normally 'SQL_Latin1_General_CP1_CI_AS', the _CI means "case insensitive" - for case-sensitive we would see _CS in its place,
If we were to change the collation, that may break the ETL if you have any, as you may currently not use case sensitive searches for MERGES, it would take a re-write, re-test ect to make it work, 

Build DIM Tables: 

Collation can be set on Server, DB or by Query:
I would recommend using the "Per Query" collation, we can simply apply a "hint" to our query, and then the query would do a case sensitive search instead of the default, case insensitive, See here for detail:

An example for OLS:
-- case Insensitive DEFAULT
SELECT d.*, b.*, a.*, c.*
 ....
  where value = 'pass'
  
-- case Sensitive DEFAULT
SELECT d.*, b.*, a.*, c.*
  FROM..
Where
Value LIKE '%PASS%' Collate SQL_Latin1_General_CP1_CS_AS


SSIS LookUps
Options include:

1. Another option is to keep your Select DISTINCT, and then in your SSIS lookup, tell SSIS to "do the lookup in the SQL Server", I believe you can do this as follows:
"Set the CacheType property of the lookup transformation to Partial or None, the lookup comparisons will now be done by SQL Server and not by the SSIS lookup component. "


2. Also, in Dimenion processing later on, SSAS takes its collation from the DB default: "SSAS dimension will inherits its collation from underlying database and if you don't change the setting it might use case sensitive collation"

3. Use LOWER() or UPPER() functions in query and pipeline.
 
To me this is an SSIS issue, I would err on the side of simplicity and maintainable because in the future someone would need to maintain the solution and the simpler the solution the quicker someone can fix it.  In SSIS Ive always seen either lookups with Upper()/Lower(), by setting the cache type or pulling the lookup out of SSIS and use T-SQL. 

Monday, August 4, 2014

Performance Counter Example

using System;
using System.Diagnostics;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace DTech.Processor
{
    /// <summary>
    /// Helper class to demonstrate the setup of performance counters.
    /// </summary>
    public static class PerformanceCounterHelper
    {
        public static readonly string CATEGORY = "IFP";

        public static readonly string COUNTER_FILES_RECEIVED = "Total Files Processed";
        public static readonly string COUNTER_Transactions_RECEIVED = "Total Transactions Processed";
        public static readonly string COUNTER_DUP_Transactions_RECEIVED = "Total Duplicates Transactions Processed";

        public static readonly string COUNTER_AVG_FILE_DURATION = "Avg time to process file";
        public static readonly string COUNTER_AVG_FILE_DURATION_BASE = "Avg time to process file Base";

        public static readonly string COUNTER_IFP_ERRORS = "Total IFP Errors";


        // Total number of tests executed
        private static PerformanceCounter _TotalFiles = null;
        // Total Transactions
        private static PerformanceCounter _TotalTransactions = null;
        // Total dup Transactions
        private static PerformanceCounter _TotalDupTransactions = null;
        // avg files
        private static PerformanceCounter _AvgFiles = null;
        // avg files
        private static PerformanceCounter _AvgFilesBase = null;
        // avg files
        private static PerformanceCounter _TotalErrors = null;


        /// <summary>
        /// Constructor
        /// </summary>
        static PerformanceCounterHelper()
        {
            try
            {
                // Set up the performance counter(s)
                if (!PerformanceCounterCategory.Exists(CATEGORY))
                {
                    // Create the collection container
                    CounterCreationDataCollection counters = new
                        CounterCreationDataCollection();
                    addCounter(counters, COUNTER_FILES_RECEIVED, COUNTER_FILES_RECEIVED, PerformanceCounterType.NumberOfItems64);
                    addCounter(counters, COUNTER_Transactions_RECEIVED, COUNTER_Transactions_RECEIVED, PerformanceCounterType.NumberOfItems64);
                    addCounter(counters, COUNTER_DUP_Transactions_RECEIVED, COUNTER_DUP_Transactions_RECEIVED, PerformanceCounterType.NumberOfItems64);

                    addCounter(counters, COUNTER_IFP_ERRORS, COUNTER_IFP_ERRORS, PerformanceCounterType.NumberOfItems64);

                    addCounter(counters, COUNTER_AVG_FILE_DURATION, COUNTER_AVG_FILE_DURATION, PerformanceCounterType.AverageTimer32);
                    addCounter(counters, COUNTER_AVG_FILE_DURATION_BASE, COUNTER_AVG_FILE_DURATION_BASE, PerformanceCounterType.AverageBase);
                   

                    //// Create counter #1 and add it to the collection
                    //CounterCreationData tests = new CounterCreationData();
                    //tests.CounterName = "Total Tests Executed";
                    //tests.CounterHelp = "Total number of tests executed.";
                    //tests.CounterType = PerformanceCounterType.NumberOfItems32;
                    //counters.Add(tests);

                    //// Create counter #2 and add it to the collection
                    //CounterCreationData testsPerSec = new CounterCreationData();
                    //testsPerSec.CounterName = "Tests Executed / sec";
                    //testsPerSec.CounterHelp = "Number of tests executed per second.";
                    //testsPerSec.CounterType =
                    //   PerformanceCounterType.RateOfCountsPerSecond32;
                    //counters.Add(testsPerSec);


                    //// Create counter #3 and add it to the collection
                    //CounterCreationData avgTest = new CounterCreationData();
                    //avgTest.CounterName = "Average Test Duration";
                    //avgTest.CounterHelp = "Average time to execute a test.";
                    //avgTest.CounterType = PerformanceCounterType.AverageTimer32;
                    //counters.Add(avgTest);

                    //// Create counter #4 and add it to the collection
                    //CounterCreationData avgTestBase = new CounterCreationData();
                    //avgTestBase.CounterName = "Average Test Duration Base";
                    //avgTestBase.CounterHelp = "Average time to execute a test base.";
                    //avgTestBase.CounterType = PerformanceCounterType.AverageBase;
                    //counters.Add(avgTestBase);

                    // Create the category and all of the counters.
                    PerformanceCounterCategory.Create(CATEGORY,
                       "IFP Counters.",
                       counters);
                }

                _TotalFiles = buildPerfCounter(COUNTER_FILES_RECEIVED);
                _TotalFiles.RawValue = 0;

                _TotalTransactions = buildPerfCounter(COUNTER_Transactions_RECEIVED);
                _TotalTransactions.RawValue = 0;

                _TotalDupTransactions = buildPerfCounter(COUNTER_DUP_Transactions_RECEIVED);
                _TotalDupTransactions.RawValue = 0;

                _TotalErrors = buildPerfCounter(COUNTER_IFP_ERRORS);
                _TotalErrors.RawValue = 0;

                _AvgFiles = buildPerfCounter(COUNTER_AVG_FILE_DURATION);
                _AvgFiles.RawValue = 0;

                _AvgFilesBase = buildPerfCounter(COUNTER_AVG_FILE_DURATION_BASE);
                _AvgFilesBase.RawValue = 0;
            }
            catch (Exception exc)
            {
                Debug.Write("PerformanceCounterHelper: ERROR-> " + exc.Message);
            }

        }


        /// <summary>
        /// Increment the CodeGuru sample counters.
        /// </summary>
        /// <param name="Ticks">Timing interval</param>
        public static void IncrementFiles()
        {
            try
            {
                checkReset();
                if (_TotalFiles.RawValue >= Int64.MaxValue || rF)
                {
                    _TotalFiles.RawValue = 0;
                    rF = false;
                }
                else
                    _TotalFiles.Increment();
            }
            catch { }
            //_TestsPerSecond.Increment();
            //_TotalDupTransactions.IncrementBy(Ticks);
            //_TotalDupTransactionsBase.Increment();
        }

        public static void IncrementTransactions()
        {
            try
            {
                checkReset();
                if (_TotalTransactions.RawValue >= Int64.MaxValue || rS)
                {
                    _TotalTransactions.RawValue = 0;
                    rS = false;
                }
                else
                    _TotalTransactions.Increment();
            }
            catch { }
        }

        public static void IncrementDupTransactions()
        {
            try
            {
                checkReset();
                if (_TotalDupTransactions.RawValue >= Int64.MaxValue || rDS)
                {
                    _TotalDupTransactions.RawValue = 0;
                    rDS = false;
                }
                else
                    _TotalDupTransactions.Increment();
            }
            catch { }
        }

        public static void IncrementErrors()
        {
            try
            {
                checkReset();
                if (_TotalErrors.RawValue >= Int64.MaxValue || rE)
                {
                    _TotalErrors.RawValue = 0;
                    rDS = false;
                }
                else
                    _TotalErrors.Increment();
            }
            catch { }
        }

        public static void IncrementAvgFiles(long ticks)
        {
            try
            {
                _AvgFiles.IncrementBy(ticks);
                if (_AvgFilesBase.RawValue > Int64.MaxValue)
                    _AvgFilesBase.RawValue = 0;
                else
                    _AvgFilesBase.Increment();

            }
            catch { }
        }

        public static void IncrementAvgFiles(long ticks, int howmany)
        {
            try
            {
                _AvgFiles.IncrementBy(ticks);
                _AvgFilesBase.IncrementBy(howmany);

            }
            catch { }
        }

        private static void addCounter(CounterCreationDataCollection counters, string counterName, string help, PerformanceCounterType type)
        {
            // Create counter #1 and add it to the collection
            CounterCreationData tests = new CounterCreationData();
            tests.CounterName = counterName;
            tests.CounterHelp = help;
            tests.CounterType = type;
            counters.Add(tests);
        }

        private static PerformanceCounter buildPerfCounter(string name)
        {
            PerformanceCounter r = new PerformanceCounter();
            r.CategoryName = CATEGORY;
            r.CounterName = name;
            r.MachineName = ".";
            r.ReadOnly = false;
            return r;
        }

        private static DateTime lastResetDate = DateTime.Now;

        private static bool rF = false;
        private static bool rS = false;
        private static bool rDS = false;
        private static bool rE = false;

        private static object _lock = new object();
        private static void checkReset()
        {
            lock (_lock)
            {
                if (DateTime.Now.Subtract(lastResetDate).TotalHours > 24)
                {
                    Debug.WriteLine("RESET Perf Counters");
                    rF = true;
                    rS = true;
                    rS = true;
                    rE = true;
                    lastResetDate = DateTime.Now;
                }
            }
        }
    }
}

Task Cancellation Example

private void startRoute1(FileRouteFactory.FileRouteType frType, List<string> files, List<BaseTypes.FileType> fts, IFileHelper io)
         {

             const int numTasks = 9;

             // Set up the cancellation source and get the token.
             CancellationTokenSource tokenSource = new CancellationTokenSource();
             CancellationToken token = tokenSource.Token;

             // Set up the tasks
             Task[] tasks = new Task[numTasks];
             for (int i = 0; i < numTasks; i++)
                 tasks[i] = Task.Factory.StartNew(() => new Worker().PerformTask(token), token);

             // Now the tasks are all set up, show the state.
             // Most will be WaitingToRun, some will be Running
             foreach (Task t in tasks.OrderBy(t => t.Id))
                 Console.WriteLine("Tasks {0} state: {1}", t.Id, t.Status);

             // Give some of the tasks a chance to do something.
             Thread.Sleep(1500);

             // Cancel the tasks
             //Debug.WriteLine("Cancelling tasks");
             //tokenSource.Cancel();
             //Debug.WriteLine("Cancellation Signalled");

             try
             {

                 int idx = 0;

                 do
                 {
                     if (this.CheckStop())
                     {
                         DiagnosticHelper.DebugWriteLine(this.DisplayString() + " ->  " + frType.ToString() + " , calling CancellationTokenSource.Cancel...");
                         tokenSource.Cancel();
                         Task.WaitAll(tasks);
                         break;
                     }
                     else
                     {
                         if ((idx % 5) == 0)
                         {
                             DiagnosticHelper.DebugWriteLine(this.DisplayString() + " ->  " + frType.ToString() + " ->  Task.WaitAll, Waiting...");
                         }
                     }
                     idx++;
                     if (idx > 1000)
                         idx = 0;

                 } while (!Task.WaitAll(tasks, 1000));

                 // Wait for the tasks to cancel if they've not already completed
                 //Task.WaitAll(tasks);
             }
             catch (AggregateException aex)
             {
                 aex.Handle(ex =>
                 {
                     // Handle the cancelled tasks
                     TaskCanceledException tcex = ex as TaskCanceledException;
                     if (tcex != null)
                     {
                         Debug.WriteLine("Handling cancellation of task {0}", tcex.Task.Id);
                         return true;
                     }

                     // Not handling any other types of exception.
                     return false;
                 });
             }

             // Show the state of each of the tasks.
             // Some will be RanToCompletion, others will be Cancelled.
             foreach (Task t in tasks.OrderBy(t => t.Id))
                 Debug.WriteLine("Tasks {0} state: {1}", t.Id, t.Status);


             Debug.WriteLine("Program End");
        }

Thursday, July 31, 2014

BI Visualization with Asp.net, SQL and R: Open Source, low cost and pretty cool!

I got this recruiting email which lead me to this , thought u guys might find an interesting read....looks like free BI and Visualization to me, if I was building a BI product for on the cheap, this would be pretty cool, especially for people who suck at Math like me!




U ever hear of R? Looks like a pretty cool alternative to do statistical analysis on Tabular data and to create really slick charts in boom...a few lines of code!!


I kind of like how U can pass R data as a SQL view, and it can pivot, create charts, I know its bad to mix presentation and business code, but hey this guys is a Math PHD, its always cool to see what a mathematician does with a little programming knowledge, especially in analytic's and visualizing non-linear relationships.

I could see how we could create a :
Relationship between signal, channel, and seat for a signal elastic airline/tail.
ect..

He shows an example plot and data: "Using statistical modelling, we could infer a mathematical relationship (simply speaking – a formula) between profit, price, cost, and quantity. This formula would show whether the volume increase due to the price decrease can compensate for the losses (or even often uplift profit) due to the price reduction. This concept of nonlinear relationship between various factors is not uncommon in science or/and business"


I especially like how we can pivot easily, just give R a table and call t() function:
Inline image 1

Inline image 2


Then boom:
Inline image 3

Sunday, July 20, 2014

C# Case Insensitive Dictionary, Merging dictionaries, Add or Update Dictionary Union

Here is example of merging Case Insensitive dictionaries, thru Linq, Still havent found a way to Union and only get the newest without resorting to a foreach loop....TBC...


After some research, found we can use conditional statement in Liunq ForEach like so to get new or updated dictionary items,


Add or Update Dictionary Union

 kvs2.Keys.ToList().ForEach(x =>
                    {
                        if (kvs.ContainsKey(x))
                        {
                            kvs[x] = kvs2[x];
                        }
                        else
                        {
                            kvs.Add(x, kvs2[x]);
                        }
                    });
                   



C# Case Insensitive Dictionary, Merging dictionaries

  Dictionary<string, string> kvs = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase);
                kvs.Add("UserID", "Asdasd");
                kvs.Add("Password", "Asdasd");

                Dictionary<string, string> kvs2 = new Dictionary<string, string>(StringComparer.OrdinalIgnoreCase);
                kvs2.Add("userid", "NewUser");
                kvs2.Add("userid2", "Asdasd");
                kvs2.Add("PASSWORD", "NePWD");
                kvs2.Add("PASSWORD2", "Asdasd");


                //var newDict = kvs.Union(kvs2).GroupBy(d => d.Key).ToDictionary(d => d.Key, d => d.First().Value);
                //var newDict = kvs.Union(kvs2).GroupBy(d => d.Key).ToDictionary(d => d.Key, d => (kvs2.ContainsKey(d.Key) ? kvs2[d.key] : d.First().Value));
                var newDict = kvs.Union(kvs2).GroupBy(d => d.Key).ToDictionary(d => d.Key, d => (kvs2.ContainsKey(d.Key) ? kvs2[d.Key] : d.First().Value));
                newDict.Keys.ToList().ForEach(x => Debug.WriteLine(string.Format("Key={0}-> Value={1}", x, newDict[x])));

                var newDict2 = kvs.Concat(kvs2.Where(x => !kvs.Keys.Contains(x.Key))).ToDictionary(d => d.Key, d => d.Value);
                newDict2.Keys.ToList().ForEach(x => Debug.WriteLine(string.Format("Key={0}-> Value={1}", x, newDict[x])));

Friday, July 18, 2014

Outlook to forward Email

Do you have an corp email acct that does not allow Internet access? Well use this macro to send email msgs and meetings to your gmail acct:

Sub SendGmail(Item As Outlook.MailItem)

On Error GoTo ErrHandler

 
    Dim objMsg As MailItem
    Set objMsg = Application.CreateItem(olMailItem)
   
   
    Dim iCfg As Object
    Dim iMsg As Object
    Dim srecs As String
    Dim sTo As String
   
    For Each Recipient In Item.Recipients
      srecs = srecs & Recipient & ","
    Next Recipient
   
   
    Dim sb As String
    sb = "Original Message: Sender = " & Item.SenderName & ":" & Item.Sender & ", To = " & Item.To & ", Recepients = " & srecs & vbCrLf & "Body-> " & Item.Body
   
   
    objMsg.Body = sb 'Item.Body
    objMsg.Subject = "FW: " & Item.Subject
    objMsg.Recipients.Add "YOU@gmail.com"

    objMsg.Send

ExitSub:
    'any always-execute (cleanup?) code goes here -- analagous to a Finally block.
    'don't forget to do this -- you don't want to fall into error handling when there's no error
    Exit Sub

ErrHandler:
    'can Select Case on Err.Number if there are any you want to handle specially

    'display to user
    MsgBox "Something's wrong: " & vbCrLf & Err.Description

    Resume ExitSub
    Resume
End Sub


Sub ForwardMeetingDetails(oRequest As MeetingItem)
 
 
On Error GoTo ErrHandler

    Dim oAppt As AppointmentItem
    Set oAppt = oRequest.GetAssociatedAppointment(True)
     
     
    Dim fwdAppt As MailItem
    Set fwdAppt = Application.CreateItem(olMailItem)
     strBody = "Organizer: " & oAppt.Organizer & vbCrLf _
     & "Start: " & oAppt.Start & vbCrLf & "End: " & oAppt.End _
     & vbCrLf & "Location: " & oAppt.Location & vbCrLf & "Message: " & oAppt.Body
     
    With fwdAppt
     .Recipients.Add "jrdtechnologiesapps@gmail.com"
     .Recipients.Add "jrdtechnologies@gmail.com"
     .Subject = "ATT FW MTG: " & oAppt.Subject
     .Body = strBody
     .Send
    End With
ExitSub:
    'any always-execute (cleanup?) code goes here -- analagous to a Finally block.
    'don't forget to do this -- you don't want to fall into error handling when there's no error
    Exit Sub

ErrHandler:
    'can Select Case on Err.Number if there are any you want to handle specially

    'display to user
    MsgBox "Something's wrong: " & vbCrLf & Err.Description

    Resume ExitSub

End Sub

Tuesday, July 1, 2014

SQL Agent History Increase Number of Days in History

Per http://dba.stackexchange.com/questions/44293/history-from-sql-agent-jobs-sql-server-2005

Examples:

EXEC msdb.dbo.sp_set_sqlagent_properties
    @jobhistory_max_rows = 1000,
    @jobhistory_max_rows_per_job = 100


--Set new Limit size of job history
EXEC msdb.dbo.sp_set_sqlagent_properties @jobhistory_max_rows=-1,@jobhistory_max_rows_per_job=-1

--delete jobhistory older than 7 days and schedule following as a job or use gui
DECLARE @oldest_date DATETIME
SET @oldest_date = DATEADD(DAY,-7,GETDATE())
PRINT @oldest_date
EXEC msdb..sp_purge_jobhistory NULL,NULL,@oldest_date