I've had a few requests lately for resources I've used related to data warehouse design and performance tips for SSAS and SSRS for SQL Server 2005. I thought if a few people are interest there must be a lot of others out searching the web for similar nuggets of gold. So, here is my list of useful links for others to cash in on:
SSAS Performance tips: http://www.calumo.com/pdf/SSAS2005PerformanceGuide.pdf - this is a great white paper from Microsoft on fine tuning your cube performance from dimension design, through query and server hardware best practices.
Using Indexed Views to increase performance: http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx - this article describes what indexed views are and when you should consider using them in your database.
Designing your data warehouse schema - dimension modeling instruction and best practices: http://www.kimballuniversity.com/ - This site was started by Ralph Kimball, called the father of data warehousing by some, and includes many free articles on dimension modeling for a data warehouse. Understanding the Kimball technique is a must for those just starting out in data warehousing.
Best practices from a practical implementation: http://www.microsoft.com/sql/solutions/bi/projectreal.mspx - Project REAL was an attempt to implement a data warehouse for a real customer (Barnes & Noble) using SQL Server 2005 and a large set of real business data. The result is a collection of the project code and documentation on suggested design approaches, best practices and scripts you can migrate into your own applications.
Thank you everyone who attended the Chicago DevCares event on Feb. 11. We had some interesting conversations on web site security techniques and the new VS 2008 office applications features. I'm sorry for the delay on posting the slides from the event, I know I promised I'd have it up last week after the Chicago DevCares. You can find the slide content for the event at the following links:
Securing Web Applications (Part 1)
Securing Web Applications (Part 2)
VS 2008 Office Business Applications
Please note, to view the slides you'll need Office 2007 or install the Office Compatibility Pack for 2007 File Formats so you can view the file in an older version of office.
I recently encountered a very tricky issue with the threading model of .NET when you need to interop with a legacy VB 6 COM component. The web service in question was created to consume components of a third party vendor that still distributed API functionality as a set of VB 6 COM dlls. Each web service method utilized one or more calls into the VB 6 components to perform the desired functionality. Initially everything appeared to be working properly under a light user load, but as soon as load increased on the application the web service response times began to vary drastically and in some instances slow to a crawl. This was very perplexing as the call times varied so much it was hard to discern any sort of pattern. The problem was narrowed down to be coming from the response time of method calls to the VB 6 components. A trend was also noticed that the database connection for the VB 6 components was never exceeding 1 connection, which was odd considering there would be 20 ASP.NET calls in progress simultaneously and they were all utilizing the COM component. To me this indicated some kind of bottleneck that seemed to be single threading the VB 6 COM component usage. Slowly I began to remember my glorious (at the time) VB 6 days back in the late 90s and how VB 6 components are compiled to run in a single threaded apartment (STA). After doing a quick search I found an excellent article from MSDN Magazine that described the exact same issues the problem web service was demonstrating. The article explains how the issue arises from .NET running in a MTA (Multi-threaded apartment) and the way the OS is forced to run the COM components in a seperate STA space causing a queuing effect between the multiple ASP.NET threads trying to access the single COM component thread. The solution is to get ASP.NET running in a STA mode as well so that the COM components can be loaded within each individual thread space of the ASP.NET processing threads removing the need to marshal across apartments. This achieves what was the original desired result for the web service of having multiple seperate instances of the VB 6 component running concurrently for each web request. The article gives a sweet implementation work around for .NET to get a web service running in STA mode instead of the default MTA.
I had to post this for as many people to see as possible because we all know VB 6 lives on and this hidden threading issue of using the legacy components from within a .NET web service is not a very apparent problem when it starts happening. I hope this saves every developer from pulling out hair trying to isolate and resolve this issue!
Thanks to everyone that attended the December DevCares event in Chicago last week. One of the topics I presented on was techniques for optimizing client side JavaScript to obtain a better Rich Interactive Internet Application (RIIA) experience. A lot of you gave me feedback that you found the content informative and I wanted to pass along a blog link that contains most of the topics we covered so you can have it for future reference:
http://blogs.msdn.com/ie/archive/2006/11/16/ie-javascript-performance-recommendations-part-2-javascript-code-inefficiencies.aspx
Keep the following points in mind as well when writing your script code to maximize your performance:
Performance implications can be tied to the lookup in the scope chain for the variables you are accessing. Here is the list of scope in order of least to highest performance impact: Local variables, global variables, DOM. The DOM is the most expensive operation you can do so be sure to evaluate whenever you are going to walk the tree to work on an object. If you need access to an object in the tree for multiple operations consider using a pointer so that your code doesn't walk the tree each time and take the performance hit. Example:
var lside = document.body.all.lside.value;
var rside = document.body.all.rside.value;
can be changed to:
var ptrAll = document.body.all;
var lside = ptrAll.lside.value;
var rside = ptrAll.rside.value;
Also, you can use function pointers to reduce the overhead of looking up the entry point in situations where the function may be called numerous times.
var length = myCollection.getItemCount();
for (var idx=0; idx<length; idx++){
Work(myCollection[idx]);
}
The above loop will perform symbol resolution for the location of the Work function every iteration of the loop, which depending on the collection could be a large iteration. The following example utilizes a function pointer to perform the symbol resolution once prior to the loop.
var funcWork = Work;
var length = myCollection.getItemCount();
for (var idx=0; idx<length; idx++){
funcWork(myCollection[idx]);
}
Look for more great developer tips, examples and overviews coming in the 2008 DevCares sessions!
In last months DevCares LINQ presentations there were some good questions on using joins within LINQ statements when querying a collection of objects similar to how you would write a SQL join statement when querying a database. In this post I'll show a simple example of performing a LINQ to objects join.
In order to be able to perform a join of objects you first must have a property on the object that refers to either a reference to the other object itself or any sort of identifier value (such as a numeric ID). You can then use that property as the join property in the LINQ statement. For example, look at the following class definitions:
public class Person{ ... public string Name {
get { return _name; }
set { _name = value; }
}
public DateTime? Birthday {
get { return _Birthday; }
set { _Birthday = value; }
}
public int JobID {
get { return _jobID; }
set { _jobID = value; }
}
public Person(string name, DateTime birthday, bool member, int jobID){
this._name = name;
this._Birthday = birthday;
this._jobID = jobID;
}
...
}
public class Job {...
public string Title {
get { return _title; }
set { _title = value; }
}
public string Company {
get { return _company; }
set { _company = value; }
}
public int ID {
get { return _id; }
set { _id = value; }
}
public Region Location {
get { return _location; }
set { _location = value; }
}
public Job(int id, string title, string company, Region location) {
_title = title;
_id = id;
_company = company;
_location = location;
}
...}
The Person object has a JobID property which is the identifier of the person's job. We can use this to join to a collection of job objects that has more details on the actual job to return in our shaped LINQ result. If we loaded the following person objects and job objects:
List<Person> people =
new List<Person>();
myPeople.Add(new Person("Gerald", DateTime.Parse("1/2/2003"), 3));
myPeople.Add(new Person("Linda", DateTime.Parse("2/2/2002"), 1));
myPeople.Add(new Person("Sarah", DateTime.Parse("4/2/2002"), 1));
myPeople.Add(new Person("Bill", DateTime.Parse("4/5/2006"), 2));
List<Job> jobs = new List<Job>();
jobs.Add(new Job(1, "Developer", "Microsoft", Region.West));
jobs.Add(new Job(2, "Developer", "Microsoft", Region.East));
jobs.Add(new Job(3, "Technical Account Representative", "Microsoft", Region.Central));
We could then perform a LINQ statement to filter person's by specific jobs.
var result = from p in people
from j in jobs
where p.JobID == j.ID
select new{
p.Name, j.Company, j.Title
};
Using an anonymous type as the output we can shape the resulting objects to include the person's name, job title and company. We can then manipulate or display this shaped type as necessary. If you wanted you could have also had a Job property of type Job on the Person object and having a direct object reference on the Job property of every Person. It would still be possible to join the Person.Job property to Job in a LINQ statement to include a filter by an attribute of Job or specific pull out Job property values into the results.
Change the JobID property on Person to:
public Job Job {
get { return _job; }
set { _job = value; }
}
Then after assigning Job object references to the Person objects in the people collection you could use LINQ to join in a query:
var result = from p in people
from j in jobs
where p.Job == j
select new{
p.Name,
j.Company,
j.Title,
j.Location
};
LINQ to objects gives you a lot of flexibility in how you can filter against a collection of objects. With this new VS 2008 feature you can virtually wave good bye to all those previous foreach loops with comparison checks embedded within them!
A few people attending the October DevCares presentation in Chicago earlier this week requested access to the slides. You can find the slide decks for all three LINQ presentations given by myself and Rick Haffey here. Please note, you will need Microsoft Office 2007 or the Office Compatibility Pack to view the slide decks.
If you were unable to attend in Chicago you still have time to sign up for the event taking place at the Microsoft office in Downers Grove on Thursday, Oct. 25.
There are a couple of free Microsoft developer events coming up in the month of September in Chicago that are worth a look.
- In downtown Chicago on Sept. 12th is the ArcREADY session. ArcREADY is an event that is touring the Midwest through the month of September. This is a 3 hour morning presentation and discussion covering the technology aspects related to the industry buzz word “Web 2.0”. The topics cover best practices and architecture options for planning and implementing applications using Web 2.0 technologies such as AJAX in ASP.NET, Sharepoint Server 2007 and Windows Communication Foundation (WCF) in Visual Studio 2008. To read more about the session or to register go to http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032345647&Culture=en-US
- In downtown Chicago on Sept. 18th and then in the Microsoft office in Downers Grove on Sept. 27th, is the monthly DevCares event. The topic is Windows Communication Foundation (WCF). DevCares is an afternoon event consisting of multiple presentation sessions focusing each month on a different technology topic. WCF is available in the .NET 3.0 framework and is a significant architecture feature which becomes fully integrated and available in Visual Studio 2008 (there is limited availability of it in Visual Studio 2005 through add ins). This event consists of three presentation sessions on the details of WCF management features, diagnostics, channels and scalability. I will be presenting several of the sessions so I can guarantee it will dig into technical implementation details. For more information, or to register, go to http://www.devcares.com
I recently experienced a really frustrating error when attempting to build a report model for SQL Server 2005 Reporting Services off of a SQL Server 2005 Analysis Services cube. I kept getting the following error message:
w3wp!semanticmodelgenerator!5!08/28/2007-09:16:39:: e ERROR: Semantic Model Generator: ModelGeneratorExtention
An error occurred while executing rule 'Create attributes for dimension Customer ':Error Code = 0x80004005, External Code = 0x00000000:..
This cryptic error message gave me no hints as to what was really wrong and surprisingly there was nothing on the internet about it (until now!). At first it sent me on a wild goose chase thinking there was something wrong with my Customer dimension defined in the SSAS cube. But what could it be? I couldn't find anything odd about the dimension as it was a straight forward dimension with a few basic hierarchies. So at that point I removed the dimension from the cube altogether and alas got the same error when attempting to create a report model, but this time it said 'Create attributes for dimension Date '. At this point I suspected the rule information it was showing with the error information was inaccurate so I had to do some deeper problem solving.
What I eventually found to be the real cause of the error was the parent-child relationship I had defined in a Product dimension. This dimension had two fields in the Product table that were keys back to the Product table. One of the keys had its parent-child hierarchy relationship defined in the dimension already. Once I removed the self referencing foreign keys and the parent-child hierarchy on the Product dimension (and after adding my Customer dimension back into the cube) the model was finally able to be generated.
I'm posting this in case anyone else runs into this cryptic and misleading error message during model generation.
Most .NET developers that write web services are familiar with the default test page that appears when you navigate to the asmx page in a web browser. This page is installed with Visual Studio and allows a developer to test posting data to web service methods as long as the web service is only using simple type parameters. If the web service is more complicated with complex types (serialized classes from your code) the default test page is of no help. But alas, it can be utilized for testing if you customize it!
I've made a customized landing page that shows examples of the raw SOAP XML message request and response. It also allows the user to fill in values for the XML elements as SOAP parameters for simple or complex types and post them directly to the service which then displays the raw SOAP XML response. This is the best way for testing that the XML output of a web service method is being produced the way you expect it to be and involves no complex port sniffing or extra tool installation. It's also a quick way (no coding involved) to test posting different types of input values and how the service responds to them. You can find my customized landing page here. The code essentially reads the wsdl information of the web service to generate the compliant XML of a SOAP message for every method defined in the service. This customized page also supports web services that import from a static wsdl file (which a web site can be configured to do in the case where you want to override the wsdl .NET creates when hitting the .asmx?wsdl url of the web service page).
There are two ways you can set up a customized asmx default page. The first is to replace the main copy that is in effect for all web services on the server and can be found at c:\windows\microsoft.net\framework\v2.0.50727\config\DefaultWsdlHelpGenerator.aspx
Essentially the asmx handler is forwarding all non-SOAP http requests to the help generator aspx page to display a user friendly landing page. If you want all your sites to utilize the same test page then replace the DefaultWsdlHelpGenerator.aspx page with your own version. Be sure to retain the same file name though. (This works as well for the .NET 1.1 framework, just replace the v2.0.50727 folder with v1.1.4322 in the above path.)
If you want just a particular site to use a customized page then add a web.config entry to the web site to point to the location of the desired page. This overrides the usage of the page found in the windows directory listed above. An example web.config settings to make the default page point to a page in my current web site would be:
<
system.web>
<
webServices><wsdlHelpGenerator href="MyWsdlHelpPage.aspx" /></webServices>
</
system.web>
Since the help generator page is just an aspx page you can feel free to make any web changes you'd like including branding to match an associated site. The page does not have to include the ability to submit data to the web service allowing you to conceal that ability from any user that happens to land on the page.
We've recently introduced a new section to the Clarity Consulting web site called ClarityTV. In this section we are posting recordings of all the latest technology presentations given by Clarity consultants. This includes recent DevCares topics such as the Orcas and Linq previews along with CNUG presentations. ClarityTV can be found at ClarityTV.
All the presentations are viewable using the new Microsoft Silverlight technology. Silverlight is a cross-browser, cross-platform plug in for rich content and media in a .NET enabled environment. For more information on Silverlight go to the official Microsoft Silverlight web site.
Last month's DevCares topic was focused on the new features of IIS 7.0. For those of you that missed the Chicago area presentations I've composed a short summary of what I felt were the most interesting items for developers.
- IIS 7 is currently only available on Windows Vista. It will be released with Longhorn once the product ships and you can use it on the Longhorn CTP currently available. IIS 7 cannot be installed separately on Windows 2003 server as the architecture is built into benefits of OS changes available in the new OS versions. The only difference between the version of IIS 7 running on Vista and what will be available in Longhorn is that the Vista version has a 10 simultaneous connection limit (it can only accept 10 concurrent requests at any one time).
- Componentized Architecture - IIS 7 is built as a set of configurable component blocks. This means you can 'build' the type of web server you want to run on your server by selecting only the component blocks with features you are going to use. All the other blocks can be disregarded and as such are not even loaded into memory. This gives you a stream lined service without loading unused dlls and minimizes the surface area of attack since you only have specific blocks resident on the server. Everything is a block, you can essential strip away all the blocks and the IIS service becomes a dumb socket listener that receives connections but does nothing with them (not that you'd ever have a use for that in a real environment, but the building block concept is powerful).
- Merged pipeline allows a request to first go through native and managed pipeline handlers before being handed off to the isapi extension for page processing. This means you can now have all of your pages take advantage of ASP.NET 2.0 features such as forms authentication. Want to protect your images or classic ASP pages with the same security scheme used in your ASP.NET forms app? Well now you can as the merged pipeline will process the forms authentication managed handler before it directs the page request to any of the handlers so all requests regardless of file extension must pass forms auth.
- New Win32 API makes it easier to build your own component blocks to plug into the merged IIS pipeline or custom ISAP extensions. Extensions can now be made in managed code using an object oriented Win32 API, no more struggling with native C++ and antiquated Win32 calls. All the features the blocks built by Microsoft take advantage of are also exposed to your custom blocks by the API.
- IIS and ASP.NET configuration is now all done in one single config file. A server level config file can host the default settings for all sites on the server and are inherited to all sites and applications. A site or application can override inherited settings within their own web.config file if the setting isn't locked down at the server level. The server administrator can decide which settings are locked down and which aren't so your server features can be customized according to your corporate standards.
- Metabase is gone!
- New IIS Manager GUI provides ASP.Net and IIS configuration abilities. A command line tool (appcmd.exe) exists as well and enables the same capabilities as the UI.
- Real time process information is available through the GUI or command line tool. You can now see exactly what app pools are running, how many AppDomains are loaded and what requests are being executed in each pool. Request information shows the page name, client connection information, and current execution time. No more having to guess what's going on and what requests are in progress by watching perfmon stats and thread counts in task manager!
- New Failed Request Tracing gives detailed IIS pipeline trace information to help troubleshoot errors and performance in your pipeline.
This is a significant IIS release as there have been a lot of core changes while still making it simple for you to copy an IIS 6 site and have it just work under IIS 7! For more detailed information visit the new dedicated IIS site at http://www.IIS.net.
A while back I posted code on a method that would allow for serializing any object that was serializable into XML called 'A generic serialization method'. I had some requests on how the XML could be deserialized back into an object instance such as once the XML is passed between systems or stored and reloaded by an application at a later time. The following is a generic deserialization method that will take the XML document as a string and deserialize it back into an object instance.
/// <summary>
/// Deserialize an XML document into the specified object. This can only work
/// on objects that are Serializable.
///
/// Be sure to type cast the object being returned. For example:
/// MyObject obj = (MyObject)UtilityFunctions.DeserializeFromXML(xmlOfObject, typeof(MyObject));
/// </summary>
/// <param name="objToDeserialize">The full XML document representation of the object as a string.</param>
/// <param name="objectType">The type of the object that the XML will be deserialized into.</param>
/// <returns>An object reference to the deserialized object instance.</returns>
public static object DeserializeFromXML(string objToDeserialize, Type objectType) {
System.Xml.Serialization.XmlSerializer mySerializer = null;
XmlTextReader reader = null;
try {
//Instantiate the Serializer with the type of object that is being deserialized.
mySerializer = new System.Xml.Serialization.XmlSerializer(objectType);
reader = new XmlTextReader(objToDeserialize, XmlNodeType.Document, null);
//Serialize the object to xml
byte[] objData = System.Text.ASCIIEncoding.ASCII.GetBytes(objToDeserialize);
object result = mySerializer.Deserialize(reader);
reader.Close();
return result;
}
catch (Exception ex) {
throw ex;
}
finally {
if (reader != null) { reader.Close(); }
}
}
When a caller calls this method be sure to remember to cast the return value into the type of object being deserialized. Below is a simple example object and a code snipet on how to call the SerializeToXML and DeserializeFromXML methods.
[Serializable()]
public class SerializeMe{
private int _prop1 = 0;
private string _prop2 = "prop2";
public int Prop1{
get{return _prop1;}
set{_prop1 = value;}
}
public string Prop2{
get{return _prop2;}
set{_prop2 = value;}
}
}
Example code snippet:
SerializeMe cls = new SerializeMe();
cls.Prop2 = "testing";
string test = UtilityFunctions.SerializeToXML(cls);
SerializeMe cls2 = (SerializeMe)UtilityFunctions.DeserializeFromXML(test, typeof(SerializeMe));
Arghh, what are these "General Network Error" messages? You know the ones, that randomly with no apparent pattern or reason happen in an application and raise tension between your developer and networking groups on who is to blame? Typically because it says 'network' in the message the developers toss it over the fence to the network guys to try to tackle, but they toss back because the network connectivity passes all of their secret tests and then the issue drags on endlessly.
Well, I just found a great blog entry from one of the SQL engineers that may be an answer for some people. I recently ran into this issue myself when SQL 2005 server was under load on a Windows 2003 server it would generate errors with the generic "General Network Error" message or "server has timed out or is unavailable". The errors were very sporatic and seemed unrelated to anything going on in the code or the server itself. Since the applications were still running on the .NET 1.1 framework the details of the underlying protocol issue couldn't be seen in the exception stack (the SqlClient stack in .NET 2.0 has a more robust error messaging syntax that actually reports the underlying protocol message such as 'TCP socket was closed from the remote host') and the generic error messages were all we had to work with. Implementing the suggested registry change virtually eliminated the problem. Be sure to read the comments of the blog that correct the registry parameter name (SynAttackProtect) which was mis-typed in the blog entry.
In today's DevCares session I presented on the capabilities of integrating a custom application with the Search capabilities available in Windows Vista and other new Microsoft products (Office 07, Sharepoint, etc). The demo application demonstrated using the OleDb provider available with Vista to execute SQL query statements against the Vista index catalog. As an extra addition to the session demo I imported the SearchAPI type library available in the Windows SDK into a managed library so it can be utilized in .NET allowing an application to accept the same AQS (Advanced Query Syntax) statements that a user can enter in the Vista search field so any application can obtain the same results. The demo application is available here for download.
For more information on AQS and the SearchAPI be sure to check out Catherine Heller's 'A la Vista' blog.
If you are located in the Chicago area and missed today's DevCares event in downtown Chicago there is still a chance to attend at the Downer's Grove location on Thursday, Feb. 22, 2007. Registration is free and can be done through the DevCares web site.
I've been porting some DTS packages from SQL Server 2000 into the new Integration Services packages that are supported in SQL Server 2005. For those that are unaware, DTS is no longer a feature in SQL Server 2005 and has been replaced by the Integration Services (SSIS) architecture. You can still run DTS packages on a SQL Server 2005 server but it is not the focus of my blog entry. What I wanted to know the most when beginning the conversion process was how can you set the specific database connection information for a connection object in an SSIS package?
What I always run into is deployment of packages and setting the proper connection information for the environment. Previously with DTS packages you could simply save the package to your target SQL server (say you're deploying a package from development to a QA environment) and then edit the connection in the package on the QA server and save it again. Done, the connection is now pointing to the appropriate database and credentials for QA. With SSIS though your packages are compiled before you put them on the server. You cannot open a package on a server anymore and edit it and having to compile a different version for each server just because the connection needed to indicate a different server/credentials seemed crazy (and is). I couldn't find a straight forward answer though for SSIS in books online on how you could change connection info in a compiled package. Most of the things I found indicated using package configurations which allow you to store configuration values for an SSIS package in an XML file, registry or system variables. This seemed cumbersome to have to track another file to deploy for just wanting to change a db name or credential for a connection so I didn't do it. But what I eventually found out is how straightforward it is right within the Execute Package and Schedule Package screens in SQL 2005! Once you import your package into the Integration Service on your target server when you go to execute the package you can switch to the Connections tab and it lists out all the connections from within your package and allows you to check a box and override the connection string. Perfect! This is exactly what I wanted to do and I don't have to maintain any 'package configuration' files. If you run your package on a scheduled basis using the SQL Agent you also get this option when creating the job. After you add the package as a step be sure to go to the connection tab and override whatever connection strings you need too.
I was pretty surprised I couldn't find a simple reference to this in any of the books online material! So in case anyone else searches on this topic I hope they find the simple answer here and it saves them from hunting when researching an approach and from thinking they have to make package configurations for something pretty simple. Package configurations have their place in complex packages that use variables and other options, but they're overkill in my book if all you need to do is change connection information.
More Posts
Next page »