At a client, they are interested in trying out some data cubes, so I spun up an instance of SSAS and gave them a demo of some of the awesome opportunities that cubes unlock – but found an interesting puzzle trying to cube-ify some of their data.
The client sells subscriptions for their services – and their subscriptions can be in different statuses at different times. Whether or not they knew it, their subscriptions where a slowly changing dimension. And various departments wanted to know how many of their subscriptions might be in a given status at a given time.
With SQL (or MDX), it is fairly straightforward to right to right queries that use can return what rows call within a start and end date, but with a self-service BI style pivot table in Excel, that may not be so easy – so my first goal was to change from date ranges to effective dates.
I created a view of the data, where one row for a one year subscription becomes 365 rows – one for every day of the year. I then created a measure that does a distinct count of Subscription IDs - This means that in Excel you can filter the data on a given day and know what subscriptions apply to that day.
However, with 200,000 active subscriptions on any given day, and 365 days in a year, that means 73 million rows of data per year. Sadly – the distinct count on all that data was less than speedy. At that point, the suggestion was to change from a measure to a calculation.
Instead of a distinct count of the Subscription ID in the subscription fact table, a Subscription Dimension was created and a calculated measure added which does a distinct count on Subscription ID.
Where the distinct count was scanning through millions of rows in the fact table for an answer, the calculation can focus on only the thousands of rows that match the given query. The run time on more complex queries now shifted from minutes to seconds – not ideal, but definitely tolerable.
Lately I've inherited some code written by another company using quite a bit of LINQ to SQL and quite a bit of lazy loading. I changed much of the worst offenders to eager load the relationships I knew I would want, and still felt like the system was slower than I would have expected.
A sample of my code:
DataContext custom = new DataContext();
DataLoadOptions dlo = new DataLoadOptions();
custom.DeferredLoadingEnabled = false;
dlo.LoadWith<Customer>(C => C.CustomerAddress);
custom.DataLoadOptions = dlo;
custom.Customer.Where(c => c.Active == true).ToList();
After looking at the function in SQL Profiler, I noticed something interesting about how LoadWith works in LINQ to SQL - And I was surprised -
Inside one database transaction, first SQL executes the filtered query against the Customer table. Next, for every Customer Address in the result set, the following query executes:
SELECT [t0].[AddressID],[t0].[Address1],[t0].[Address2],[t0].[City],[t0].[State],[t0].[Zip]
FROM [dbo].[CustomerAddress] AS [t0]
WHERE [t0].[AddressID] = @p0
In many of the queries, the relationship chain is several tables long, and in worst cases - causing the number of related look-ups to grow geometrically.
For many of those queries, I switched to using LINQ to SQL to fetch all of the data in one query, and iterating through the result assigning to lists on the server rather than using the LINQ to Entities approach which brought the run-time down to a much more respectable number.
I had been putting together a solution for a client over the past few weeks using Microsoft Azure, and had been really enjoying their new Virtual Machine functionality. The other morning I logged in to make some changes to the virtual machine and noticed it was missing!
After browsing the web and asking around, the answer is that once the free azure trial runs out, rather than charge any money Microsoft destroys the virtual machine compute instance.
The VHD is still on the Azure blob storage, but the compute instance is no more.
Well, not entirely no more - When you try to create a new Azure IaaS Virtual Machine - this time with money on the table - Microsoft Azure will inform you that the name is already taken.
When Microsoft destroyed your machine, little pieces of Virtual Machine are still left in their infrastructure. Now it is time to pull out Azure Power Shell to clean up the little bits of Virtual Machine still lying around so that you can get the system back to where you started.
And when you start up PowerShell, you will need to synchronize PowerShell with your Azure Compute instance - To do this you will need your publish settings file (the same one you use to synchronize Azure with Visual Studio) - Pull that down from here:
https://windows.azure.com/download/publishprofile.aspx and save that somewhere convenient.
But the ones you want are:
*** Connect PowerShell to your Azure instance
$cert = Get-File D:\path\my.pubsettings
Import-AzurePublishSettingsFile $cert
*** Delete the phantom azure virtual machine
Remove-AzureVM servicename -myvirtualmachine
*** You should now be able to create a new virtual machine with the same name as the old virtual machine.
*** It is possible to see the virtual machines you currently have running - strangely enough, the one that Microsoft destroyed will likely not be in this list
$myMachines = Get-AzureVM
$myMachines
*** This will list every active virtual machine you have on your account
Good luck!
I was helping a coworker with some code changes on a windows console application, when suddenly the entire program refused to load:
The type initializer for [program] threw an exception.
was thrown at the static void main() routine starting the console app.
The console application would not even load. Looking into the stack trace of the exception, it seemed to point to configuration problems, so first thought was to look at the app.config.
Everything checked out. No issues there.
After looking deeper under the cover, started looking at the class itself. The class itself was defined as static, and there were a half dozen static properties of the object that were being assigned in the class, in addition to the program's static void main() that was trying to run.
The exception was resulting from attempting to initialize the static variables in the static class, but the stack trace was pointing at the static void main() because technically the execution context is at that point in the application lifecycle.
Last week I pushed a new application up to production, and started hearing complaints of a memory leak. After running some diagnostics I learned two exciting things:
http://bugs.jqueryui.com/ticket/7666
The current version of jQuery UI (1.18.16) has a memory leak with the DatePicker control - including the DatePicker control as part of the jQuery UI is all it takes to cause memory to be allocated and never returned until the browser is closed. Every refresh of the page, or every time a new page is loaded, more memory is allocated to the browser process.
Although my application did not utilize the DatePicker control, it came as part of the default jqueryui package and I did not go out of my way to exclude the DatePicker from the jqueryui package.
This reminded me of an important aspect of software design: The more features a system has, the more features a system has that can go wrong. Although today we may believe our system to be safe and secure, new vulnerabilities will undoubtably be found tomorrow proving that we weren't so safe after all. Exposing additional functionality that is not intended to be used is potentially dangerous and in my case caused an unecessary memory leak.
At the same time I discovered another memory leak in my implementation of http://datatables.net/ that applies to IE 7.
I had a table like such:
$(document).ready(function() {
$('#example').dataTable( {
"aoColumns": [
{ "fnRender": function ( oObj ) {
return "<div onMouseOver=\"alert('abc');\">" +
oObj.aData[0] + "</div>"; } }
]
} );
} );
(In other words, I added some basic javascript functionality at render time to the outputed html of my datatables.)
I noticed that when I would page forwards and backwards through my datatable, memory was being leaked within Internet Explorer - In the html of the table, we have a javascript onMouseOver event which was being registered against the DOM, but since the datatables library did not do the registering, it did not know to unregister the event before loading the next page of data.
Instead of registering the events to the cells themselves, I switched to event delegation (http://www.quirksmode.org/blog/archives/2008/04/delegating_the.html) which is a much better strategy than registering an event listener for every row of a table that could potentially be several hundred rows long.
Two memory leaks down. How many more could there possibly be?...
With SSRS we can quickly generate reports that can be exported to multiple formats. What happens when you want to extend your report with custom code?
Friday November 11th 2011 (this Veteran's Day) I'll be talking about just that in Eden Prairie MN during:
http://sqlsaturday.com/99/eventhome.aspx
Perhaps you need a custom authentication layer, custom access to data requiring .Net code, or you want to extend some of the controls that come out of the box with SSRS. In this session we will see an example of extending SSRS to use a .Net library for a data provider allowing us to use a custom .Net business layer for our report. We will also show an example extension to the SSRS graph control allowing us to make design graphs for SSRS. Custom code is a great alternative for those considering using toolkits like Nevron or Dundas toolkits to extend their SSRS graphing capabilities
SSRS is a wonderful tool for quickly retrieving data from many different data sources and presenting the data to the user at a run-time decided format. One area where SSRS often falls short is when the underlying data needs to come from several different sources. Perhaps we want to retrieve data from the General Ledger which is in Oracle, and join that against a list of departments and employees which are stored in SQL Server for us to display in one table. When this happens, we are unable to join them into one dataset without the use of a linked server.
I've put together a sample SSRS DPE project and uploaded it to Code Plex as a starting point for any interested developers to work from. Depending on the specific needs of the project I'm certain additional development will be required, but my goal is to create a useful starting point for anybody that needs to use SSRS to report data coming from two or more different databases.
http://www.codeproject.com/KB/reporting-services/SSRSMultiDataSourceDPE.aspx
Javascript is a very powerful tool to enhance the user experience. However, poorly optimized javascript can have a very negative effect due to increased page load time. This becomes particularly apparent as the number of elements on a page increases thanks to controls bound to increasingly large data sets.
For those that are new to jQuery, assuming we have two divs (div_drag and div_drop), we can add drag and drop functionality by registering listeners during our form load:
$("#div_drag").draggable();
$("#div_drop").droppable({
accept: "#draggable"
});
Now instead let's assume we have two lists - active & inactive. We want to be able to drag items from left to right and back again. With a maximum height and overflow, we will have scrollbars on our list for when the length of the items extends beyond 400px.
<table>
<tr>
<td>
<div class="DrgTgtList" >
<div id="bktInactivate" class="DrgTgtHeader">
<h3>Inactive Items</h3>
(Drop here to remove an item)
</div>
<ul id="InactiveItems" class="InactiveList"
style="list-style-type:none; height: 400px; overflow:auto;"
onscroll="SetDraggable(this)">
<li class="DrgInactive" id="1">ABC</li>
<li class="DrgInactive" id="2">XYZ</li>
<li class="DrgInactive" id="3">123</li>
<li class="DrgInactive" id="4">456</li>
</ul>
</div>
</td>
<td>
<div style="DrgTgtList" >
<div id="bktActivate" class="DrgTgtHeader" >
<h3>Active Items</h3>
(Drop here to add a new item)
</div>
<ul id="ActiveItems" class="ActiveList"
style="list-style-type:none; height: 400px; overflow:auto"
onscroll="SetDraggable(this)">
<li class="DrgActive" id="7">A</li>
<li class="DrgActive" id="8">B</li>
<li class="DrgActive" id="9">C</li>
<li class="DrgActive" id="10">D</li>
</ul>
</div>
</td>
</tr>
</table>
If we wanted to make the divs draggable, all we would need is to register the divs like below:
<script language="javascript" type="text/javascript">
$(document).ready(function(){
$("#bktInactivate").droppable({
accept: ".DrgActive",
hoverClass: "drop-state-hover",
drop: function(event, ui) {
alert('deactivate!')
});
$("#bktActivate").droppable({
accept: ".DrgInactive",
hoverClass: "drop-state-hover",
drop: function(event, ui) {
alert('activate!');
}
});
$("#bktActivate li").draggable({
helper: "clone"
});
$("#bktAInactivate li").draggable({
helper: "clone"
});
}
</script>
This will work perfectly fine for a relatively small number of items in our list. However, in the above code, every div that is to be draggable or droppable is registered at the same time (the document read event). Once the number of draggable or droppable items in the list gets too high (upwards of 100) the performance of attaching the event listeners becomes very noticable in IE based browsers (several seconds to several minutes). To combat this slow, unwieldy page load, we decided to defer the event registration as long as possible.
In the below sample, we only want to register the items that are currently visible. As we scroll up and down on the containing lists, we will then register the draggable events to the newly visible items. jQuery will maintain which elements have already been registered more efficiently than we will be able to easily do so, so the responsibility of double registration is left to jQuery.
When the page loads, we will register our two droppable divs (the headers of each list) and initialize the currently visible draggable divs as draggable. Only when the scroll bar moves other divs into visibility will they be registered. We can now have a virtually unlimited number of draggable divs in our panel and feel no performance penalty on form load.
<script language="javascript" type="text/javascript">
$(document).ready(function(){
$("#bktInactivate").droppable({
accept: ".DrgActive",
hoverClass: "drop-state-hover",
drop: function(event, ui) {
alert('deactivate!')
});
$("#bktActivate").droppable({
accept: ".DrgInactive",
hoverClass: "drop-state-hover",
drop: function(event, ui) {
alert('activate!');
}
});
SetDraggable(document.getElementById('ActiveItems'));
SetDraggable(document.getElementById('InactiveItems'));
}
function SetDraggable(draggableUL) {
var scrollTop = draggableUL.scrollTop;
// Activate li items currently visible
for (var index = 0; index < draggableUL.childNodes.length; index++) {
var child = draggableUL.childNodes[index];
if (child.offsetTop < scrollTop) // skip down until we are at current level
continue;
if (child.offsetTop > scrollTop + 400) // stop after we pass visible level
break;
$("#" + child.id).draggable({
helper: "clone"
});
}
}
</script>
I am working on a project where a series of ranges are to be administered. The ranges are associated with a certain value. Say that a sales associate is at 20% commission when his sales are from 0 to 3 million, and after 3 million, the commission goes up to 25%. This data is stored in tabular format in the database, but administering the data through a tabular grid view was getting very difficult to maintain and administer. It was difficult to prevent the user from creating gaps or overlap in the ranges.
We wanted a graphical way to represent range based data. We first tried to use a "slider" (http://jqueryui.com/demos/slider/) control, but since the ranges are all affected by each other this caused us to layer sliders on top of sliders and it became a visual and administrative mess. We then went over to the white board to think of how else to represent ranges of values.
We first worked out a number line, with icons indicating the various possible actions. We tried to decide on a layout that would be as easy to reproduce in html as possible. The source code and a brief description for our Range Administration and Maintenance Control used to manage ranges of numbers is available at the following URL:
http://www.codeproject.com/Tips/232423/Range-Administration-and-Maintenance-Control
Hopefully somebody finds this a useful way to represent data, or maybe other people have put together other controls to maintain ranges of data.
At a client I work at, we have a few dozen WCF services used throughout the environment. It's become difficult to consolidate the various services so that people know when knew ones become available and to administer their connections as environments change.
To address this, we created one web project that has connection information for every service. Each service has a different factory method depending on Binding and Endpoint credentials. The bindings and endpoints are generated programmatically, rather than through the convoluted XML nodes necessary in the App.Config.
We have a few base classes BaseBinding, and BaseEndpoint:
class BaseBinding : System.ServiceModel.BasicHttpBinding
{
public BaseBinding()
{
}
}
class BaseEndpoint : System.ServiceModel.EndpointAddress
{
public BaseEndpoint(string serviceName) :
base(ConstructUri(serviceName))
{
}
private static string ConstructUri(string serviceName)
{
// Could read from config, or local resource file to determine appropriate
// addresses based on environment (prod, dev, UAT)
if (serviceName == "WEATHER")
return "http://wsf.cdyne.com/WeatherWS/Weather.asmx";
else
return "http://www.w3schools.com/webservices/tempconvert.asmx";
}
}
And also in the ServiceLibrary we have our sample classes:
public class TempuratureUtil : TempuratureService.TempConvertSoapClient
{
public TempuratureUtil() :
base(new BaseBinding(), new BaseEndpoint("Tempurature"))
{
}
}
public class WeatherUtil : WeatherService.WeatherSoapClient
{
public WeatherUtil() :
base(new BaseBinding(), new BaseEndpoint("WEATHER"))
{
}
}
Now in the application layer, I simply add a reference to the class library:
static void Main(string[] args)
{
try
{
TempuratureUtil temp = new TempuratureUtil();
string result = temp.CelsiusToFahrenheit("23");
WeatherUtil weather = new WeatherUtil();
var forecast = weather.GetCityForecastByZIP("55337");
}
catch (Exception ex)
{
string output = ex.Message;
}
}
The application is not concerned with the specifics of the WCF service, it is all determined by the class library which is being referenced.