Business Intelligence and Data Warehouse Concepts

When creating an efficient and robust solution for Extract, Transform and Load Process, Lot of things do matter especially when you are working with huge size of databases. Challenge is not only to meet functional requirements Also to look at the performance requirements. Here, I would like to cover most of the part on extracting and loading process, I will not discuss (Buffer usage, Execution tree, Parallelism etc.) You will find those details here: http://technet.microsoft.com/en-us/library/cc966529.aspx Let’s discuss few necessary steps when we want to optimize the extracting and loading process in the package. Before you make any changes in your package, always use SSIS advance functionality to monitor and log metadata about package execution (SSIS Logging, SSIS Performance Counter, and SQL Server Profiler) Steps you should check while creating SSIS package: 1.    Remove unwanted columns from the source and set data types appropriately; this will help you with buffer ...

KPI - Key Performance Indicator is used as a measure of performance (success/failure) of a particular activity. Sometime it is very useful to define a new strategy for the organization. Let's talk about KPI in SSRS 2008 R2 , they have introduced new item in toolbox called 'Indicator' . So we directly drag and drop that indicator to define KPIs in 2008 R2. There are many choices available for KPI presentation like Directional, Symbols, Shapes, Ratings in SSRS 2008 R2.  But in SSRS 2005, 2008 version , There are two ways to define KPIs in the reports 1) by adding images and image value expression and 2) by adding a new column with background color expression. 1) Ex. you want to define KPIs for sales amount > 200 'Green', <= 200 and >= 100 'Yellow', <100 'Red' Simply add a new column and go to the background color property -->add expression there Method1: (Using IIF statement) =iif(fields!salesamt.va...

We use more than one dataset in many of the reports and we always think to join or merge datasets to provide a good solution in reports but we were not able to do it in 2005 or 2008, but now the time has come and microsoft has finally included some cool functions (Lookup, LookupSet, MultiLookup) in SSRS 2008 R2 Let's see how "LOOKUP" works here, Suppose we have a two datasets in the report, ex. one dataset (Store) has store information (StoreID, StoreName, Location, $ Sales etc, Date) and second dataset (Sales) has sales information. (StoreID, Date, Target $ Sales etc.) I will directly grab a columns from first dataset but I also want to display target sales amount as a new column with this information. Now you can use Lookup function here in a new column =LOOKUP(Fields!StoreID.Value,Fields!StoreID.Value,Fields!TargetSales.Value,"Sales") In Lookup function, you need to pass following things:   Value form the first dataset to join with second dataset ...

When you try to set up Tablix property for keep header visible while scrolling, you will see that it is not working in SSRS 2008 or SSRS 2008 R2. You need to take some extra steps to set up this property 1)       Uncheck keep header visible while scrolling tab under tablix property                            2)       Go to Grouping and Click on the right side arrow (Advanced Mode) 3)       Select the first Static Property in Row Groups (In your report first row should display column headers) 4)       Make Fixed Data = True                  5)       Run the report and you will get column headers visible while scrolling Hope it helps! Thanks

We can use sql queries or some of the cool expressions available in SSRS to set default date parameters: Set First Day of previous week (Monday) =DateAdd(DateInterval.Day, -6,DateAdd(DateInterval.Day, 1-Weekday(today),Today)) Set Last Day of previous week (Sunday) =DateAdd(DateInterval.Day, -0,DateAdd(DateInterval.Day, 1-Weekday(today),Today)) Set First Day of Current Week (Monday) =DateAdd("d", 1 - DatePart(DateInterval.WeekDay, Today(),FirstDayOfWeek.Monday), Today()) Set Last Day of Current Week (Sunday) =DateAdd("d" ,7- DatePart(DateInterval.WeekDay,Today(),FirstDayOfWeek.Monday),Today()) Set First Date of last month =DateAdd("m", -1, DateSerial(Year(Now()), Month(Now()), 1)) Set Last date of last month =DateAdd("d", -1, DateSerial(Year(Now()), Month(Now()), 1)) Set First date of current month =DateSerial(Year(Now()), Month(Now()), 1) Set Last date of current month =DateAdd("d",-1,(DateAdd("m", 1,...

In some cases as per the requirements you will need to add 'ALL' as an independent parameter value (not a multivalue parameter) 1) Create a new dataset to populate parameter values Ex: select distinct city from table1 where city is not null        union       select 'ALL' as city from dual       order by 1 2) Modify your main dataset query Ex: where (city = :city or 'ALL' = :city) Thank you

In most of the reports, we use multivalue parameters and for that we can directly write sql query in a separate data sets and modify the main dataset. It works fine for BIDS and Report Server for multi value parameters. But when we need to pass parameter values from the external portal to the reports then follow below steps: 1) Create a split function in your database (Many examples are available online) 2) Create a separate dataset for multivalue parameters 3) Change main dataset to populate multivalue parameter values from split function i.e: and (param1 in             (select column_value as param1 from table(HR.SPLIT(:Param1))) OR :param1 = ‘ALL’) 4) Go to the main dataset properties –> parameter –> select multivalue parameter expression –> add     this: =Join(Parameters!Param1.Value,”,”) 5) It should work now For the URL populate multivalue paramter value list externally using sql query. I have also included 'ALL' as ...