30 December 2013

Don't buy Windows 8

A few months ago we decided that our home computer, the good old Windows XP, should retire. The PC isn't dead, it should just have some new RAM, and we format it, and removed all the data to external hard drive. And yet we felt yhe need to refresh after 8 years of hard service.
We thought that a Mac would be too expensive, and as both me and my partner are developers we did the obvious and bought a strong PC, Windows 8. What a mistake! What a waste of money! We have no issues with the vendor (hp), but we do have the following issues (could someone at Redmond please take a note?)

  1. Things are tiled. So where is everything? The tiles are mobile and keep on changing
  2. You're working on an application: typing, browsing, whatever... After all it's a computer, while all of a sudden it decides to restart. 
  3. We have 8 Giga Ram, 1 T hard drive, latest intel centrino, and the latest restart (because we were forced to upgrade to 8.1) lasts for an hour. Of course it decided to restart while we were watching a webinar.
Therefore, I would not recommend you to purchase a Windows computer. Try doors computer as they are better, and they do not restart without asking for permission first or getting stuck at 88% applying PC setting. It is not a computer to be used by your grandmother, or any person who gets easily frustrated.

This post was written on my iPad.

17 December 2013

the query could not be processed query canceled press f9 to restart the query and display data

If you get the above error, or the error:
The query could not be processed: o The data provider didn't supply any other error information
While browsing in you SSAS cube browser, 
I highly recommend the classic solution:
Log off and then log on again.

27 November 2013

Calculating the Week Number for the Australian Financial Year

In Australia, the Financial Year Starts at the 1st of July.
In my cube, they wanted to browse by the week by of the Financial Year.
I've created two new columns: one for the Week of the Year, the other for the Week of the Financial year.

While the first column is easily populated

UPDATE dimDate
SET WeekYear = datepart (ww, myDateFormat)


The second one is a bit more complicated:
The year start at 1/7/2013, so I want that week to start as 1. So I need the WeekYear - week number of 1/7/2013.
I added 1 because we start at 1, not 0.


UPDATE [dimDate]
WeekFinancialYear = WeekYear - DATEPART (ww, CAST (CAST([CalanderYear] AS CHAR(4)) + '0701' AS DATE)) + 1
WHERE [month] >=7


But that's for months in the beginning of the financial year, months that their number is bigger than 7.

Otherwise, Just add 26 (this is the number of 1/2 a year).


UPDATE dimDate
SET WeekFinancialYear = WeekYear + 26
WHERE [month] < 7






(BTW if you're looking for a job here down under, between the last week of June and the First week of August then you're quite hopeless. Or Week 52 till week 5 of the Financial Year!).

15 November 2013

Getting empty values with Sum, Left Join and Inner Join

I'm now working on a simple Training Record database. It's a bit of a downgrade from BI. It's a database with basically 3 tables:
Learners (=the employees in the company)
Courses
Participation (which learner took which course when).

My architecture was based on SQL server as the back end and MS-access as the front end. It makes the database robust and the front end design quick (though you have to pay a price for that, and about that in another post). Another tip is if you don't get the result set that you want running a query in MS-Access it's always easier to investigate the situation on the SQL itself.

The Participation requires the following fields: Learner, Course AND CPD, which is the number of points you get from this course. It could be less than the number of points that the Course acquires.

Sounds simple enough, doesn't it? Well, you'd be surprised how many problems can one encounter with a simple database like that.

Business asked for a simple request: when they see all the Learners, they want to see those without any courses as well. The problem is outlined below:



If you run the following query:

SELECT LearnerList.FullName, Sum(dbo_Participation.CPD) AS SumOfCPD, LearnerList.id
FROM LearnerList LEFT JOIN dbo_Participation ON LearnerList.[id] = dbo_Participation.[Student]
WHERE (( LearnerList.id)=5 Or ( LearnerList.id)=6 Or ( LearnerList.id)=7 Or ( LearnerList.id)=34)
GROUP BY LearnerList.FullName, dbo_Participation.Student, LearnerList.id;


That gives me all the required participants, including those who has NULL as sumOfCPD

FullName            SumOfCPD  id
Amanda Smith         5.75        5
Amanda Jones         24.00       6
Catherine Smith NULL     7
John Smith          NULL      34



BUT, if I try to manipulate it to a certain time range, those didn't take any courses (or in SQL: they're SumOfCPD is NULL) disappears:

SELECT LearnerList.FullName, Sum(dbo_Participation.CPD) AS SumOfCPD, LearnerList.id
FROM LearnerList LEFT JOIN (dbo.Participation
INNER JOIN dbo.course ON dbo.Participation.course = dbo.course.id)
ON  LearnerList.[id] = dbo.Participation.[Student]
WHERE
(dbo.course.Date)<='20131115'
And (dbo.course.Date)>='20130701'
AND
(( LearnerList.id)=5 Or ( LearnerList.id)=6 Or ( LearnerList.id)=7 Or ( LearnerList.id)=34)
GROUP BY LearnerList.FullName, dbo_Participation.Student, LearnerList.id;


FullName            SumOfCPD  id
Amanda Smith         5.75         5
Amanda Jones         24.00        6


If you move the condition of the "Where" clause back to the "Join" part, that sort out the problem:

SELECT LearnerList.FullName, Sum(dbo_Participation.CPD) AS SumOfCPD
FROM LearnerList LEFT JOIN dbo.Participation
INNER JOIN dbo.course ON dbo.Participation.course = dbo.course.id
AND  (dbo.course.Date)<='20131115' And (dbo.course.Date)>='20130701')
ON dbo.student.[id] = dbo.Participation.[Student]
WHERE
(( LearnerList.id)=5 Or ( LearnerList.id)=6 Or ( LearnerList.id)=7 Or ( LearnerList.id)=34)
GROUP BY LearnerList.FullName, dbo_Participation.Student;


Results:
FullName            SumOfCPD  id
Amanda Smith         5.75         5
Amanda Jones         24.00       6
Catherine Smith NULL     7
John Smith          NULL     34

Running the above script in MS-Access would give me the "Error in the FROM clause". The above script run directly from the SQL.
Another option it to use the "NOT EXIST", which exist in MS-Access.
In this example, as I created the SQL as a string in the VBA engine, I didn't want to have the Date as a parameter of the JOIN, as I didn't know how Access would handle it (I wouldn't take a wild guess saying it wouldn't, would I?)


SELECT T1.FullName, T1.id
FROM LearnerList  T1
WHERE  NOT EXISTS
(SELECT T2.FullName,
Sum(dbo.Participation.CPD) AS SumOfCPD
FROM dbo.LearnerList  T2 LEFT JOIN (dbo.Participation
INNER JOIN dbo.course ON dbo.Participation.course = dbo.course.id)
ON T2.[id] = dbo.Participation.[Student]
WHERE
(dbo.course.Date)<='20131115'
And (dbo.course.Date)>='20130701'
AND ((T2.id)=5 Or (T2.id)=6 Or (T2.id)=7 Or (T2.id)=34)
AND T1.id = T2.id

GROUP BY T2.LearnerList , dbo.Participation.Student)
AND
(T1.id = 5 OR T1.id = 6 OR t1.id = 7 OR t1.id = 34)


It's important to note that correctly, if run in SQL Server, you'd get the warning of:
Warning: Null value is eliminated by an aggregate or other SET operation.
For all of the above queries except for the "not exist" one.

In MS-Access, on the other hand, even creating a sub-query wouldn't help. You'll get and "ODBC - Call Error" just from the LEFT JOIN. In INNER JOIN you wouldn't get any error.

And last but not least: as this had to be read as a report, and when you do the following

Dim RS As New ADODB.Recordset
'Execute the code to set the RS
Set Reports("rpt_participation_summary").Recordset = RS
You get "You can do this type of action only in an ADP" I just used "SELECT...INTO "a table which is "temporary" on the SQL server. Ah, the joys of using Access! remind me NEVER, but NEVER to do it again!

03 October 2013

Ethics for Developers

Ethics for Developers? Does this cow even exist? Well yes, it does, and I just thought to be the first one to write about it.
Why? Because Computer Programmers /Developers work with people. And when you work with people there's not only a discussion of what's legal, but of also of things that may be legal, but could get you fired. And no, I'm not talking here about misusing the Printer. Or the Internet. I'm talking about some other stuff...

The first issue when we discuss ethics, is the burning issue of documentation. Best practices do cover that (over 126 Million result in Google!), and the reason is that it is pure ethics. Give meaningful names for your functions and parameters, and comment everything which isn't obvious. No need to write documentation just for the sake of writing. Follow the book Code Complete for once (960 pages!), and your company guidelines.
Always code as if the guy who ends up maintaining your code will be a violent psychopath who knows where you live.  - Martin Golding
Another part of Documentation is: do not use less known feature of your programming language, as this way you're locked to the project in order to achieve maintenance. No 'goto' in C, No pointers to functions in C++, no your-own-inventions Design Patterns. If you're reaching in your design the case of "Multiple Inheritance" (Outside of COM) "Private Inheritance" or other weird functionality in C++ you should seriously re consider your design. Maybe it is the best practice for this particular situation; more likely you're way off track.
I would add to Documentation the clause: Do not use undocumented functions. Documented function is a legal term. It's a feature that is sure to stay between one version of the product (SQL, programming language, running environment, operating system) to another. Undocumented function wouldn't. You're very likely to crash your product once the next version comes out. So don't use undocumented functions. Whenever I read a forum post recommending the use of undocumented function I shy away from that. Surely there's a "legal" way (I always find out there was one).
Another issue is hacking. You could say that Hacking is BAD, but I'd claim that Hacking (as long as it's not meant to cause harm) is actually GOOD. More often than not it will tell you about the system that you've been working on more than any documentation.
As long as you make sure that you report the results of your search to your boss, I don't see anything wrong with that.Seriously. Unless you travel to the "illegal" area.As long as it's not for profit, you can probably still get away with it.

But there are other issues that you may have missed but I've encountered as an end user. And it wasn't a pleasant experience.
Beware of unfunny jokes!
Exception Handling. In a nice little educational computer game that my daughter once received as as freebie, whenever there was an error instead of just dumping you outside of the game, the computer programmer probably recorded himself making a scary laugh with some sound affects. So my 5 years old refused to touch it. Pity.
General QA - acknowledge your testing
You need to create a test user? Call it "Test User". You can get away with "Darth Vader", after all, you're probably a geek.. You can't get away with "Fat Cow". It's unacceptable. The name will probably stay in the system, and somebody (who may or may not be horizontally challenged) will find it offensive.
You need to send a test email? say "This is a test email". If the test goes wrong (a possibility, after all, it is a test) and it is being sent out. If it goes to the wrong person it could be very embarrassing.
and what do you think is Ethics for developers and not being regarded as such? Please let me know via the comments below!

30 January 2013

How To: extract from an undocumented database

Here's the problem which you encounter:
There's a database in your company that you need to extract data from. They  want to make that database redundant (and everybody mumbles around you "it's about time!") and you, as the database-know-how, required to create a report with the required fields.
I've done this daunting task twice: first, for a front end that run on Win 95 (in 2008!) That database was on a Syabase database, and believe me, that line isn't even on my CV. The second time I run it from MS-Access, and that was a way more pleasant experience. Those database had couple of things in common, and the most obvious one that they were shelf products, not custom made, and therefor had many tables that weren't actually in use.
And these are the steps I took:
First, ask your Business Analyst (or whomever had given you this task) which fields they need. They can usually give you at least one line of data, some names, etc, which could give you your reference point for investigation.
The second stage for me was to migrate the data to MS-SQL. This stage isn't strictly necessary; after all, if you can query one database you can query them all. But using my own local version has the benefit of (a) there's not a chance I'll mess up the data and (b) I'm using the SQL version I know well with its new features, and that enables me to focus on the problem, not on the differences between different SQL versions.
Now it's time for querying, but before that, a little script could help.
How do you get the size of all the tables in a database?
SELECT 
    t.NAME AS TableName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnuasedSpaceKB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, p.Rows
ORDER BY 
    t.Name
The benefit of this script is clear: in a shelf database there could be heaps of tables that are not even in use. When you start investigating you don't need those. From my experience, start with the second largest table. The largest one is usually a log, and doesn't usually contain any necessary information. Of course you can ignore all the ones that have 0 data or only one line...

And now for the investigation itself. Look up that name. Look up those details. Try to locate foreign keys and indexes. That database was written by a human who has logic: how does that person's logic works?
Sure enough, you'll be able to present some results for your Business Analyst. They will probably be very happy with your results, and only have some comments. Modify the original script, and believe me, they'll be happy. Very happy, even.

09 January 2013

No more Messenger (or Marital Crisis)

Microsoft had announced today (January 9th, 2013) that it's going to kill our very dear and old friend: the Instant Messenger (IM), in favor (or favour, depends on your locale) of Skype. Well, Skype has 31 million users, while IM has 330 million users, though we cannot tell if all of them are active or not - apparently those numbers are not published.
One of the main differences between Skype and IM is that Skype is a peer to peer technology, in which its only access to a server is to confirm the password on login; the rest of the time, it's P2P, encrypted, and basically better and safer to use than IM. IM  goes through a server, and all your posts and messages pass through a server as well.(That means that publishing those numbers shouldn't be a problem for Microsoft who owns both technologies, but it's theirs and it is theirs choice).
Nevertheless, it's the use that counts: the first thing we think of when we hear "Skype" is "Internet Telephony" while with IM it's "quick chat" (though you can use both technologies for both), and that's why  I was quite devastated to hear that from my Husband (and no, I didn't shot the messenger, ha ha very funny), since I am a worker on a PC and my favourite method of communicating with my husband is via the IM. Since both of us are in the same profession approximately (he's a web developer) I sometimes use it to send him a quick link or to ask him a tech question... Romantic, I know.
Unfortunately, installing Skype on my machine without the proper set of of headset seems a bit out of order, as I cannot receive or accept calls (I work in a financial institution, remember?)

So therefore let me cry out load: Microsoft! Don't give up! We all love our IM! Don't make it a thing of the past! Alternatively, send me a headset!