This simple design, popped up in my inbox today asking for advice…
What you need to understand about this diagram is that there is a concatenated key (keys are in BOLD) on each of the orange dimension tables, with one part of that key being CompanyID. The exception is dCompany which is just keyed on CompanyID. You can imagine that this is a ledger fact table for multiple sub companies in a large organisation (BTW there is a time dimension as well but I want to keep the post simple).
I can understand that each product, location and so on belongs to particular company and that there might be occasions when you might need a complete list of the locations, cost centres etc for that one company. This design allows each of the dimensions to yield that list in a simple 2 table query (by joiing directly to dCompany on CompanyID).
The fact table has no redundant columns because even CompanyID is needed to link to the company dimension.
So all I would do is add in surrogate keys, i.e. a key with no relevance to the data in the dimension which would be the primary and clustered key of each of the dimensions..
For example in dProductGroup the ProductSK of 1 is just a made up number – it might refer to CompanyID4, ProductID20, rather than choosing ProductSK on some transformation of the two business keys. This will make queries faster and speed up cube processing if you plan to put analysis services on top of this. In my projects I nearly always use surrogate keys and I simply use the identity property on the column that will be the surrogate key on the dimension to do this, reserving 0 for a an unknown row e.g. unknown Location.
Whether or not it’s worth doing this is going to depend on 2 factors:
A final thought is that the design in the second diagram will also reduce the size of the fact table in SQL Server 2005 and standard edition of SQL Server 2008, but data compression in SQL Server 2008 enterprise edition will remove this advantage.
My first introduction to Access was a painful experience, not because of the product itself but the way it was used, so it was mixed feelings that I read up on the official Access blog what it will look like in Office 2010.
Yippee it’s going to be 64 bit , so it will be easy load data form it and excel 2010 into 64bit SQL Server.
Oh [insert expletive here]! users are going to be able to create really huge database outside the control of the IT department, that is until they break.
The front end will have the latest office treatment and for me this is where Access shines , forms data logic and reporting all in a box. They have also done all sorts of other thing like putting in trigger like functionality
Another interesting area will be how all of this will work with SharePoint (itself a whizzy front into a database) but that will have to wait until the SharePoint conference in October and so you probably won’t see that functionality in the initial technical previews.
If all this is making you wonder about the future of the lower ends version of SQL Server, even the lowliest version of SQL will still be a better in a variety of ways.
.. albeit without the built in front end.
Of course Access isn’t in every version of Office, but the kind of users who love this sort of tool often have ways and means of purchasing what they need (they usually have the cheque books!). So don’t assume this user friendly tool won’t turn up on a desktop near you. SharePoint integration might mean that the more sophisticated users will park their data on centralised servers but if they loose their data or they run into trouble then who are they gonna call.. the dba! So you might want to keep an eye out for what the Access team are up to whatever your own views are about how good it is.
One of the fundamental dichotomies in development is the relational world of the database and the object orientated world of services, which sadly spills over into developers and DBA’s not always being the best of friends. Developers don't always see the need for a relational database, and the expense of coding to use one is a frustration even if the database engine itself is free.
I picked up this article on the NoSQL movement (I track SQL on tweets Twiiter) and it got me thinking would the the world in general and the IT industry be better off without it?
So what does a true relational database give you that you can’t get anywhere else:
Of course not every system wants or needs these capabilities, but the image of a database as a safe haven for data has seen their use extended into all sorts of areas where another data storage engine might better meet this purpose. A good example is Microsoft Exchange which doesn’t use SQL server.
Transactions can also make web sites appear slow as the database has to commit, and in the world of business intelligence I want all of the referential integrity but not much else as my whole refresh of the data warehouse is one transaction as far as I am concerned.
So I am pleased to see some push back against SQL – it definitely isn’t the answer to life the universe and everything, equally the relational database has survived the PC revolution, the millennium, and the internet, virtualisation.
The next big thing for architecture is of course the cloud, and for Microsoft this means SQL Services, part of the Azure platform. What is interesting here is that Microsoft shied away from a REST-ful database and has essentially implemented this service in such a way that it behaves exactly like SQL Server on your PC/Server (BTW I have a short interview on this on TechNet Edge). Why did this happen, essentially pressure form customers. I am also sure one of the many reasons Oracle bought Sun was because of not despite MySQL
So I don’t think it’s time to consign SQL to the recycle bin just yet, but I would be interested on what you think and perhaps I could setup a Live Meeting on this if I get enough interest.
I have done a couple of posts on licensing SQL Server before , but I just got off the phone from my good friend and MVP Simon Sabin and I realised I need to clear up the issues around Report Builder 2 licensing.
Simon has noticed that you can build reports (which are stored as .RDL) files and run these reports in Report Builder 2 on a standalone machine without any reference to a SQL Server:
However this doesn’t get around the fact that you have to be licensed to connect to SQL Server to use these tools , and this is made quite clear (well I understood it) in the licensing screen that comes up when you install Report Builder from the install file..
Microsoft SQL Server 2008 Report Builder 2.0 for SQL Server 2008 PLEASE NOTE: Microsoft Corporation (or based on where you live, one of its affiliates) licenses this supplement to you as part of and for use with your validly licensed copy of the Microsoft SQL Server 2008 software (for which this supplement is applicable) (the “software”). This supplement is part of the “additional software” and the license terms for the software apply to your use of this supplement. You may not use this supplement if you do not have a license for the software. The term “software” does not include Microsoft SQL Server 2008 Express or SQL Server 2008 Express Advanced Editions.
I have to say that even if this wasn’t a license violation it would be a messy implementation with users saving report definitions (the rdl files to a share) with each having it’s own connection string all of which would make for a lot of management, a few security headaches and a rather poor user experience all for the cost of copy of SQL Server standard.
For detailed queries on this or any licensing question please contact Emma Healey (aka “Lady Licensing”)
I haven’t had many problems myself when installing SQL Server 2008 on either Windows 7 or Windows server 2008 r2 but there are a few things to watch out for which I have shamelessly lifted from KB955725:
You should install SQL Server 2008 sp1 as this is the supported verion on htese new operating systems (they included fixes specific to a couple of issues including the fact that Windows 7 doesn’t use the Required Kerberos property to determine if Kerberos is enabled for a network name resource). .Net Framework. You’re going to need the .Net framework 3.5 sp1. If it isn’t there then the SQL installer will do it for you in Windows 7 but it will then restart the installation once this has been done which may look a little odd and confuse you. In Windows Server 2008r2 you should add the application role using server manager, servermangercmd.exe or the new Deployment Imaging servicing & management tool (DISM) prio to installing SQL Server.
You should install SQL Server 2008 sp1 as this is the supported verion on htese new operating systems (they included fixes specific to a couple of issues including the fact that Windows 7 doesn’t use the Required Kerberos property to determine if Kerberos is enabled for a network name resource).
.Net Framework. You’re going to need the .Net framework 3.5 sp1. If it isn’t there then the SQL installer will do it for you in Windows 7 but it will then restart the installation once this has been done which may look a little odd and confuse you. In Windows Server 2008r2 you should add the application role using server manager, servermangercmd.exe or the new Deployment Imaging servicing & management tool (DISM) prio to installing SQL Server.
I mention this because I know it’s easy to be swamped by KB notifications, a lot of you are trying these new operating systems, and because the SQL Server install is very picky when it runs. This is not a moan, if it installs properly it’s working and you can rely on it which is what databases are all about.
I just got this..
Tax Refund Notification
After the last annual calculations of your fiscal activity, we have determined that you are eligible to receive a tax refund of 188.50 GBP. Please submit the tax refund request and allow 6-9 days in order to process it.
Click Here to submit you tax refund request
Note : A refund can be delayed a variety of reasons, for example submitting invalid records or applying after deadline.
HM Revenue & Customs
..and I nearly fell of the chair laughing. The idea of HMRC informing you of a refund is many very unlikely, and doing it via e-mail is stretching credibility to the limit.
Of course it’s another phishing exercise and takes you to a site Romania (which I have removed the link to) and was identified as such by our systems and sent to my junk mail sans links and pictures.
My point is that if it’s too good to be true it is.
A lot of teenagers are on work experience at the moment, and as I don’t have any of my own children I got to be cool uncle for a week and took my nephew Harry to spend a week inside Microsoft. We run a full week course of lectures and practical exercises along the lines of the apprentice, to teach them presentation and influencing skills,as well as basic selling technique. They also got to play with our latest stuff and I managed to blag a new HP TouchSmart PC for Harry to try while he was staying with us. This cool gadget allows some of the surface technology to work in windows 7, although it relies on touch rather than the cameras in Surface to work.
If you haven’t seen one of these you can watch Harry trying it out …
Dell also have the Latitude XT laptop with similar capabilities which would be great especially if (a Big IF) Office 2010/ SQL Server 2008 r2 has special bits in to use this interface for slicing and dicing!