What do we study in Information Systems Grad School? If there's a single topic that comes up in every class, it's databases. If we haven't memorized the first three normal forms by now, we haven't learned much. While few of us will bother going into Boyce-Codd Normal Form, 4NF and 5NF, every specification for a system we write that has a database needs an entity relationship diagram. An ERD is a visual representation of your data model, and your data model is probably the single most important part of any system you design. A good data model will survive several major versions of your software; a poor data model will make your system useless. Thus, we spend a lot of time doing data models and documenting them with ERDs.
As much as I love Visio, drawing the things from scratch is somewhat tedious. It's much easier to design and test in Access. (I have it on good authority that even elite Oracle DBAs who hand-tune Solaris for better performance will design and test in Access just because it's easy.) So what do you do when you have a decent test DB in Access and you don't want to diagram every little change in your masterful Visio ERD? Reverse engineer.
In Visio, it's fairly easy, but there are a couple of spots where it doesn't behave as nicely as it should. I'm going to refer to Visio 2007, still in Beta and free for the download and registration. Visio 2003 is almost the same. Visio 2003 Enterprise Architect Edition will create the database from your diagram, in case you can design an enterprise DB but don't know how to create the tables in SQL. (Not really someone you'd want touching your SQL server.)
1. Open Visio and select New | Software & Database | Database Model Diagram with the units of your choice.
2. Now that you have a database model diagram open, the database menu will appear. Select "Reverse Engineer" off of the database diagram.
3. A confusing dialog box will appear. Use it to verify you have the right drivers installed.
4. For Microsoft Access, choose Microsoft Access as your driver, and hit Next.
5. A username and password dialog box will pop up. Unless you've assigned a username and pw to the database, leave it blank and hit OK.
6. Navigate your filesystem and select your database. Ignore the clunkiness and be grateful that you can see filenames longer than 8.3.
7. When you have found your .MDB file, choose it and hit OK.
8. Select the types of objects you would like to import and hit Next. (No, you don't get stored procedures and triggers in Access, but you would in SQL.)
9. Select the specific tables, queries, etc. you want to see in your diagram and hit Next.
10. Select Yes to add them to your current diagram, and hit Finish. (Select no you have a lot of tables, queries, etc.)
11. You should see your tables in the diagram.
12. To add the crow's feet and cardinality, select options on the database menu. (Database | Options | Document )
13. You get three sections to change here: The General tab covers symbol sets: IDEF1X or Relational, Conceptual, Physical, both, or names based on symbol set. The table tab lets you display keys, indexes, non keys, and the IDEF1X optionality 0. The relationship tab lets you display relationships (duh), crow's feet, cardinality, and referential actions. You must select cardinality before you select crow's feet. (caridinality gets greyed out when crow's feet is checked.)
14. To update your diagram, select "Refresh Model" on the Database menu...
Now that you can see your information model, you know why it's messed up. That database that your business/organization/department runs on -- it's not in any kind of normal form. Or it has about 100 tables more than you thought it should.
The database people in my office have a debate: were the software engineers just trying to make it impossible to wean your organization off of their support, or were they just bad at information modeling?
Monday, October 2, 2006
Friday, September 22, 2006
Visit my new Educational Technologies Blog
Given that I have a new job in education, I started doing a few entries about recent developments in education and technology. I use the framework of educational technology to answer President Bush's question, "Is our children learning?"
At least it explains the dearth of recent posts here.
At least it explains the dearth of recent posts here.
Thursday, September 14, 2006
Threat Analysis and Modeling Tool, Office Groove Beta
Just when I thought I would stop experimenting with new Microsoft products and hunker down into my new job and my capstone project, I find a couple that will save me time and aggravation: Microsoft's Threat Analysis and Modeling Tool. This dot.Net 2.0 application has a wizard to create for us a CRUD matrix, that will can paste into our system security chapter. A CRUD matrix is simply a table of which users need what kind of access to which tables. It can get more complex if you have column-level security in your database. We don't, so it won't be overly detailed.
The other tool that can save me time, and possibly my group members, is Microsoft's Office Groove 2007 Beta. Like many cool Microsoft products, Groove did not originate at Microsoft. Most people I talk to about Groove don't really get what it does, but I blame that more on bad IT and IS metaphors than anything else. Groove is simply a shared workspace where a team can share documents, discussions, and contacts. When you set up Groove, you can set up one or more shared workspaces. You then add specific files to that workspace. Then all your invited and verified team members can access those documents whether you are online or off. No more uploading to Sharepoint or Blackboard, or whatever. You've got anywhere, anytime sharing, provided you have an Internet connection. I have it on my work computer and my home computer and share documents between them without having to email them back and forth. So far, I have no firewall issues either.
What if you want to secure your data and not have it stored on third-party servers? Use Groove Server, and give more money to Microsoft. Before the Office 2007 launch, both Groove and Groove server are free for the download, registration and product keys required. Try it and at least you'll know what people are talking about. The next big thing is enabling teamwork and collaboration and making the world "flat."
The other tool that can save me time, and possibly my group members, is Microsoft's Office Groove 2007 Beta. Like many cool Microsoft products, Groove did not originate at Microsoft. Most people I talk to about Groove don't really get what it does, but I blame that more on bad IT and IS metaphors than anything else. Groove is simply a shared workspace where a team can share documents, discussions, and contacts. When you set up Groove, you can set up one or more shared workspaces. You then add specific files to that workspace. Then all your invited and verified team members can access those documents whether you are online or off. No more uploading to Sharepoint or Blackboard, or whatever. You've got anywhere, anytime sharing, provided you have an Internet connection. I have it on my work computer and my home computer and share documents between them without having to email them back and forth. So far, I have no firewall issues either.
What if you want to secure your data and not have it stored on third-party servers? Use Groove Server, and give more money to Microsoft. Before the Office 2007 launch, both Groove and Groove server are free for the download, registration and product keys required. Try it and at least you'll know what people are talking about. The next big thing is enabling teamwork and collaboration and making the world "flat."
Friday, August 4, 2006
Services for Unix in Six Easy Steps
After tiring of redoing samba.conf files over and over again, I finally tried out Microsoft's (free) Services for Unix for simple file sharing between my Fedora Core 4 box and servers on my domain.
1. Copy over /etc/passwd and /etc/group to a secure folder on the windows machine where you are going to install SfU.
2. Download and install Microsoft's Services for Unix, and tell it to use password files during the installation process. (This is not a lesson on setting up a NIS domain -- just connecting one Linux box to your Windows server quickly and reliably. SfU installs several other items by default, like Unix Perl and grep; ActiveState Perl is optional. You want NFS Server.) Reboot.
3. In Windows, right click on the folder you'd like to share, click and NFS sharing tab, and select "share this folder."
4. On your linux box, as root, add a line like this to /etc/fstab:
myserver.com:/somefolder /somefolder nfs defaults 0 0
5. On linux, mount /pub
6. cd /pub and ls -la to your heart's content.
There are many security implications for Windows and Linux, like sharing your password and group files, and I'll sniff and trying cracking the passwords later.
1. Copy over /etc/passwd and /etc/group to a secure folder on the windows machine where you are going to install SfU.
2. Download and install Microsoft's Services for Unix, and tell it to use password files during the installation process. (This is not a lesson on setting up a NIS domain -- just connecting one Linux box to your Windows server quickly and reliably. SfU installs several other items by default, like Unix Perl and grep; ActiveState Perl is optional. You want NFS Server.) Reboot.
3. In Windows, right click on the folder you'd like to share, click and NFS sharing tab, and select "share this folder."
4. On your linux box, as root, add a line like this to /etc/fstab:
myserver.com:/somefolder /somefolder nfs defaults 0 0
5. On linux, mount /pub
6. cd /pub and ls -la to your heart's content.
There are many security implications for Windows and Linux, like sharing your password and group files, and I'll sniff and trying cracking the passwords later.
Thursday, August 3, 2006
Business Intelligence Studio: A Wizard for your Data Mart
My grad school project group finished our data mart for class. I learned a few things about SQL Server 2005 Analysis Services, Reporting Services, and Business Intelligence Studio along the way. One of our group members is a SAS programmer, so he provided us with simulated data: attendance records for a theoretical amusement park that included zip code and promotion type, with zip-code ACORN differences. Our idea was that if you came to the park with a coupon, we would know from the bar code where you came from; if you didn't have a coupon, the gate would ask you your zip code, just like at the retail store.
We had six million rows in our fact table, which included dateID, promotionID, zipID, and attendance, giving us three dimensions. We were going more for scalability rather than trying to pile in a lot of dimensions. I took the flat files from our SAS master and imported them into a SQL 2005 database. For some reason, SQL 2005's import tool defaults to a nvarchar(50) type. While six million records isn't much, the records were fixed-size and much smaller, so I was able to stuff them in an nchar type that was small and efficient because it doesn't require an offset column array. Our six million fact table records matched to 5 types of promotion, about 10,000 zip codes, and individual days for 3 years.
Once we created the database, I exited SQL Server Management Studio and opened up a new Business Intelligence Studio project. One difference between Management Studio and BI Studio is that BI Studio does not use SQL authentication. You must use Windows/Domain accounts. There is no sa in Business Intelligence Studio.
Once in BI Studio,you create a data source, just like with any project that involves a database. Then you create a data source view by selecting the tables you want to use and creating a dimensional model diagram. Microsoft calls this their Unified Dimensional Model. While your fact table doesn't need a primary key, your dimension tables do. The arrows should be pointing from your fact table to the dimension tables. I don't know why, but I often get this wrong when I'm creating a new data source view.
Finally, your fact table columns need to reference the right dimensions. Matching these up took a little longer than expected because I had to guess which fields were what, because our SAS genius didn't document which numbers were what. I looked at the 8-digit numbers and guessed wrong. The auto-build cube wizard worked fine, and the dimensions looked OK. Processing the cube failed when it timed out, and the error messages didn't say anything about orphaned fact rows.
Once the columns were matched up correctly, everything worked fine, and the auto-build cube wizard is pretty impressive. If you did everything right, building the cube should give you dimensions. You can create new dimensions if the wizard missed some. The time dimension has many options. At first I set time to be a regular dimension to avoid promblems: is day of week a number or a name? Monday or 1? While this will give you good reports, the order of weekdays and months will be alphabetical. (Which makes be think we should rename all month and day names so they can be alphabetical and still be in order, or re-sort them so that April is the first month and Friday the first day of the week.) Setting the time dimension as a time dimension rather than a "standard" dimension will fix this.
Processing the cube took under five minutes. (You also need apppropriate account permissions to process the cube) If the cube processes sucessfully, you can browse pivots immediately with the browser in BI Studio. Once you see that your dimensions work, you can start connecting to SQL Server Analysis services from Excel or from SS Report Server. (Again, with appropriate Windows Domain accounts.)
Microsoft's BI Studio Cube and Dimension wizards work the way they should: if you have defined your dimensional model accurately and your data are valid, they'll create the right dimensions for you. The wizards are no substitute for a valid dimensional model and they can't fix bad data or orphaned records.
Next: Creating Pivot Table Reports using our cube and SQL Server Reporting Services for IIS.
We had six million rows in our fact table, which included dateID, promotionID, zipID, and attendance, giving us three dimensions. We were going more for scalability rather than trying to pile in a lot of dimensions. I took the flat files from our SAS master and imported them into a SQL 2005 database. For some reason, SQL 2005's import tool defaults to a nvarchar(50) type. While six million records isn't much, the records were fixed-size and much smaller, so I was able to stuff them in an nchar type that was small and efficient because it doesn't require an offset column array. Our six million fact table records matched to 5 types of promotion, about 10,000 zip codes, and individual days for 3 years.
Once we created the database, I exited SQL Server Management Studio and opened up a new Business Intelligence Studio project. One difference between Management Studio and BI Studio is that BI Studio does not use SQL authentication. You must use Windows/Domain accounts. There is no sa in Business Intelligence Studio.
Once in BI Studio,you create a data source, just like with any project that involves a database. Then you create a data source view by selecting the tables you want to use and creating a dimensional model diagram. Microsoft calls this their Unified Dimensional Model. While your fact table doesn't need a primary key, your dimension tables do. The arrows should be pointing from your fact table to the dimension tables. I don't know why, but I often get this wrong when I'm creating a new data source view.
Finally, your fact table columns need to reference the right dimensions. Matching these up took a little longer than expected because I had to guess which fields were what, because our SAS genius didn't document which numbers were what. I looked at the 8-digit numbers and guessed wrong. The auto-build cube wizard worked fine, and the dimensions looked OK. Processing the cube failed when it timed out, and the error messages didn't say anything about orphaned fact rows.
Once the columns were matched up correctly, everything worked fine, and the auto-build cube wizard is pretty impressive. If you did everything right, building the cube should give you dimensions. You can create new dimensions if the wizard missed some. The time dimension has many options. At first I set time to be a regular dimension to avoid promblems: is day of week a number or a name? Monday or 1? While this will give you good reports, the order of weekdays and months will be alphabetical. (Which makes be think we should rename all month and day names so they can be alphabetical and still be in order, or re-sort them so that April is the first month and Friday the first day of the week.) Setting the time dimension as a time dimension rather than a "standard" dimension will fix this.
Processing the cube took under five minutes. (You also need apppropriate account permissions to process the cube) If the cube processes sucessfully, you can browse pivots immediately with the browser in BI Studio. Once you see that your dimensions work, you can start connecting to SQL Server Analysis services from Excel or from SS Report Server. (Again, with appropriate Windows Domain accounts.)
Microsoft's BI Studio Cube and Dimension wizards work the way they should: if you have defined your dimensional model accurately and your data are valid, they'll create the right dimensions for you. The wizards are no substitute for a valid dimensional model and they can't fix bad data or orphaned records.
Next: Creating Pivot Table Reports using our cube and SQL Server Reporting Services for IIS.
Thursday, July 20, 2006
Google vs. the National Security Agency
Apparently, watching Google is now as much sport as watching the NSA, according to the latest in Baseline Magazine. Discovering the way Google solves data-related problems may be more interesting because Google, unlike the NSA, is not encumbered by government contracting procedures and regulations.
Think about it: you can search the web with Google and find files faster than you can when you're looking for files on your own computer using Windows Search. To learn why it takes longer to search a hard disk on your own *#$% computer than the web, read the Baseline story.
The National Security Agency and Google are in the same business, essentially: take a firehose spitting out information and sort it into something useful. Both the NSA and Google keep their collective mouths shut about sources and methods. The NSA has been slightly better about keeping purchases of high bandwidth out of the news, but only because they have an organization advantage of operating outside the traditional business community (assuming Watkins-Johnson is not a normal business).
The Baseline story estimates the number of Google servers at somewhere around 450,000, but you should think of them as a much smaller number of MPP supercomputers. Google initially had trouble because most data centers couldn't deliver enough watts per square foot to power dense server blade environments, so they turned to AMD processors. That's a process of scaling up computing power, and I wonder how the NSA solved the same problem, although I assume they just pumped in more watts for processors and cooling. Those of you familiar with Microsoft's current file system, NTFS, may know that you can set disk cluster size from 4 kbytes to 64 kbytes. Google's file system has a cluster size of 64 Mbytes. Their files are large, and a large cluster size leads to more efficiency. Google has re-engineered kernel, filesystems, and who knows what else for scalability. Did they re-engineer from the ground up more efficiently than the NSA?
Another facet of the Baseline Google story is the office-in-a-box. As a former IT contractor for political campaigns, I had to figure out the cheapest fastest way to set up a computing infrastructure for a field office in, say, Des Moines, Manchester, or Columbia. (Columbia is the capital of South Carolina, for those of you out of the primary calendar loop.) My setup was fairly simple: router, firewall, server (Domain Controller also running DHCP, DNS, and a Global Catalog, of course), printer/copier.
Google has office IT-in-a-box that would put mine to shame. Google also has shipping containers converted into server infrastructures that they can ship anywhere. Baseline implies that the military's IT- infrastructures-in-a-shipping-container exist in Powerpoint only.
As far as the Google vs. NSA operating efficiency battle goes, at least there's more than one career option for deep geeks. I would have a hard time deciding between the two because they both offer serious computing power. As far as ethical considerations go, both have pluses and minuses. The NSA doesn't make money selling advertising; as hard as Google tries, running a business requires some level of compromise to make money. The power of both organizations could be abused. The way things are going in the Intelligence Community, Google will be more secret than the NSA in five years.
What do you think?
Think about it: you can search the web with Google and find files faster than you can when you're looking for files on your own computer using Windows Search. To learn why it takes longer to search a hard disk on your own *#$% computer than the web, read the Baseline story.
The National Security Agency and Google are in the same business, essentially: take a firehose spitting out information and sort it into something useful. Both the NSA and Google keep their collective mouths shut about sources and methods. The NSA has been slightly better about keeping purchases of high bandwidth out of the news, but only because they have an organization advantage of operating outside the traditional business community (assuming Watkins-Johnson is not a normal business).
The Baseline story estimates the number of Google servers at somewhere around 450,000, but you should think of them as a much smaller number of MPP supercomputers. Google initially had trouble because most data centers couldn't deliver enough watts per square foot to power dense server blade environments, so they turned to AMD processors. That's a process of scaling up computing power, and I wonder how the NSA solved the same problem, although I assume they just pumped in more watts for processors and cooling. Those of you familiar with Microsoft's current file system, NTFS, may know that you can set disk cluster size from 4 kbytes to 64 kbytes. Google's file system has a cluster size of 64 Mbytes. Their files are large, and a large cluster size leads to more efficiency. Google has re-engineered kernel, filesystems, and who knows what else for scalability. Did they re-engineer from the ground up more efficiently than the NSA?
Another facet of the Baseline Google story is the office-in-a-box. As a former IT contractor for political campaigns, I had to figure out the cheapest fastest way to set up a computing infrastructure for a field office in, say, Des Moines, Manchester, or Columbia. (Columbia is the capital of South Carolina, for those of you out of the primary calendar loop.) My setup was fairly simple: router, firewall, server (Domain Controller also running DHCP, DNS, and a Global Catalog, of course), printer/copier.
Google has office IT-in-a-box that would put mine to shame. Google also has shipping containers converted into server infrastructures that they can ship anywhere. Baseline implies that the military's IT- infrastructures-in-a-shipping-container exist in Powerpoint only.
As far as the Google vs. NSA operating efficiency battle goes, at least there's more than one career option for deep geeks. I would have a hard time deciding between the two because they both offer serious computing power. As far as ethical considerations go, both have pluses and minuses. The NSA doesn't make money selling advertising; as hard as Google tries, running a business requires some level of compromise to make money. The power of both organizations could be abused. The way things are going in the Intelligence Community, Google will be more secret than the NSA in five years.
What do you think?
Monday, July 17, 2006
Java vs. Python/Plone vs. PHP/XOOPS vs. J2EE vs. .Net
eWeek had one of the coolest lab tests I've seen in a while last week: they tested portal applications for speed on different architectures. This is exactly the kind of testing I'd do in my little lab if I had the resources.
eWeek tested JBoss on Windows, Plone on Windows, XOOPS on Windows, Plone on Linux (SUSE), XOOPS on Linux (SUSE), JBoss on Linux (CentOS), Liferay on CentOS, and Sharepoint Portal Server on Windows. Their results were mixed: .Net had the highest throughput per second in KB, Windows JBoss the highest number of transactions per second, J2EE/Liferay/Linux the highest hits per scond, and JBoss on Windows the lowest page load time. .Net and Windows JBoss were among the fastest portal applications, but the various LAMP flavors did OK, especially if you did something to speed them up. (Like using Zend Technologies' PHP accelerator.)
The main point of all this testing did not indicate that one portal architecture necessarily better than the rest. If your organization has expertise in a specific portal area, then that architecture will work for you. The key is that whatever architecture you choose is what matches your business.
Information Technology is changing, as always. What matters most is not the technology -- it's your understanding of your business and how to make that work faster and cheaper. IT and IS are just tools for your business.
eWeek tested JBoss on Windows, Plone on Windows, XOOPS on Windows, Plone on Linux (SUSE), XOOPS on Linux (SUSE), JBoss on Linux (CentOS), Liferay on CentOS, and Sharepoint Portal Server on Windows. Their results were mixed: .Net had the highest throughput per second in KB, Windows JBoss the highest number of transactions per second, J2EE/Liferay/Linux the highest hits per scond, and JBoss on Windows the lowest page load time. .Net and Windows JBoss were among the fastest portal applications, but the various LAMP flavors did OK, especially if you did something to speed them up. (Like using Zend Technologies' PHP accelerator.)
The main point of all this testing did not indicate that one portal architecture necessarily better than the rest. If your organization has expertise in a specific portal area, then that architecture will work for you. The key is that whatever architecture you choose is what matches your business.
Information Technology is changing, as always. What matters most is not the technology -- it's your understanding of your business and how to make that work faster and cheaper. IT and IS are just tools for your business.
Subscribe to:
Posts (Atom)