l'Ishwar incroyable Nataraj

Have you ever noticed that your measure value in SSAS do not correspond to the value in the data warehouse.... this is a big hindrance....because one is wasting precious development time in extrapolating data between the cube and the warehouse. I am just going to create a checklist for issues to look at really quick if one does come across this issue!!! Problem Statement: Let us consider a Fact table called FactInternetSales with a fact called internetsalesamount select sum(internetsalesamount) from FactInternetSales; Let us say that this value is 25000. Now if we run this against the cube .... lets call it Sales. select measures].[internetsalesamount] on 0 from Sales; Now the value returned from this query is 510...... Why is this happening? Now just follow the following checklist to ensure that you can rectify this issue as soon as one possibly can.... 1. Go to the cube and in the [internetsalesamount] properties, change the aggregation ...

Recently was discussing with a colleague of mine as to the best practices in SQL. So this post is dedicated to that chat. The first thing we discussed about is the usage of NOLOCK's for tables in non highly transactional based databases with a wide user audience. Now the question of dirty reads does come to mind but at the end of the day the Read Uncommited transaction allows multiple end users to query the underlying table. NOLOCK is nothing more than a table hint that allows the query to be performed in a read uncommited fashion but is still a great practice to follow even while creating views. I have been using NOLOCK's for a large period of time (and sometimes implement it out of habit for my views i.e. SELECT C1,C2 FROM V1 (NOLOCK) as well which does not make any sense but is more of a force of habit except for my last post where I removed it on purpose) but I recently came across another table hint called READPAST but I am strictly avoiding this topic as this prevents ...

Have been writing a couple of tech articles in the recent past (If I had started writing it on a regualr basis I might have had an encyclopedia by now.... I should write something else just 4 a change huh!!!): Question posted on one of the SQL server sites out there--> concatenation of rows to columns.... There are 3 ways of accomplishing it and I ain't going thru all those approaches but will just highlight them (except for my solution here):- 1. Creating a dynamic SQL which will keep concatenating the rows into a specific column in a PL/SQL block which returns the required table. 2. Using recursive CTE to concatenate the rows into columns .... Maybe should hit upon CTE's which are extremely useful in a later post (If I get some time i.e.) 3. Now this method is pretty cool Now let me give the problem statement as posted by that individual... Let us create the table in the following manner--> select 'Report1' as reportID,'Browser' as RoleID, 'YChen...

Recently started learning more about performance related stuff in SQL server. Man oh man!!! so many things to learn and so many things to blog about.... Let us start with the basics: Writing a SQL query you need to measure a couple of things: 1. Query Cost 2. CPU Utilization 3. Time taken for the Query to execute 4. Table scans, Index scans and Index seeks. Let us start by focusing on Query cost---> Write the command- SET SHOWPLAN ON; and then execute a SQL for example--> SELECT E.EmployeeID,E.EmployeeStatus FROM Employee E where E.AddressID = 112345 ORDER BY E.EmployeeID This command will basically give us the Query analysis built into the SQL server. The first aspect you will notice is the Query cost... In this case if there are indexes on the underlying table then we can state that the query cost might be less provided the index is being used else the query cost is going to increase significantly. Also we can minimize query cost by inclusing more filters in our SQL query provi...

Just to refresh my memory, I am going to jot down the following as part of my cheat sheet, so that one glanceover would refresh my memory: ACID :---> Atomicity: This is the process by which any transaction performed on a DBMS, will not take place (or will be rolled back) in case a part of the transaction fails. Consistency: Consistency implies that the state of the database would be constant at any period of time. During transactions, the db would move from one consistent phase to another consistent phase. (All the constraints and regulations of the database is maintained) Isolation: Isolation indicates that every transaction is performed as a seperate action and will not be interdependent on one another. In case of any such dependencies then the transactions will take place in a sequential manner (based on FCFS or some priority based operation) Durability: This indicates that the database will be robust and will not collapse at any period of time. At frequent time intervals snapsho...