Another victory of Sri Lanka

This clearly shows the reality, this shows who wants to destroy the great country, Sri Lanka. Sri Lanka won again, defeating the international terror. How they voted at the UNHRC sessions Victory for Lanka’s fight against terrorism

PerformancePoint error: Unable to connect to Server

Are you experiencing this? You might. There are different set of connection related errors with PerformancePoint 2007. If you get errors like "Unable to connect to the specified server. Make sure the address is correct" or "The PerformancePoint could not connect to the specified data source", most probably, you will find the solution here. If you face for an error like "Unable to connect to Server" with PerformancePoint dashboard designer options, the reason might be the unavailability of AJAX 1.0. Solution is here. It has another solution but installing AJAX fixed my problem.

Skipping prerequisities when installing PerformancePoint 2007 SP2

While installing the SP2 of PerformancePoint 2007, you may get an error like; Failed package requirement analysis, please refer to the log file for more information. This can be avoided by setting the SKIPREQCHECK as true with msiexec command. But you have remember one thing, when we install the product we use i switch but p switch has to be used when installing service packs. Please see the code below; Installing the product, skipping the prerequisites; msiexec /i PSCSrv.msi SKIPREQCHECK=1 Installing the service pack, skipping the prerequisities; msiexec /p PSCSrv.msi SKIPREQCHECK=1

Installing PerformancePoint 2007 SP2

If you are using PerformancePoint 2007 and you have not installed the SP2, then this is the time for installing it. As you know, it did not support natively for SQL Server 2008, and now it allows. You will be installing this SP2 under three scenarios; 1. Update PerformancePoint Server with SP2 only. No changes to SQL Server 2005. 2. Update SQL Server 2005 to SQL Server 2008 and then update PerformancePoint Server to SP2. 3. Install PerformancePoint Server RTM and SQL Server 2008 in a new environment. You need to make sure that you follow the correct steps for whatever the scenario you select. You can find the instruction for all scenarios here. You can download the SP2 from here(x86) and here(x64).

Sri Lanka is free from Terror

After 30 years, His Excellency Mahinda Rajapakse, President of Sri Lanka has freed the country from LTTE terror, keeping his word. How fortunate we are? My gratitude to President and victorious armed forces.

Another BI workshop

This is the first workshop organized by IronOne Technologies. It was started on last Thursday and will continue till this Tuesday. I feel bit tired because it is very difficult to keep on talking :), without a partner. Almost all workshops that were organized before, were done by Gogula and me, hence work was shared, doing it alone is a very difficult task. Audience of the workshop is really good, some have work on BI related projects too. Everybody exposes an average knowledge, hence it is not so difficult to run the workshop.

How to distinguish between measures and KPIs…

As you all know, measures are identified at the analysis stage of Data Warehouse/BI project. Are all identified measures KPIs? This question came up, while we were having a discussion on one of our new projects. It was an interesting question, thought to make a post on it, showing what I think, what I have read… In a way, yes, we can consider all measures as KPIs. But they become proper KPIs when it shows the quality of the value, health of the value. This requires another attribute to be attached: Let’s take an example, Sales Amount, obviously it is a measure. It shows the sales amount for some dimensions (for current year, for a product). Does it show the health of that value, whether it is good or bad? No, it is just a value. Important thing to remember is, KPI is not just a value, it indicates the health of the value too. So, if we need to make Sales Amount as a KPI, we need to use another attribute such as Target. Target value allows to judge the Sales Amount whether it is healthier or not, hence Sales Amount becomes a KPI. That’s how I see the difference between a measure and a KPI? Am I right? Any thoughts on this?

Is your architecture leading to Stovepipe data mart?

Question arose while we were discussing BI. I asked myself, whats the hell is "Stovepipe" data mart, I was aware of the word but picture behind it, did not come to my mind immediately. So, brushed up.... Stovepipe data marts are nothing but data marts that prevent integration of themselves for making a data warehouse because of the unavailability of conformed dimensions. In most cases, data marts (dimensions in data marts) are designed with their own rules, policies and mainly own structures. This results incompatible dimensions in different data marts though they represent same entity. It will be a mess, will be awkward situation when it comes for making a data warehouse, combining all data marts. Though data marts have many advantages, cost-wise, time-wise, etc.. this is the major disadvantage of them. Are you leading to a Stovepipe data mart?

SSRS 2008 Video: Formatting text blocks individually in a single textbox

This is my first video, had been planning for months, and somehow was able to do one. I do not know whether the quality of this lesson is okay, because I made many mistakes while recording, this is my 8th or 9th recording :). This shows one of the newest features of Reporting Services 2008. Reporting Services 2005 did not allow us to format text blocks in a textbox with different styles. Now it is possible. Watch the video and see whether it is useful. Appreciate your comments on this.

REPEATABLE READ is fine, but want to let others to read non-locked records

We usually set the REPEATABLE READ Transaction Isolation Level in order to overcome Inconsistent analysis (nonrepeatable read) and Lost Update concurrency problems. This level do not let other users to read records that are read by your transaction, but creates a deadlock situation. Here is a simple example;

   1: SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

   2: BEGIN TRAN

   3:     DECLARE @Id int

   4:     SELECT TOP 1 @Id = Id

   5:     FROM dbo.Employee

   6:     WHERE IsUpdated = 0

   7:  

   8:     ......... -- more processing

   9:  

  10:     UPDATE dbo.Employee

  11:         SET Info = 'some text', IsUpdated = 1

  12:     WHERE Id = @Id

  13: COMMIT TRAN 

This code gets the first record available for updating (TOP 1, IsUpdated = 0) and does some processing, finally updates the record. This code does not allow others to modify the record while it is being accessed, avoiding inconsistent analysis and lost updates. But it reduces the concurrency. It does not allow other to get the second available record too, making a deadlock situation if the same is executed by another connection. There is no harm of letting others to access the second available record. How do we do it? This cannot be implemented with REPEATABLE READ isolation level. All we have to do is, use UPDLOCK and READPAST query hints. UPLOCK puts exclusive lock on the object and does not allow others to obtain a lock on the same. READPAST skips all locked records and read the non-locked records. Here is the corrected code;

   1: -- SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

   2: BEGIN TRAN

   3:     DECLARE @Id int

   4:     SELECT TOP 1 @Id = Id

   5:     FROM dbo.Employee

   6:     WITH (UPDLOCK, READPAST)

   7:     WHERE IsUpdated = 0

   8:  

   9:     ......... -- more processing

  10:  

  11:     UPDATE dbo.Employee

  12:         SET Info = 'some text', IsUpdated = 1

  13:     WHERE Id = @Id

  14: COMMIT TRAN 

This locks the record while it is being accessed but allows to process the next available record for others.

Reporting Services 2008 Error: The underlying connection was closed. An unexpected error occured on a send.

It hit me again. This does not allow you to deploy any reports or open report manager via the browser. This is because of the invalid configuration in reportserver.config file. The default value of the key SecureConnectionLevel has been set as 2, which requires SSL configuration. If you do not use SSL, make it as 0, making service least secure. Refer BOL for more info.

Reporting Services 2008 Error: Invalid namespace

This error comes when we try to configure Reporting Services 2008 with Reporting Services Configuration Manager. The reason for this is funny and identified as a known issue; It checks for wrong instance name. This has been fixed with the Cumulative Update Package 3. If you are unable to download, another method has been given with this which I used. It gives instructions for changing the reportingservices.mof file.

SQL 2008 Launch, Kandy: Few more pohotos

Doing my presentation.
Giving a prize to a winner.
All prize grabbers.
SQL Server crew

Sucess of Community Launch of MS SQL Server 2008, in Kandy

I am proud to announce that the SQL Server 2008 Community Launch, Kandy took place in grand scale. This was organized with some great difficulties, however we managed to make the event as a success event. Targeted audience was 100 and there were 90-odd participants. All participants were delighted and almost all appreciated the event. Following the agenda, Gogula delivered the KeyNote and Introduction to SQL Server 2008. Dinesh Asanka's presentation was the next one which was on developer track. Preethi did the third presentation, again it was on developer track, finally I did a presentation on BI. In addition to that Dinesh Asanka showed a presentation that spoke about red-gate tools. I did not get all photos for the event, once I have, we will publish on SQLServerUniverse.Com. I must thank to following companies, individual for making the event success; Gogula Aryalingam - This was his idea and credit must go to him. Dinesh Asanka - Did a fantastic job, not only delivering job, helping from start to end. Preethi - He was with us since we were planing, just like Dinesh Asanka and did a great job too. Wela (Microsoft Sri Lanka) - Supported us providing financial support, where we were in great difficulty. Rachel (RedGate software) - Supported us sending a package of goodies for delivering. It helped us very well. Akalanka (Peradani University) - Helped us to organized the event at the campus. Shamri (ESoft, Kandy) - Supported us by giving 30% discount vouchers on MS courses for all participants. L. H. Event Management - Supported the event by organizing the lunch. It was a great buffet :). And all participants - We would not be able to make this is as a success event without you guys. We will be doing the Colombo event soon. Though it was scheduled for this month, since we have some financial issues to be sorted out, we had put it off. Here are some pictures; At registration: Everybody got a SQLServerUniverse.Com pen, 30% discount voucher and a SQL Server 2008 file. Part of the audiance - just before starting the event During lunch time Phots of sessions and other activities will be published soon.

Separating batches using a new word, instead of “GO”.

Can we use a different word instead of GO? Yes, it is possible because GO is not a T-SQL statement; it is a command that is recognized as a batch separator by client tools such as Management Studio and sqlcmd. Unfortunately most are unaware of this and consider it is as a T-SQL statement. If you need to use a new word with your management studio, follow these steps; Open Options window (Tools -> Options). Expand and select the node Query Execution -> SQL Server -> General. Find an option called Batch Separator. Set a new value as you want. Done. Now you can use the new word instead of GO. You may face for a problem if you execute the code in a different Management Studio. Because of that, the word GO is preferred and recommended but remember the fact; it is not a T-SQL statement.

Community Launch of MS SQL Server 2008, in Kandy

We, SQL Server MVPs are organizing Community Launch of SQL Server 2008, next week in Kandy and 4th week of January in Colombo with the support of Microsoft Sri Lanka, Red-gate and SQLServerUniverse. SQL Server MVPs (Dinesh Asanka, Gogula and me) and Preethi (One of experienced DBA with vast knowledge) will be delivering; What's new in SQL Server 2008. If you are in Kandy, join with us.

MVP again

Microsoft MVP award team has decided keep my MVP title for another year. Thanks Microsoft award team, Microsoft Sri Lanka, Lead Lilian Quek for re-awarding me.

Index Properties window does not show the Fill Factor set

This was another issue we came across. See the below images, They show the same index property of AdventureWorks database in 2005 instance. The first image from SQL Server 2005 Management Studio and the second from the SQL Server 2008 Management Studio. Note that 2005 MS does not show the fill factor set properly. Is it a bug? Gooled but so far, no luck on this.

Happy New Year!

Hi everybody, wish you all very happy, lovely, peaceful new year!

Unable to process the objects in Analysis Services but deploy

This ate few minutes from my allocated time :). I was able to successfully deploy the Analysis Services 2008 project to another server that has only SSAS 2008 installed but had no way of processing. I was able to connect to the SSAS via MS too and see the deployed items but it did not allow me to process too. This was the error; OLE DB error: OLE DB or ODBC error: Login timeout expired; HYT00; A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.; 08001; Named Pipes Provider: Could not open a connection to SQL Server [2]. ; 08001. I was bit confused but finally figured out the problem. The issue was with the data source. Though the connection string that was used with data source in the project can be used for connecting to the data source, once deployed, SSAS cannot connect to the data source via same credential. I changed the credential of project's the data source that allows to connect to the data source from both machine. Finally I was able to deploy and process the objects :). You may face for same, hence shared it.

How sys.dm_db_index_physical_stats - LIMITED shows the leaf level fragmentation?

This question of raised by one of my students in my new batch, excellent question. We all know that when we use LIMITED mode, it scans only the pages above the leaf level. If that is the case, how does it report the fragmentation of leaf pages (index_level = 0)? I could not answer immediately, so, started digging it with many articles, many blog posts. Most say what BOL says, some had some additional, valuable points too. Finally I found the answer from Kalen's blog. The reason is the pointers in the upper level. It uses pointers to calculate the fragmentation of leaf level without scanning the leaf level. LIMITED mode does not scan leaf level at all. Great. One more question to be answered. Will blog once I solved that too.

Silly mistake I had made :)

I had written code filtering some records out and had assumed that it would produce the result I want. After few minutes I realized that how careless I was; here is a similar implementation that I had done; Assume that we have a table like this: And want to get all records that "value"s are not equal to 0. This was my code: select * from test where [value] != 0 But it should be written like this :). select * from test where isnull([value], -1) != 0 Hope you wont make that mistake.

Are practice exams worth to purchase?

I was thinking of doing 70-448 (MS SQL Server 2008 Business Intelligence Development and Maintenance) exam and thought to take a practice exam from one of the exam providers. Once googled, I realized that they are bit expensive to me (most were around US$ 125) and had no way of purchasing them but downloaded one sample test that contained 10 questions. Here are 2 questions out of 10, and I decided NOT to purchase any. This question says that there is an error but no description of the error, then how can we figure out the correct answer :)? And I think that the explanation is not relevant to the question too. Again, I am confused, where is the method named "DMO"? Almost all question are just like these. Do you think that they are correct? Have I taken them wrong? Are they worth enough to purchase by spending US$125?

Char column contains a lot of NULLs. Should change to varchar?

Most of the time, the decision of the usage of data types, char or varchar is depending on, whether the values are fixed-length or variable-length. Varchar is used when the length is vary and number of characters expected are greater than 10 (heard about this before? ). There is another fact to be considered which most ignore which is nullability of the column. What if the type is char and it is fixed-length, and contains a lot of NULLs. We cannot forget that char takes up the assigned number of bytes even it contains NULL. Therefore, considering this factor, if your char column contains lot of NULLs, you should really think of changing it to varchar data type.

New batch for course 2779B

Another batch for course 2779B that is for exam:70-431 was started on last Saturday at NetAssist. Fourteen students have been registered but some were absent at the first day . Some of students come with SQL Server background but some are not, unfortunately it is one of prerequisites. Anyway, just like the other batches, hope this batch will learn well too.