Posts

SQL Query Optimization

While working with large amount of data, the main problem with queries is to increase performance of queries by decreasing execution time. Many factors are affecting on query performance. Some are as bellows: 1.     Data volume : If you are playing with large amount of data and taking multiple column from multiple table with certain join then performance of query is get slow down. You need to keep in mind that before joining table to get another column first reduce size of data. Apply where clause and take data on conation what you required. You can use CTE or Temporary table for capturing limited data and instead on joining actual table join CTE or Temporary table to it. It will defiantly optimize the performance of query. 2.     Indexes: Creating useful indexes is one of the most important ways to achieve better query performance. While creating Indexed you need to consider, frequency of run the query, type of queries which is using ...
Post a Comment
Read more

JSON_Automated_stored_procedure

Image
What is JSON: JSON stands for  J ava S cript  O bject  N otation, and is a way to store information in an organized, easy-to-access manner, "self-describing" and easy to understand. It gives us a human-readable collection of data that we can access in a really logical manner. Why we need JSON: JSON is a lightweight data-interchange format and it is language independent. Many sites are sharing data using JSON in addition to RSS feeds nowadays, and with good reason: JSON feeds can be loaded asynchronously much more easily than XML/RSS. Format of JSON string: JSON Example { "employees" :[     { "firstName" : "John" ,   "lastName" : "Doe" },     { "firstName" : "Anna" ,   "lastName" : "Smith" },     { "firstName" : "Peter" ,   "lastName" : "Jones" }             ] } You can compa...
Post a Comment
Read more

MDX Error handling tricks

Error List: Duplication of column value(column is not primary key): This issue occur due to NULL records. You have to handle NULL records for particular column. Below are the some properties which you need to set for the attribute: On dim property set: UnknownMember = Visible On attribute property:Key column Expand -> Null Processing-> handle in your way.
Post a Comment
Read more

MDX Tricks

I have started working on MDX queries also. Basic syntax looks like SQL queries but still it is far different than SQL queries. While working I found some difficulties to apply some tricky logic in queries, so I thought to jot down the tricks for others will helpful for some one. So I am hopping that you should know the basic logic to write MDX queries and below are some defined situation and its logic. Checking which hierarchy is selected on Axis: CASE     when NOT IsError(Extract( Axis (0), [Dim Date].[Calendar_Year]). Count ) then (Formula for Calendar year hierarchy ) when NOT IsError(Extract( Axis (1), [Dim Date].[Calendar_Year]). Count ) then ( Formula for Calendar year hierarchy ) when NOT IsError(Extract( Axis (0), [Dim Date].[Fiscal_Year]). Count ) then ( Formula for Fiscal hierarchy ) when NOT IsError(Extract( Axis (1), [Dim Date].[Fiscal_Year]). Count ) then ( Formula for Fiscal hierarchy ) else '-1' END H...
Post a Comment
Read more