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!

https://www.simple-talk.com/dotnet/asp.net/creating-a-business-intelligence-dashboard-with-r-and-asp.net-mvc-part-1/

https://www.simple-talk.com/dotnet/asp.net/creating-a-business-intelligence-dashboard-with-r-and-asp.net-mvc-part-2/

https://www.simple-talk.com/dotnet/asp.net/creating-a-business-intelligence-dashboard-with-r-and-asp.net-mvc-part-3/

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

http://www.r-project.org/index.html

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:

Then boom:

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])));

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

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