MDX and DAX Query Formatter …. is back :)
Hi Friends,
Just to share with you quickly that “MDX and DAX Query Formatter” site is back online.
Here is the link: http://formatmdx.azurewebsites.net/
Thanks, Khilit
May 13, 2013
· 
Khilitchandra Prajapati · 
No Comments
Tags: DAX, Formatting, MDX, Microsoft, Query · Posted in: Business Intelligence, Data Mining, DMX, General, Interview QA, MDX, Query Tuning, SQL Server 2012, SSAS
Recover Backed-up query files in SQL Server Management Studio
Hi guys,
I just want to share quickly very handy tip regarding your SQL query sessions. When you open SQL queries in SSMS, you generally go on and work in newly opened pane/window but sometimes dont save that work.
SSMS by default saves them in the event of pre-mature closure or crash of SSMS or PC restart etc. You can find your SQL queries from following lcoation.
C:\Users\<UserName>\Documents\SQL Server Management Studio\Backup Files\Solution1
FYI, I have found my queries here:
P.S. I am using Windows Server 2008 Operating System. This path could vary based on the Operating System. You can figure it out for sure 
I hope you find this interesting and helpful.
Thanks, Khilit
May 7, 2013
· Khilitchandra Prajapati · No Comments
Tags: SQL Server, Tips, Tutorial · Posted in: Business Intelligence, General, Quick Tips, SQL Server 2012, Tutorial
Retrieving id of inserted record: What to use @@IDENTITY or SCOPE_IDENTITY() or OUTPUT
Hi guys,
This seems very basic information but need to know properly.
Issue:
I need to find out or have the id of the records which I inserted in table.
Analysis:
This requirement seems very simple but there could be various cases where you would not get the correct id just by using @@IDENTITY function.
Think of the case where you are inserting a record in table A and there is a trigger which fires as soon as there is a insert in table A which insert a record in table B. so in such case @@IDENTITY wont give you the correct id/pointer of inserted record.
So to deal with this issue you can use SCOPE_IDENTITY(). You will always get the last value you have inserted in table. However, it is noticed that sometimes in some scenarios SCOPE_IDENTITY() does not promise the correctness of results. May be due to parallelism or multiple inserts or bulk inserts in table(s). E.g. when you do … Insert Into Select <columns> from Table
Well, Microsoft recommends that “You do not use either of these functions in your queries when parallel plans are involved as they are not always reliable. Instead use the OUTPUT clause of INSERT statement to retrieve the identity value“.
Check out MS articles:
http://support.microsoft.com/default.aspx?scid=kb;en-US;2019779
http://msdn.microsoft.com/en-us/library/ms177564.aspx
I hope this will help you somewhere.
Thanks, Khilit
February 6, 2013
· Khilitchandra Prajapati · No Comments
Tags: Business Intelligence, General, IDENTITY, Interview Tips, OUTPUT, SCOPE_IDENTITY(), SQL Server · Posted in: Business Intelligence, Free, General, Interview QA, Quick Fix, Quick Tips, Script, SQL Server, SQL Server 2012, SQLServerPedia, T-SQL, Training, Tutorial
The Story of Precision, Scale, and Length in SQL Server
Hi guys,
Did you ever faced issues with BIG mathematical figures – related to either overflow or any other arithmetic errors. This may be applicable to SQL arithmetic operations or arithmetic operations you do with SQL table in SSIS etc.
Well, check 1 more time, if you have any issues in your database design related to column data type which stores the BIG numbers.
I ran into this issue couple of times, and reason behind this issue was same all the time. So decided to do a quick post. And that reason is “Improper or insufficient precision, scale and length defined for storing Decimals and Numeric values in table“.
I wanted to describe this with an example but there is already a very nice explanation by MSDN on this topic. Check out: http://msdn.microsoft.com/en-us/library/ms190476.aspx
You will notice in this article that, how any arithmetic operation will affect size and space allocation of the resultant value during mathematical operation. You should use following shown table for defining and designing tables in such a way that can handle BIG mathematical figures during operations.
From MSDN, for your quick view:
|
Operation |
Result precision |
Result scale * |
| e1 + e2 | max(s1, s2) + max(p1-s1, p2-s2) + 1 | max(s1, s2) |
| e1 – e2 | max(s1, s2) + max(p1-s1, p2-s2) + 1 | max(s1, s2) |
| e1 * e2 | p1 + p2 + 1 | s1 + s2 |
| e1 / e2 | p1 – s1 + s2 + max(6, s1 + p2 + 1) | max(6, s1 + p2 + 1) |
| e1 { UNION | EXCEPT | INTERSECT } e2 | max(s1, s2) + max(p1-s1, p2-s2) | max(s1, s2) |
| e1 % e2 | min(p1-s1, p2 -s2) + max( s1,s2 ) | max(s1, s2) |
* The result precision and scale have an absolute maximum of 38. When a result precision is greater than 38, the corresponding scale is reduced to prevent the integral part of a result from being truncated.
Important Note: Don’t forget to check for your SQL Server Version.
I hope you will find this informative.
Thanks, Khilit
January 28, 2013
· Khilitchandra Prajapati · No Comments
Tags: Arithmetic Overflow, Business Intelligence, Error, General, SQL Server, SSIS · Posted in: Business Intelligence, Free, General, Interview QA, Quick Fix, Quick Tips, Script, SQL Server, SQL Server 2012, SQLServerPedia, SSAS, SSIS, SSRS, T-SQL, Tutorial
Tracking Down Ghost Characters: WHERE Clause Does Not Work Properly In T-SQL Query
Hi guys,
I would like to share an experience with you when I was working on 1 SSIS related issue.
Scenario was:
I have to exclude some records from Table A by matching some keys of Table B.
Table A
| Year | Month | CustomerCode | <Extra Columns> |
| 2012 | JAN | CX1000 | |
| 2012 | JAN | CX2000 | |
| 2012 | JAN | CD3000 |
Table B
| Year | Month | RecCustomerCode | <Extra Columns> |
| 2012 | JAN | CX1000 | |
| 2012 | JAN | CX7000 | |
| 2012 | JAN | CD8000 |
What is expected was – Table A’s following records:
| Year | Month | CustomerCode | <Extra Columns> |
| 2012 | JAN | CX2000 | |
| 2012 | JAN | CD3000 |
January 25, 2013
· Khilitchandra Prajapati · No Comments
Tags: Business Intelligence, Carriage Return, Query, Sanity Check, Special Characters, SQL Server, SQL Table Value with Carriage Return, SSIS, T-SQL, Where Clause · Posted in: Business Intelligence, Free, General, Interview QA, Quick Fix, Quick Tips, Script, SQL Server, SQL Server 2012, SSIS, T-SQL, Tutorial
MSSQL: Find total records of each Table in given Database
hi guys,
i wanted to do a quick post on how you can find total number of records in each table of the database.
here is the script you can use:
USE <Your Database Name>
GO
;WITH CTE_DATABASETABLESIZE (DATABASENAME, SCHEMANAME, TABLENAME, TOTALROWS)
AS
(
SELECT
DB_NAME(), SCHEMA_NAME(SO.UID), SO.NAME, SI.ROWS
FROM
SYSOBJECTS SO
INNER JOIN SYSINDEXES SI
ON SO.ID = SI.ID
WHERE
TYPE = ‘U’ AND SI.INDID IN (0,1)
)
SELECT * FROM CTE_DATABASETABLESIZE
/*ORDER BY SCHEMANAME
ORDER BY TOTALROWS ASC*/
ORDER BY TOTALROWS DESC
Note: Customize this query according to your needs
Thanks, Khilit
January 23, 2013
· Khilitchandra Prajapati · One Comment
Tags: SQL Server, SSIS · Posted in: Business Intelligence, General, Interview QA, SQL Server, SQL Server 2012, SQLServerPedia, SSAS, SSIS, SSRS
Microsoft SSAS Solution: Cube Design Considerations
Hi guys,
today i read James Serra’ post – whether 1 single BIG cube is good as comapred to multiple small cubes. Check out the advantages and disadvantages with both design approaches. he made a pretty nifty list of both of these.
i would also recommend you to read this post.
here is the link: http://www.jamesserra.com/archive/2013/01/ssas-one-giant-cube-or-many-small-ones
i hope you also find this interesting.
thanks, khilit
January 16, 2013
· Khilitchandra Prajapati · No Comments
Tags: Business Intelligence, Microsoft, SQL Server, SSAS · Posted in: Business Intelligence, General, Interview QA, Quick Fix, SQL Server, SQL Server 2012, SSAS
User ‘Domain\User’ does not have required permissions. Verify that sufficient permissions have been granted and Windows User Account Control (UAC) restrictions have been addressed.
Hi guys,
its seems like this issue have been faced by many prople. i have checked the solutions on web and found that not all answers contains the complete solution. so decided to put 1 complete solution here.
in most of the places, you will find answers like… Run IE as Administrator -> Open Report Manager site -> Add your user in “System Role Assignment” (settings) etc.
But there is tini-tiny step you have to do before you run your IE as Administrator.
- Open your IE -> Tool -> Internet Options.
- Security tab -> Local intranet -> Sites -> Advanced -> Add your root level site here.
- Advanced tab -> Make sure you have checked (selected) the item “Enable Integrated Windows Authentication*” under Security category.
thats it.
now are you ready to follow … Run IE as Administrator etc.
Here is the what you need to do now: http://www.soheib.com/technical-knowledge/sql-server-2012-reporting-services-uac-user-access-control/
i hope this will help you somewhere.
thanks, khilit
January 16, 2013
· Khilitchandra Prajapati · No Comments
Tags: Business Intelligence, Microsoft, SQL Server, SSRS, UAC, Windows · Posted in: Business Intelligence, General, Interview QA, Quick Fix, SQL Server, SQL Server 2012, SSRS
Quick Introduction – What is FileTable in SQL Server 2012 ?
Hi guys,
With the release of SQL Server 2012, couple of Programmability enhancements are also introduced. One of the cool feature is “FileTable”.
So, what is FileTable and Why do we need it?
Applications typically store data within a relational database. But as we know that different applications stores their data in their own formats which could be unstructured formats for other applications and database, e.g. word or excel documents, media files, XML files etc.
Unstructured data usually resides on a file server (File System) and not directly in a relational database such as SQL Server. (Because if we do so, then it would increase the size of database)
So, it becomes challenging for us to manage structured and unstructured data across these systems/platforms as well as to keep them in sync.
FileTable feature which is a new capability in SQL Server 2012 is a solution for these challenges.
It builds on FILESTREAM technology that was first introduced with SQL Server 2008. FileTable offers organizations Windows file namespace support and application compatibility with the file data stored in SQL Server.
As an added bonus, when applications are allowed to integrate storage and data management within SQL Server, fulltext
and semantic search is achievable over unstructured and structured data.
You can find more details here : http://msdn.microsoft.com/en-us/library/ff929144.aspx
Thanks, Khilit
December 6, 2012
· Khilitchandra Prajapati · No Comments
Posted in: Uncategorized
Microsoft Business Intelligence at a Glance Poster – FREE
hi guys,
Download this FREE poster and find-out the Tools and Technologies Microsoft offers under BI (Business Intelligence) umbrella.
Here is the direct link: http://www.microsoft.com/en-us/download/details.aspx?id=35586
Thanks, Khilit
December 5, 2012
· Khilitchandra Prajapati · No Comments
Posted in: Uncategorized
