tag:blogger.com,1999:blog-237486842011-04-21T11:02:02.501-07:00SQL Server, Data Warehouse, Business Intelligence, Data Mining and other interesting stuffIbrahim Hafidhnoreply@blogger.comBlogger241100tag:blogger.com,1999:blog-23748684.post-7109328545861792422008-09-22T07:15:00.000-07:002008-09-22T07:18:52.241-07:00

http://ihafidh.wordpress.comWordpress seems to be much faster than Blogger. Plus they have more themes with better designs.

Ibrahim Hafidhnoreply@blogger.com0tag:blogger.com,1999:blog-23748684.post-2313263310224325892008-09-18T09:30:00.000-07:002008-09-18T09:50:32.809-07:00

This is more of a reminder to myself. I can't always remember the commands to show the execution plan in text. It's nice having the graphical interface but when you have a big execution plan then seeing it in text is much easier. Here are the commands:-- simple estimated execution planset showplan_text onGO --*** estimated execution plan with more detail. best to use this ****set showplan_all

Ibrahim Hafidhnoreply@blogger.com0tag:blogger.com,1999:blog-23748684.post-33107998478291099172008-09-04T14:12:00.000-07:002008-09-04T14:52:46.008-07:00

I wanted to execute a batch file in SQL Server Integration Services, and I wanted to utilize two parameters from the Execute Process Task in SSIS: StandardOutputVariable and SuccessValue.The reasons for using these two parameters is that I wanted to know if the external batch file failed or not. And I wanted to capture any error messages produced.In SSIS, it is straightforward. Drag the "Execute

Ibrahim Hafidhnoreply@blogger.com1tag:blogger.com,1999:blog-23748684.post-34309705085021003042008-07-31T14:07:00.000-07:002008-07-31T14:41:19.916-07:00

Well, after a long break from blogging I am back. We have a new addition to the family so that kept me away. Actually, that and just being lazy to blog really.Anyway, I recently came across some useful resources on SQL 2005 and thought I would share:JumpStartTVGreat intro videos for beginners wanting to learn SQL 2005, SSIS, SSAS, etc. They are free but you have to register.TechNet Virtual Labs:

Ibrahim Hafidhnoreply@blogger.com0tag:blogger.com,1999:blog-23748684.post-63315290339782470582007-11-14T08:53:00.000-08:002007-11-14T09:04:54.653-08:00

Got this error trying to rename a SSAS 2005 database:"Backup and restore errors: Restore under different DatabaseId/DatabaseName can not be done for a database with partitions with specific location."Apparently, there is a limitation with SSAS 2005 when you try to restore a database with a different name AND you have specified a different folder location for storage. Hopefully this limitation is

Ibrahim Hafidhnoreply@blogger.com0tag:blogger.com,1999:blog-23748684.post-11751885612609182952007-11-01T15:38:00.000-07:002007-11-01T15:57:52.224-07:00

I kept getting the following error when trying to do YTD calculations using MDX on a client cube:VALUE #Error Query By default, a year level was expected. No such level was found in the cube.FORMATTED_VALUE #Error By default, a year level was expected. No such level was found in the cube.Here is the MDX to do a simple YTD:SUM(YTD([Time].currentmember), [Measures].[Sales])The MDX is good

Ibrahim Hafidhnoreply@blogger.com0tag:blogger.com,1999:blog-23748684.post-62385637933178935682007-10-30T08:13:00.000-07:002007-10-30T08:27:19.419-07:00

I just got asked how to move a SSAS 2005 database from one drive to another. The default data folder is the C: drive when you install SQL 2005. Usually, the data drive that system admins assign is the D: , F: , etc drive.So how do you move a SSAS 2005 database? Well, apparently you can't move individual databases, you have to move the entire data folder to another drive. You also have to change

Ibrahim Hafidhnoreply@blogger.com0tag:blogger.com,1999:blog-23748684.post-45956187748786270122007-10-22T08:56:00.000-07:002007-10-22T09:03:44.183-07:00

Update on books to read:1. T-SQL 2000 - Itzik Ben GanRe-read 3 chapters. Downloaded the SQL scripts in the book from the publisher's website. I did not know that they existed. That will make a good reference.3. Fast Track to MDX - MoshaAlmost done. Need to finish the last chapter.8. SQL Server 2005 Analysis Services Step by Step - ReedGood read. Read most of the book and found it very useful in

Ibrahim Hafidhnoreply@blogger.com0tag:blogger.com,1999:blog-23748684.post-9320496514757073112007-10-22T08:54:00.000-07:002007-10-22T08:55:38.599-07:00

Common Table Expressions in SQL 2005 rule! One of the best uses of CTE is to do recursive queries. The syntax for it is as follows:WITH myCTE (id, name, desc) AS ( select id, name, desc from table you can reference the CTE here )select * from myCTEThe concept is similar to using derived tables in the way that your query can reference the CTE or derived table. The difference is that in a

Ibrahim Hafidhnoreply@blogger.com0tag:blogger.com,1999:blog-23748684.post-44205124452171534642007-07-19T19:30:00.000-07:002007-10-22T08:56:55.227-07:00

Books that are on my shelf that I need start reading, reread or finish off reading:1. T-SQL 2000 - Itzik Ben Gan2. Data Warehouse Toolkit - Kimball3. Fast Track to MDX - Mosha4. 2 minute SQL Stumpers - sqlservercentral.com5. Analysis Services 2005 - Malomed at el6. MDX Solutions - George Spoffard7. Data Mining with SQL 2005 - Jamie Mac et al8. SQL Server 2005 Analysis Services Step by Step - Reed

Ibrahim Hafidhnoreply@blogger.com0tag:blogger.com,1999:blog-23748684.post-45858410744950175912007-06-01T13:43:00.000-07:002007-10-30T08:32:28.968-07:00

It's been a while since I used Global Variables in SQL DTS. So here is a simple tutorial to:Setup a Stored Procedure using Input Variables Dynamically from an INI file (or any other source available in Dynamic Properties Task)(e.g. EXEC usp_test ?, ?)1. Add a SQL Task and enter your SQL script e.g. EXEC usp_test ?,?a) Click on "Parameters"b) Click on "Create Global Variables" to create global

Ibrahim Hafidhnoreply@blogger.com0tag:blogger.com,1999:blog-23748684.post-76510305253531183892007-03-26T20:06:00.000-07:002007-03-26T20:28:45.989-07:00

So I have had a chance to work with aggregation settings to optimize performance in SQL AS 2000 of distinct counts. So far tweaking the aggregations has gotten me the best performance. Dare I say I have the cure to the distinct count blues in SQL 2000? Well give it a try. You will notice significant cube processing times. Basically here is the rundown:Aggregations:1. Start at a lower aggregation

Ibrahim Hafidhnoreply@blogger.com0tag:blogger.com,1999:blog-23748684.post-84731913368745597772007-02-27T07:36:00.000-08:002007-02-27T09:10:42.659-08:00

Do you have the case of the distinct count blues? Too many rows in your fact table to do a distinct count? Are your resources overwhelmed by the distinct count process?No worries...SQL2005 to the rescue! Take advantage of the many-to-many relationship capabilities of SQL2005 to do counts on your dimension table instead of the usual distinct count on your fact table. Read more about the

Ibrahim Hafidhnoreply@blogger.com0tag:blogger.com,1999:blog-23748684.post-3913101511951881452007-02-26T08:45:00.000-08:002007-02-27T07:36:21.080-08:00

Anyone who has built cubes in SQL 2000 and had to deal with the issue of many-many relationships knows how difficult it was to deal with that.Enter SQL 2005 and the all problems are solved. Really, it is that much easier to handle many-many scenarios in 2005. The implementation is very straightforward. You have your "bridge tables" in your database structure and all you need to do is set them up

Ibrahim Hafidhnoreply@blogger.com0tag:blogger.com,1999:blog-23748684.post-18955835348926014972007-02-25T18:24:00.000-08:002007-02-26T09:32:45.801-08:00

There really is no wizard or documented procedure from Microsoft regarding this issue. Also, there isn't a simple way of backing up in SQL 2005 and restoring in SQL 2000. Actually, you can't; because there is no backward compatibility between the two versions. You can backup in SQL 2000 and restore in SQL 2005 but not the other way around.Is this a case of Microsoft forcing you to upgrade or are

Ibrahim Hafidhnoreply@blogger.com0tag:blogger.com,1999:blog-23748684.post-83152322426745297412007-02-07T13:12:00.000-08:002007-02-26T09:33:13.755-08:00

Distinct counts are a pain in SQL AS.DISTINCT COUNTAS2k - based on the dimension structure.AS2k5 - based on hierarchy and attribute structure. This allows the distinct count measure to be calculated on much larger volumes of data.Some issues: distinct counts are typically calculated at run-time – i.e. at the moment the user asks the question.OPTIMIZE DISTINCT COUNTSThe basic run down of these

Ibrahim Hafidhnoreply@blogger.com0tag:blogger.com,1999:blog-23748684.post-12224808979131418532007-02-07T10:47:00.000-08:002007-02-07T10:55:53.469-08:00

I like these tips from the consumerist:Using Credit and Paying it Off on TimeSaying NO to the Extended WarrantyShopping in the Store But Buying OnlineOpening A Store Credit Card To Get A Discount, Then Cutting It UpRead more here.

Ibrahim Hafidhnoreply@blogger.com0tag:blogger.com,1999:blog-23748684.post-62021005946827943782007-02-07T10:40:00.000-08:002007-02-07T11:06:59.905-08:00

CASE TableA Case table has Attributes (columns) and States (values).==> Equivalent to the Dimension Table in Data WarehousingCase key = the PK of the tableNESTED TableNested case - the many part of the relationship.==> Equivalent to the Fact Table in Data WarehousingNested key = Not the FK (i.e. the PK of the table). Important: It is rather the other attributes that make up the nested

Ibrahim Hafidhnoreply@blogger.com0tag:blogger.com,1999:blog-23748684.post-33337660814429949642007-02-07T10:36:00.000-08:002007-02-26T09:33:27.031-08:00

Analysis Services 2005 Performance Guide is now available from the MSDN downloads. Good stuff on performance including: distinct counts, partitioning, many-many, etc.

Ibrahim Hafidhnoreply@blogger.com0tag:blogger.com,1999:blog-23748684.post-37619760421214189542007-02-07T10:23:00.000-08:002007-02-07T15:10:07.337-08:00

Definition:Data mining is about analyzing data and finding hidden patterns using automatic or semi-automatic means.Three Parts to Data Mining:1. Create the model - similar to "create table" discrete = distinct categories continuous = numeric columns2. Train the model - similar to "insert into table" a. processing the model ==> similar to processing a cube b. training model ==> truth table3.

Ibrahim Hafidhnoreply@blogger.com0tag:blogger.com,1999:blog-23748684.post-1141943486782693372006-03-09T14:31:00.000-08:002007-02-26T09:33:40.066-08:00

The Microsoft SQL Server Advisory Team has a good article on indexes: http://blogs.msdn.com/sqlcat/archive/2005/12/12/502735.aspx They include very useful performance tuning queries. SQL Server 2005 has what they call DMV (Dynamic Management Views) that lets you do diagnostics and tuning. This was not available in SQL 2000. This article answers the following questions: (1) How can I find out

Ibrahim Hafidhnoreply@blogger.com0tag:blogger.com,1999:blog-23748684.post-1141943463685542162006-03-09T14:30:00.000-08:002007-02-26T09:34:05.871-08:00

Download the SQL Server 2005 System Views here. Use it to get familiar with internal workings of SQL Server 2005. Found this from SQL-Server-Performance.com.

Ibrahim Hafidhnoreply@blogger.com0tag:blogger.com,1999:blog-23748684.post-1141935259305199612006-03-09T12:13:00.000-08:002007-02-07T10:27:43.235-08:00

NetZero Voice-over-Internet Protocol (VoIP) Service for Dial-Up and Broadband lets you call Anyone, Anywhere in the world, Anytime for Free. Setup an account with Netzero and you can make calls in the US and Canada for free. They also give you a local US number. So international users can call you for free. i.e. PC to Phone! Thanks to my friend Sabir for this link.

Ibrahim Hafidhnoreply@blogger.com0tag:blogger.com,1999:blog-23748684.post-1141925950914634232006-03-09T09:36:00.000-08:002006-03-09T12:20:05.420-08:00

This is my first blog. I actually created another blog at blog.com but their servers seemed to be too slow. Hopefully blogger is faster.So what will I be writing about...hmm....well, mostly SQL, Business Intelligence, and of course just random thoughts.

Ibrahim Hafidhnoreply@blogger.com0