If you wish to create a custom theme in a WSS installation and wanted to install that theme into your Sharepoint installation, you will realize that there is no quick and easy one click method to do it. Below are the steps to illustrate what has to be done to upload a new theme to the WSS installation

1. After creating your theme, copy the content of the theme into the "C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\THEMES" folder
2. Open SPTHEMES.XML located in "C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\LAYOUTS\1033"
3. Add another child node to the <SPThemes> tag to reflect the name of your new theme
4. Capture the screen of the new theme and save the file in "C:\Program Files\Common Files\Microsoft Shared\web server extensions\12\TEMPLATE\IMAGES". The name of the file is suppose to be the same as the name of the thumbnail as highlighted in point 3
5. Run iisreset.

And you're done!

While installing WSS 3.0, I came across another strange error when I try to start my Search Service in the server farm. For some reason WSS refuses to acknowledge that the machine is the indexer. The setup was on single a 64 bit machine installed as a server farm. After reinstalling and re-confirming that everything is where it should be, I decided to try the most unlikely reason why it fails.

I used the server name instead of its IP address when specifying the configuration. Strange enough, WSS stops denying the search service.

Just a couple of weeks ago, I was tearing my hair out trying to meet a really tight timeline and at the same time trying to solve a technical issue which in my opinion is a total waste of time. If anyone of you had ever tried to make SSIS and Oracle work on a 64 bit machine, I am sure you know what I was referring to.

Fortunately we have people like Steve McHugh and Greg Galloway, who both wrote some excellent how-to articles addressing this issue. I've now grown so dependent on these articles (especially Greg Galloway's) that I'm ripping it off to put on my own blog site in case he takes the article down one day.

"If all you want to do is run scheduled packages under the x64 version of SSIS, you can just do step 2. If you want to develop packages on this box or run the SQL Server Import/Export Wizard, then you need to do all the steps.

1. Download the latest 32-bit drivers and install them. We would suggest Oracle10g Release 2 ODAC.
2. Download the latest 64-bit drivers and install them. We would suggest Oracle10g Release 2 ODAC (64-bit) 10.2.0.3 for Windows x64.
3. Because you have several drivers installed, we suggest you add a TNS_ADMIN environment variable which says "C:\TNS" and then move your tnsnames.ora and sqlnet.ora files to that one "C:\TNS" directory. You can add a TNS_ADMIN environment variable by right clicking My Computer on your desktop, choosing Properties, flipping to the Advanced tab, clicking the Environment Variables button, and adding that variable.
4. Note: During one install of the drivers, it failed because it couldn't find gacutil.exe. If you get that failure, uninstall the Oracle driver you just tried to install, then copy gacutil.exe and gacutil.exe.config from <C:\program files\Microsoft Visual Studio 8\SDK\v2.0\Bin> to <C:\program files (x86)\Microsoft Visual Studio 8\SDK\v2.0\Bin>. The most recent time we did the installs, this was not a problem, so maybe Oracle has fixed this issue.
5. All 32-bit SQL Server applications start under the "Program Files (x86)" directory. You need to fix them so they start in the "Progra~2" directory. The following steps will need to be rerun after every subsequent SQL service pack is installed.
   1. To fix the SQL Server Import/Export Wizard so you can launch it from Management Studio, fix the reference to it in the registry by running the "SQL import-export wizard on 64-bit server.reg" file that I have included in the ZIP file below. (That registry file fixes the HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\MSDTS\Setup\WizardPath registry path.)
   2. The shortcuts to SQL Server Management Studio and SQL Server Business Intelligence Development Studio need to be fixed. Unfortunately, you can't just change the shortcut, because Windows automatically expands the "Progra~2". Instead, create a bat file that launches the EXE, then change the shortcut to point to the bat file. These bat files and shortcuts are included in the ZIP file below. Basically, those bat files look like:
      start /B "C:\Progra~2\Microsoft Visual Studio 8\Common7\IDE" "C:\Progra~2\Microsoft Visual Studio 8\Common7\IDE\devenv.exe"
   3. When you double-click a .sln file, the path it uses for Visual Studio needs to be fixed. If you only have Visual Studio 2005 (i.e. SQL Server 2005) installed on this server, you can make the following change. Open the C drive… Go to the Tools menu… Folder Options… Flip to the File Types tab… Type in SLN to skip down to the SLN file type… Click the Advanced button… Highlight the "Open" action… Click Edit… Change the path to say:

      "C:\Progra~2\Microsoft Visual Studio 8\Common7\IDE\devenv.exe" "%1"

      Consider fixing any other file extensions you wish to double click which should launch 32-bit processes.

   4. Fix the PATH environment variable by changing any reference that says "C:\Program Files (x86)\Microsoft SQL Server\" to "C:\Progra~2\Microsoft SQL Server\". And change any reference to "C:\Program Files (x86)\Microsoft Visual Studio 8" to "C:\Progra~2\Microsoft Visual Studio 8". Environment variables can be edited by right clicking My Computer on your desktop, choosing Properties, flipping to the Advanced tab, clicking the Environment Variables button, choosing the Path system variable, and clicking Edit.
   5. Fix the shortcuts for other 32-bit applications that need to connect to Oracle (such as Toad) as outlined in step B above.
6. We had trouble when our master SSIS package launched child packages out-of-process if those child packages needed to connect to Oracle. We decided to change those Execute Package Tasks to run in-process, and we didn't investigate further.
7. You will probably have to set the AlwaysUseDefaultCodePage property to True on the OLE DB Source components in your data flow tasks that pull from Oracle.

Download a oracle x64 fix.zip which contains a couple of shortcuts and the .reg file used in step 5a above.

Miscellaneous Note: To setup a linked server to Oracle, review this post by Max Oleznyuk."

It also helps to know (from Steve's article) the following

• Visual Studio (BIDS) is 32 bit
• SQL Server Management Studio is 32 bit
• dtexecui.exe is 32 bit
• dtexec.exe comes in 32 bit and 64 bit
• Oracle Provider comes in 32 bit and 64 bit
• SQLAgent is 64 bit

If Greg or Steve is reading this post, thanks a lot for making the installation/configuration so easy. Good on ya!

SCD (Slow Changing Dimension) has been a very common scenario for data warehouses and there are many established and proven techniques that can be used to solve the problems. This includes the usage of SCD Type II (Insert only) or SCD Type I (Update only) methodology. When we start to look at designing a system that caters for real time information, it's very common that we will meet the same problems, with the exception that the dimension doesn't change slowly, but rapidly. Moreover, they normally are very large in size.

Adopting the SCD Type II methodology will often mean that we will need to insert new records into the same dimensional table. This is fine and works well if the table is not too large and that the changes doesn't happen frequently. However, if the table is some multi-million size large and changes happens every few minutes, we will need a better solution than storing them in one large SCD table.

The solution to this is to break out that dimensional table into sub dimensional tables which are rapidly changing in nature and add another foreign key into the fact table to reference that key. This technique can be applied to any dimensional attribute or to any more dimensions so that loading and data insertion can be kept to a manageable size.

For example, if there's a Customer Dimension table

Customer Key        Name     Country
1001             John USA

If John changed his state to Australia, we can justify this as a SCD Type II (for historical recording) and insert the table so that we have

Customer Key        Name     Country
1001             John USA
1002            John     Australia

and in the fact table we can have something like

Fact Table
Fact_ID            Customer Key    Field1
1             1001        $10
2            1002        $10

and there we have our typical SCD Type II. However, if this Customer Dimension table is multi-million rows in size with changes happening every few minutes, and that you need to have an ETL process that runs it for every 15 minutes, it will an issue if this table is to be updated as a normal TYPE II. The solution is to split it up into manageable size.

Customer Dimension
Customer Key    Name
1001        John

Country Dimension
Country Key    Country
2001        USA
2002        Australia

Then the fact table will be

Fact ID    Customer Key    Country Key    Field1
1    1001        2001        $10
2    1001        2002        $10

With this technique, you can then further split your dimensions into different groups and have the database manage a smaller group of data insertions for fields that change and fields that doesn't change as often. On top of that, you can subgroup the attributes to record ranges instead of absolute figures.

However, even with this technique, if and when possible, a denormalized table will always be better than an over-normalized tables schema in a data warehouse

While there have many takes and talks about Business Intelligence 2.0, which is really BI working in a SOA environment, the technology behind Business Intelligence hasn't seems to mature to the state where we can really have real time performance without sacrificing some traditional BI systems characteristics. SQL Server 2005 did a great job in getting us closer to Real Time Business Intelligence through the introduction of Proactive Caching as well as SSIS's dimensional destination. However, things get tricky when we try to set up a system with the following requirements

• Real Time data
• Historical archives
• Large SCD Dimensions
• Very Large Data Warehouses

Part of my consulting work includes designing architectures for Business Intelligence and SOA systems. After some considerable research on 'Best Practices', the options are basically between these 2

UDM to connect directly into the OLTP relational database and into SSAS

While this will enable us to be truly real time with proactive caching, there is a big catch which cannot be ignored. It is obvious that if a system is set up this way, we will have the following issues to worry about

• No data cleansing and data quality check
• No dimension versioning with SCD Type II
• No surrogate keys
• Unable to hold historical data unless OLTP system archives them
• Slower queries

This almost contradict the entire philosophy behind having a Business Intelligence system as we're basically going through a big round to connect for traditional reporting with the advantage of being able to connect multiple data sources in the UDM for 'single version of truth'

UDM to connect into Datawarehouse and into SSAS

While this sounds like a good idea, present technologies haven't gotten us to a comfortable level with this arrangement yet. What this mean is that we will not be able to achieve a real 'Real Time' scenario. The shortcomings are as follows

• Will ETL take too long to process and hence unable to catch up?
• Will there be competition on hardware resource resulting from ETL and SSAS processing competing with each other, and hence increasing costs due to the need to have separate installations of each?
• Real Time scope will have to be 'lengthen'
• More complex system development and maintenance due to the need to store historical partitions and current partitions

Solution

While there is really no ideal solution to this (yet, I hope someone might correct me here!). There are a few possible workarounds so that you can get your job done. You can either

• Adapt option 1 if the requirements allows (which is quite likely)
• Split the project into 2 sub projects
  • Real-time system without historical audit and X% tolerance for unclean data
  • Historical data warehouse with ability to report of reports coming from the Real-time system.
• Adapt option 2 and work with your project sponsors and stakeholders on the definition of 'Real-time'
  • Work on the slightly more complex environment and try to simplify it as much as you can

I'm actually more inclined towards the last option as in my experience; it is easier to justify system limitations to business users, then trying to explain why data doesn't reconcile with 2 different systems. I hope this blog entry will give someone out there with some insight with the available options currently.

Is this even possible? I've been trying to design a BI system on SQL Server 2005 with strict constraints to maintain a 'single version of truth'. One of the hardest things in this system is to provide Real Time data views with historical audit. To make things more exciting, many of the dimensions are slowly changing dimensions.

Proactive Caching in SSAS seems to address the issue of Real Time BI in a good sense. We're actually able to get pretty good performance on a ROLAP engine thanks to this neat feature. However, it seems like we're trying to merge two entirely different things here when we need to have a Real Time Business Intelligence System which is capable of capturing historical changes and do some dimensional versioning. Since proactive caching really works its charm when we do a direct connection to the OLTP relational database, we will be really missing out on the ETL / Cleansing layer. In short, we will have no area where we can do SCD Type II transformation.

There are other ways in which we can use SSIS with dimension destination, but I doubt if this approach will yield good performance and results.

There are probably some other ways in which this can be achieved, such as splitting the system into 2 different sub systems, one to cater for Real Time needs, and the other for historical data analysis. I'm not too happy with this as well, as it takes away the 'single version of truth' away from a Business Intelligence system.

Comments anyone?

I thought this is worth noting. This is the way to generate 1 million records within 1 second in SQL Server 2005. I'll have the code pasted here so that I can have a reference the next time I need to use it. As Namwar Rizvi said, this code is proposed in SQL Server 2005 TSQL Querying by Microsoft press.

--Declare a variable to hold the
--count of rows to be generated

Declare @p_NumberOfRows Bigint

--We need 1 million rows

Select @p_NumberOfRows=100000;

With Base As
(
Select 1 as n
Union All
Select n+1 From Base Where n < Ceiling(SQRT(@p_NumberOfRows))
),
Expand As
(
Select 1 as C
From Base as B1, Base as B2
),
Nums As
(
Select Row_Number() OVER(ORDER BY C) As n
From Expand
)
Select n from Nums Where n<=@p_NumberOfRows

--Remove Maximum Recursion level constraint

OPTION (MaxRecursion 0);

It has been a while since I last posted. Thanks to all the changes that's happening in my life. New job, new role, new technology and a new laptop. Anyway, so I heard that Word 2007 supports blog publication, which is a really neat feature.

I got it set up with the following few steps

1. Select publish to Blog
2. Select 'others' for blog type
3. Select MetaWebLog
4. Type in 'http://geekswithblogs.net/(your blog)/services/metablogapi.aspx'

There you go, start blogging away. Cheers.

Here's a pretty decent comparison done on SQLServer against MySQL. I'm not a big fan of MySQL, but recently I got into a project which requires me to evaluate the possibility of using MySQL on a enterprise level datawarehouse.

As horrific as it may sound to me, I need to convince with technical proof, that MySQL is not suitable for enterprise level datawarehousing.

If there's anyone out that who's already using MySQL on an enterprise, it'll be great to hear from you!

It took me a while to come to this blog post from Bart. He wrote a very simple and easy to understand article on how you can invoke a workflow engine from a IIS / Self Hosted WCF service.

This is very useful from the layered architecture perspective since most service-oriented systems will not have the presentation layer calling the workflow engine directly. More often than not (depending on the complexity of the system), you will want to inject a communication service layer in the middle to handle the Workflow call.

Great article there, go read it if you're into this kind of architecture.

I was writing a proof of concept which uses WCF, WF and ASP.Net 2.0 and across some problems when I tried to call a WCF service from the web application client. Turned out that I spent an hour wasted trying to debug my application because I did not place the proxy.cs file into the App_Code of the web application.

I had it documented down and wrote a simple and straight forward tutorial on how you can use a web application to access and call a WCF Service. The article is uploaded into the article section here

Hopefully this tutorial can be useful for someone.

I was pretty excited over Apple's new iPhone after witnessing its slick interface in YouTube, and decided that my dependency on the Pocket PC driven by the Windows Mobile platform will have to go.

That is until I see this.

Somebody actually made an iPhone replica sitting on the Pocket PC. Very impressive. It's a shame that the video has been taken down on YouTube due to pressures from you-know-who. The interface replica was written using a PPC development tool called PPL (Pocket PC Language) by ArianeSoft.

Now that a look at the new Samsung Ultra Messaging on Windows Mobile 6. In my opinion, it looked very similar to iPhone's interface style.

Now for those of us who has been fooling around with WPF, we're well aware that WPF will serve as a very good SDK for development of applications look and behave like iPhone, or even better. I wonder if Microsoft is already looking at making Windows Mobile 6 capable of running WPF applications?