Most of us who have worked with SQL knows of GetDate(), it returns the timestamp of the very moment it executed. Combine that with dateadd function and date calculations become much more powerful.
dateadd can take a date, and pick a part of a date (like day, month, year) and do arithmetics to it in order to get another desired date.
Example: dateadd(dd, -1, getDate()) returns yesterday, dateadd(yy, 1, getDate()) returns the same day of next year.
Last week I need to make a report that returns result of last week, last month and year to date. To make things complicated, last week doesn't mean 7 days ago, but actually Monday last week to Sunday last week. Same with last month (1st of Sept to 30th of Sept if the report is run today), to fetch the fromDate and the toDate for those case scenarios, we need to combine dateadd with datediff:
Datediff returns the integer difference for the 2 specified dates and the datepart specified.
Example: datediff(dd, getDate(), dateadd(dd, 1, getDate())) will return 1
Now, with both datediff and dateadd in action:
Dateadd(wk, Datediff(wk, 6, getDate())-1, 7) returns the first date of last week at 0 hour, 0 minute, 0.000 seconds
Dateadd(month, Datediff(month, 0, getDate())-1, 0) returns the first date of last month at 0 hour, 0 minute, 0.000 seconds
Dateadd(year, Datediff(year, 0, getDate())-1, 0) returns the first date of last year at 0 hour, 0 minute, and 0.000 seconds
From there we can be lazy about calculating the last date of last week:
Dateadd(wk, Datediff(wk, 6, getDate()), 7) actually returns the first date of this week at 0 hour, 0 minute, 0.000 seconds, if we run a query with '<' instead of '<=' then this will work just fine.
To complete the queries:
Dateadd(month, Datediff(month, 0, getDate()), 0) returns the first date of this month at 0 hour, 0 minute, 0.000 seconds
Dateadd(year, Datediff(year, 0, getDate()), 0) returns the first date of this year at 0 hour, 0 minute, and 0.000 seconds
Thursday, July 18, 2013
Thursday, July 4, 2013
Handling multi value parameters in Reporting Services 2008 R2
In reporting services, one can set up a multiple value parameter as a dropdown list with check boxes. User can then select the desired values as search parameter for the report. Such a parameter will look like as follows:
However, for an SQL stored procedure that expects a string as parameter for provider, how would a multivalue selection be handled? Multivalue selection enters the stored procedure as comma separated string. However, if we try the following: Select * from fruits where name = 'apple, orange, banana' You are not going to get much results. So we need a way to split the comma separated strings. An example of a split function looks like this:
This function returns a table with each comma separated value as individual row. So the query of fruit selection becomes the following: select * from fruits where name in (select Token from dbo.split('apple, orange, banana'))
However, for an SQL stored procedure that expects a string as parameter for provider, how would a multivalue selection be handled? Multivalue selection enters the stored procedure as comma separated string. However, if we try the following: Select * from fruits where name = 'apple, orange, banana' You are not going to get much results. So we need a way to split the comma separated strings. An example of a split function looks like this:
This function returns a table with each comma separated value as individual row. So the query of fruit selection becomes the following: select * from fruits where name in (select Token from dbo.split('apple, orange, banana'))
Thursday, June 20, 2013
Outsourcing
It has been going on for more than a decade, white collar jobs had been flooded to Asia from the US since the first tech crash. It started off with customer service, then moving on to software development.
Thousands of software development jobs moved across the ocean and those who couldn't deal with the change ended up losing their jobs, while software architects, especially those who has the background of the outsourcing destinations, suddenly took a leap in their career.
After years of experiments, the results were mixed. Some company claimed the quality of the product were too poor to meet the industrial standard, while some managed to maintain the quality while saving a big chunk of labor cost in the process.
So what was the difference? It can basically be summarized in 3 words: Design, Planning, Management.
Though software engineers across the ocean can do the development job for one fifth of the cost, but they are not meant to be software architects. Even if you come across one with software architect experience, it is unlikely they will have the same domain knowledge and cultural understanding to design for a software to meet the customer's demand. Thus the design should not be outsourced.
Once the design is done, success is still not guaranteed. The architect should have a plan on how development should be done. Preferably chopping down the assignment to smaller modules that is bite size for the oversea developers, then running unit tests to control the quality.
The last but not least is the day to day management to avoid any unexpected last minute crisis. The last thing a project manager wants is the development went totally off track and no one noticed the problem until it is too late. This goes with the planning as the what how when of management should be included in the planning. Management should of course account for the cultural and language difference. Thus this is considered the biggest obstacle to run a successful software project.
In conlusion outsourcing could work but success is in no way guaranteed. However by carefully executing the project and account for the cultural difference. Success is definitely achievable.
Thousands of software development jobs moved across the ocean and those who couldn't deal with the change ended up losing their jobs, while software architects, especially those who has the background of the outsourcing destinations, suddenly took a leap in their career.
After years of experiments, the results were mixed. Some company claimed the quality of the product were too poor to meet the industrial standard, while some managed to maintain the quality while saving a big chunk of labor cost in the process.
So what was the difference? It can basically be summarized in 3 words: Design, Planning, Management.
Though software engineers across the ocean can do the development job for one fifth of the cost, but they are not meant to be software architects. Even if you come across one with software architect experience, it is unlikely they will have the same domain knowledge and cultural understanding to design for a software to meet the customer's demand. Thus the design should not be outsourced.
Once the design is done, success is still not guaranteed. The architect should have a plan on how development should be done. Preferably chopping down the assignment to smaller modules that is bite size for the oversea developers, then running unit tests to control the quality.
The last but not least is the day to day management to avoid any unexpected last minute crisis. The last thing a project manager wants is the development went totally off track and no one noticed the problem until it is too late. This goes with the planning as the what how when of management should be included in the planning. Management should of course account for the cultural and language difference. Thus this is considered the biggest obstacle to run a successful software project.
In conlusion outsourcing could work but success is in no way guaranteed. However by carefully executing the project and account for the cultural difference. Success is definitely achievable.
Thursday, June 6, 2013
Data mining algorithm: Association
This one is often in action, whenever we buy something online, we always sees advertisements in the form of: "People who bought A also bought ..." and a list of products.
It is most likely the system is using association to find out what product purchase were associated together. Association looks through an object set (products in a purchase for example), and see what object attribute values tends to be together in the same object set. In English, what products appear often together in the same purchase? However, this is only the first part of association. To really make sense of the association result, one must also look into the dependency of the items. We may know someone who buys a phone usually buys the phone cover along with it, but does a person who buys a phone cover usually buys a phone along with it as well? Probably not. Thus when defining the association rule one must define the implication as well.
For more information:
http://msdn.microsoft.com/en-us/library/ms174916
It is most likely the system is using association to find out what product purchase were associated together. Association looks through an object set (products in a purchase for example), and see what object attribute values tends to be together in the same object set. In English, what products appear often together in the same purchase? However, this is only the first part of association. To really make sense of the association result, one must also look into the dependency of the items. We may know someone who buys a phone usually buys the phone cover along with it, but does a person who buys a phone cover usually buys a phone along with it as well? Probably not. Thus when defining the association rule one must define the implication as well.
For more information:
http://msdn.microsoft.com/en-us/library/ms174916
Thursday, May 23, 2013
Data mining algorithm: Clustering
Clustering works by plotting out each case as dots on multiple axis based on the dimension defined by the SSAS developer. Then the system proceed to group together points that are close to each other. In this model you are not trying to predict anything, rather just having an overview of what are some of the associations between dimension values.
In the images above the sample is divided into 7 clusters each with a breakdown on attribute value for each predefined attributes. Here we can see that there is probably an association between the ones who are single, 0 for class and 000 for industry from Cluster 1 which fits into the sentence (Those who are single belongs to class 0 and industry 000). It is a good way to do marketing research with Clustering, then we can maybe draw some conclusion on "Those who are between 40 and 50 and owns a house are also very likely to own a car.", then a company who does car related business would be able to identify potential targets for their marketing campaign.
For more information: http://technet.microsoft.com/en-us/library/ms174879.aspx
In the images above the sample is divided into 7 clusters each with a breakdown on attribute value for each predefined attributes. Here we can see that there is probably an association between the ones who are single, 0 for class and 000 for industry from Cluster 1 which fits into the sentence (Those who are single belongs to class 0 and industry 000). It is a good way to do marketing research with Clustering, then we can maybe draw some conclusion on "Those who are between 40 and 50 and owns a house are also very likely to own a car.", then a company who does car related business would be able to identify potential targets for their marketing campaign.
For more information: http://technet.microsoft.com/en-us/library/ms174879.aspx
Thursday, May 9, 2013
Data mining algorithm: Decision Tree
This came a bit late as I have promised to write about different data mining algorithms during the summer. Well, consider the weather in Oslo during July, I believe that summer has just started. So it still counts.
To start off I am going to talk about the data mining algorithm that is consider the simplest to understand, Decision Tree.
As if it is not already very obvious, decision tree is used to analyze the factors (or in database terms, a set of attributes) that affects the decision. For example, what kind of people is more likely to subscribe to newspaper? Which people is more likely going to vote for Romney in November?
To build a deicison tree, you first have a decision defined, usually one of the attribute that is either a true false or something that has a 2 value definition. Decision tree then splits the pool of sample into a tree heirarchy, each level represents an attribute and the sample is split into different nodes based on the value (or grouping of value). Once the desired attributes are used to build the decision tree, each node will then have a subset of the sample. The subset of people who is true or false to the initial decision.
A sample decision tree looks like this:
From the above example, it is all about the decision of play or don't play. Then the tree is built based on the weather outlook, humidity/windy condition. At the end we realize that if it is raining, then the windy or not determines whether the game is on or not, on the other hand, if it is sunny, humidity dictates whether the game is on. While the game is always on in the case of an overcast.
For more information:
http://technet.microsoft.com/en-us/library/ms175312.aspx
To start off I am going to talk about the data mining algorithm that is consider the simplest to understand, Decision Tree.
As if it is not already very obvious, decision tree is used to analyze the factors (or in database terms, a set of attributes) that affects the decision. For example, what kind of people is more likely to subscribe to newspaper? Which people is more likely going to vote for Romney in November?
To build a deicison tree, you first have a decision defined, usually one of the attribute that is either a true false or something that has a 2 value definition. Decision tree then splits the pool of sample into a tree heirarchy, each level represents an attribute and the sample is split into different nodes based on the value (or grouping of value). Once the desired attributes are used to build the decision tree, each node will then have a subset of the sample. The subset of people who is true or false to the initial decision.
A sample decision tree looks like this:
From the above example, it is all about the decision of play or don't play. Then the tree is built based on the weather outlook, humidity/windy condition. At the end we realize that if it is raining, then the windy or not determines whether the game is on or not, on the other hand, if it is sunny, humidity dictates whether the game is on. While the game is always on in the case of an overcast.
For more information:
http://technet.microsoft.com/en-us/library/ms175312.aspx
Thursday, May 2, 2013
Data Mining with Microsoft SSAS
One of the popular reason for using Microsoft SSAS is because of its standard support of data mining features. Something that would cost a fortune with other softwares. Of course, I am not saying that MS SSAS is going to be as good as those softwares, but it definitely is suffice to do some basic straightforward data mining in most cases. Along with DMX (Data mining expressions) and tightly integrated CRM and Sharepoint, this whole architecture is definitely something most SMB wishes for in creating a "budget" solution.
Throughout the summer I will go through the following data mining algorithms that are supported by MS SSAS.
Stay tuned.
Throughout the summer I will go through the following data mining algorithms that are supported by MS SSAS.
- Association
- Clustering
- Decision Trees
- Linear Regression
- Logistic Regression
- Naive Bayes
- Neural Network
- Sequence Clustering
- Time Series
Stay tuned.
Thursday, April 25, 2013
Will software engineers get to board Noah's Ark?
I recently watched the movie "2012" and that was the question that popped up in my head. For those who hasn't watched the movie yet, I recommend it.
Ok, since I am neither a very influential person in my field of work and I don't have the skills to navigate myself through massive earthquake in a car, nor a boss who happens to own a jet, my chance of being selected or forcing my way into the ark seems small, but what about the others?
In the scenario described in the movie (or the bible, whichever you prefer), there will be a massive destruction of the world and the only "thing" that is meant to survive is Noah's Ark along with whatever it manages to carry onboard. In the bible version, Noah took along all possible animal species (one male and one female) and plant seeds, and whoever who believed in his doom talk. Well, the world was a lot simpler back then. In a world as complicated as today, what else do we need?
Instead of just having Noah's family we may have to have representatives from all countries and races in the world, there are also far more species and books to carry, along with more tools and machines (Solar power I hope) that will speed up the rebuilding process. Of course there will now be books (or maybe ebook as they are far easier to carry) and art. There will also be a number of professionals each with expertise in their own field to either maintain the Ark during the waiting period and to direct the rebuilding process (Luckily the Ark in 2012 is far bigger than the one of Noah's). So what kinds of professionals do we need?
To name a few, we need farmers who can sustain the food supply, doctors to care for the sick, probably pharmacists to go along. Police to enforce order, but not much need for lawyer at this point. A bunch of renewable energy specialists would be good to quickly bring the power grid back up. Along with some telecom workers to bring up the corresponding telecom system. Without any of these there will be no need for IT specialists.
Though I think some people in the field of IT will need to be around in order to recreate the world, but not all field of IT is needed. If we are to look at all the softwares, hardwares that are designed and implemented today, 99.99% of them can easily be let go and we won't ever have to feel bad about it. It may even be a better idea to start from scratch. The rest can be stored in a revision control system, so specialists in that field would be good, to go along with some DBA. Now, to recreate all the bad hardwares and softwares that was let go, we need architects (the good ones of course, but that's not easy to define) who can direct the recreation once the rebuilding process starts. It would be nice to bring some business analysts to help defining the new batch of software so that they will be meeting the demands of the new world.
On the other hand, it is most likely that the general programmers will be waving the ark goodbye in tears as it is much lower cost to bring programming books. I am also not sure if we will need project managers as the space in the ark is precious (There can be an endless debate on that). As for the "luxury" systems that are good to have, how about all the BI, CRM, Sharepoint, Mobile App developers? Though I am a BI/CRM developer myself I really can't see the world to prioritize us.
So for us BI/CRM consultants who believe in the end of the world and would like to join the Ark, time to switch :)
Ok, since I am neither a very influential person in my field of work and I don't have the skills to navigate myself through massive earthquake in a car, nor a boss who happens to own a jet, my chance of being selected or forcing my way into the ark seems small, but what about the others?
In the scenario described in the movie (or the bible, whichever you prefer), there will be a massive destruction of the world and the only "thing" that is meant to survive is Noah's Ark along with whatever it manages to carry onboard. In the bible version, Noah took along all possible animal species (one male and one female) and plant seeds, and whoever who believed in his doom talk. Well, the world was a lot simpler back then. In a world as complicated as today, what else do we need?
Instead of just having Noah's family we may have to have representatives from all countries and races in the world, there are also far more species and books to carry, along with more tools and machines (Solar power I hope) that will speed up the rebuilding process. Of course there will now be books (or maybe ebook as they are far easier to carry) and art. There will also be a number of professionals each with expertise in their own field to either maintain the Ark during the waiting period and to direct the rebuilding process (Luckily the Ark in 2012 is far bigger than the one of Noah's). So what kinds of professionals do we need?
To name a few, we need farmers who can sustain the food supply, doctors to care for the sick, probably pharmacists to go along. Police to enforce order, but not much need for lawyer at this point. A bunch of renewable energy specialists would be good to quickly bring the power grid back up. Along with some telecom workers to bring up the corresponding telecom system. Without any of these there will be no need for IT specialists.
Though I think some people in the field of IT will need to be around in order to recreate the world, but not all field of IT is needed. If we are to look at all the softwares, hardwares that are designed and implemented today, 99.99% of them can easily be let go and we won't ever have to feel bad about it. It may even be a better idea to start from scratch. The rest can be stored in a revision control system, so specialists in that field would be good, to go along with some DBA. Now, to recreate all the bad hardwares and softwares that was let go, we need architects (the good ones of course, but that's not easy to define) who can direct the recreation once the rebuilding process starts. It would be nice to bring some business analysts to help defining the new batch of software so that they will be meeting the demands of the new world.
On the other hand, it is most likely that the general programmers will be waving the ark goodbye in tears as it is much lower cost to bring programming books. I am also not sure if we will need project managers as the space in the ark is precious (There can be an endless debate on that). As for the "luxury" systems that are good to have, how about all the BI, CRM, Sharepoint, Mobile App developers? Though I am a BI/CRM developer myself I really can't see the world to prioritize us.
So for us BI/CRM consultants who believe in the end of the world and would like to join the Ark, time to switch :)
Thursday, April 11, 2013
Documentations in a typical project
As mentioned in my previous post, one can learn a lot from creating documentations for a software projects, it challenges the writer to approach and view the software project from different perspective and thus allowing the writer to have a better understanding overall.
The following are some of the common documentations one would encounter in a software project:
Software Architecture Document (SAD):
The software architecture documents (SAD) gives the reader a complete overview of the entire software architecture, you may not know how each individual component works, or what it does, but at least you know what components exist and forms the whole architecture. This is the best way to understand the whole enterprise architecture.
Functional Specification:
The functional specification is often written by a business analyst who has been to a meeting with the customer and note down all the functional requirements by the customer. There are usually a function specification for each individual "boxes" in the SAD with each describing the functionality in full detail. A good guidance of writing one would be thinking in terms of user cases to outline what the customer is trying to achieve and thus arriving to the list of functionality requirements of the boxes.
Technical Specification:
The functional specification is then passed down to the lead developer who have several years of development experience along with some domain knowledge to translate into the technical specification. The finished product would then be the "bible" where the developers implement the solution strictly based on this document. In the document, usually the data objects, the manipulation of such objects and the presentation of the objects would be listed in detail. In a test driven design approach, one can write out the unit tests based on the functional specification as a staring point.
Test Cases:
As the implementation phase is coming to the end and the test phase approaches, the test team (a true luxury) will be getting ready to test the application. Together with the business analyst, lead developer a plan of how to test the application will be made and the test case document will be written. The test cases are usually based on the functional specification and need to cover all the use cases. Then the boundry cases need to be tested to make sure they are handled in the desired manner.
Deployment Guide/User Manual:
Once the implementation is completed and the project is entering the test phase awaiting delivery. It is time to start preparing the deployment guide, and the user manual. Some of the non specified behaviors would be described in this document to guide the user. Deployment guide would be required should the customer ever need to redeploy the system to a new server in the future. In the ideal case, the system works exactly the same as expected and no one will ever need to pull out the user manual. Well, when did the world ever function in the ideal way?
The following are some of the common documentations one would encounter in a software project:
Software Architecture Document (SAD):
The software architecture documents (SAD) gives the reader a complete overview of the entire software architecture, you may not know how each individual component works, or what it does, but at least you know what components exist and forms the whole architecture. This is the best way to understand the whole enterprise architecture.
Functional Specification:
The functional specification is often written by a business analyst who has been to a meeting with the customer and note down all the functional requirements by the customer. There are usually a function specification for each individual "boxes" in the SAD with each describing the functionality in full detail. A good guidance of writing one would be thinking in terms of user cases to outline what the customer is trying to achieve and thus arriving to the list of functionality requirements of the boxes.
Technical Specification:
The functional specification is then passed down to the lead developer who have several years of development experience along with some domain knowledge to translate into the technical specification. The finished product would then be the "bible" where the developers implement the solution strictly based on this document. In the document, usually the data objects, the manipulation of such objects and the presentation of the objects would be listed in detail. In a test driven design approach, one can write out the unit tests based on the functional specification as a staring point.
Test Cases:
As the implementation phase is coming to the end and the test phase approaches, the test team (a true luxury) will be getting ready to test the application. Together with the business analyst, lead developer a plan of how to test the application will be made and the test case document will be written. The test cases are usually based on the functional specification and need to cover all the use cases. Then the boundry cases need to be tested to make sure they are handled in the desired manner.
Deployment Guide/User Manual:
Once the implementation is completed and the project is entering the test phase awaiting delivery. It is time to start preparing the deployment guide, and the user manual. Some of the non specified behaviors would be described in this document to guide the user. Deployment guide would be required should the customer ever need to redeploy the system to a new server in the future. In the ideal case, the system works exactly the same as expected and no one will ever need to pull out the user manual. Well, when did the world ever function in the ideal way?
Thursday, March 28, 2013
Writing Documentations - Walk in when everyone walks out
In one of the projects that I have worked in, the list of the document requirements were quite long and *un*fortunately I was assigned to do them. On one hand it was boring and tedious, and it doesn't boost my morale when I am almost sure that no one will be reading them. On the other hand, this was a golden opportunity for me to disect a project and analyse it inside out.
Here is the document list that was passed down to me and expect a list of filenames (with content of course) in return:
- Software Architecture Document (SAD)
- Functional Specification
- Technical Specification
- Reusability Analysis (of some of the components from previous projects)
- Test Procedure
- Test Cases
- User Manual
Thursday, March 14, 2013
Documentations - The priceless element in a software project
Documentations, when you have it you don't see the value of it (Price-less), when you don't have it you would be giving anything for it (Priceless).
This is especially true for companies who not just develop the software for their customer, but would also be responsible for maintenance as well. Everything seems to be working fine at delivery, acceptance tests passed, acceptance letters signed and the final payment received. At that moment, no one pays attention to the documentations that come with the delivery. Someone may even be agonized over the wasted hours on them.
Fast forward to 2 years later, the customer wants to add a couple of new features. The lead architect along with a couple of developers have since delivery resigned. No big deal right? They are programmers and can find their way around the code if enough time were given. Well, as soon as the solutions opened in Visual Studio, tears/sweat starts dropping down their cheeks as if they have just opened Pandora's box. There are hundreds of files that hang together in a manner that cannot be understood. To make the matter worse, there aren't even any comments written by the code.
This is probably a scenario we have came across at some point in our career. So why do we continue to ignore the importance of having documentations to go along with a software project and fail to dedicate enough resources to get it done in a comprehensive manner? A common answer I have heard was that the documentations were never read. So because the documentations were believed to be useless, we stop writing them and so the negative spiral goes on.
So for all of you who are designing a solution or doing implementations at the moment. Please take a few moments to document what you are doing. A few moment spent by you today will save a lot of moments for others in the future.
This post will follow by another on what kind of documentations are crucial in a software project and some of the key elements that should be included. Stay tuned.
This is especially true for companies who not just develop the software for their customer, but would also be responsible for maintenance as well. Everything seems to be working fine at delivery, acceptance tests passed, acceptance letters signed and the final payment received. At that moment, no one pays attention to the documentations that come with the delivery. Someone may even be agonized over the wasted hours on them.
Fast forward to 2 years later, the customer wants to add a couple of new features. The lead architect along with a couple of developers have since delivery resigned. No big deal right? They are programmers and can find their way around the code if enough time were given. Well, as soon as the solutions opened in Visual Studio, tears/sweat starts dropping down their cheeks as if they have just opened Pandora's box. There are hundreds of files that hang together in a manner that cannot be understood. To make the matter worse, there aren't even any comments written by the code.
This is probably a scenario we have came across at some point in our career. So why do we continue to ignore the importance of having documentations to go along with a software project and fail to dedicate enough resources to get it done in a comprehensive manner? A common answer I have heard was that the documentations were never read. So because the documentations were believed to be useless, we stop writing them and so the negative spiral goes on.
So for all of you who are designing a solution or doing implementations at the moment. Please take a few moments to document what you are doing. A few moment spent by you today will save a lot of moments for others in the future.
This post will follow by another on what kind of documentations are crucial in a software project and some of the key elements that should be included. Stay tuned.
Subscribe to:
Posts (Atom)