Thursday, 17 August 2017

What is Snapshot Report in SSRS?

What is Snapshot Report in SSRS?

A Report Snapshot in SSRS is a report that contains layout information and a data-set that is retrieved at a specific point in time. Unlike on-demand reports, which get up-to-date query results when you select them, report snapshots are processed on a schedule and then saved to a report server. When you select a report snapshot for viewing, the report server retrieves the stored report from the report server database, and shows the data and layout that were current for the report at the time the snapshot was created.

Steps to a Report SnapShot :

  • Got to Report Manager,Where RDLs are Deployed.
  • Right Click The RDL and Select Manage.
  • Then select Snapshot Options from left the pane and schedule the snapshot of the report.
Blogger Widgets

Wednesday, 16 August 2017

How to Replace Null Values in SSRS Report ?

How to Replace Null Values in SSRS Report ?

You can replace the NULL Values with some Custom value using IIF , IsNothing Function in SSRS.

Just Right Click on TextBox on which you want to replace NULL Value and write an Expression :

=IIF(IsNothing(Fields.ColName.Value),0,Fields.ColName.Value)   [ To Replace with 0]
=IIF(IsNothing(Fields.ColName.Value),"Not Available",Fields.ColName.Value)   [To Replace with String]

Friday, 28 July 2017

Sunday, 16 July 2017

What is Report Builder in SSRS?

What is Report Builder in SSRS?

Report Builder is an Report authoring tool use to design ad-hoc reports and to manage the existing reports. you can preview your report in Report builder and publish your report to a reporting services . In short we can say that Report  Builder provides the capability of design, execute and deploy the SSRS reports.

How to display data on single tablix from two datasets in SSRS ?

How to display data on single tablix from two datasets in SSRS ?

We can display data on single tablix from two datasets with the help of LOOKUP function.
So if we have two datasets and we need to display on a single tablix then they must have one common column in both
datasets on which we can join.

Syntax : =LookUp(source_expression,destination_expression,result_expression,dataset)

source_expression specify the name or key to lookup
destination_expression specify the name  or key to match on
result_expression An expression i.e evaluated for the row in the dataset when source_expression=destination_expression
dataset from which we are adding column.

Friday, 14 July 2017

Top 50 DAX Interview Questions Answers PDF : SSAS/Power BI

Here we come with top 50 SSAS/PowerBI DAX interview questions with answers.

Explain what is DAX?
DAX stands for Data Analysis Expressions, and it is the formula language simply it is a collection of functions, operators, and constants that can be used in a formula, or expression in Microsoft SQL Server Analysis Services, Power Pivot in Excel, and Power BI Desktop. Stated more simply, DAX helps you create new information from data already in your model.expressions.

Explain when do you use SUMX() instead of SUM()?
When the expressions to SUM() consits of anything else than a column name. Typically when you want to add or multiply the values in different columns:
SUMX(Orderline, Orderline[quantity], Orderline[price])
SUMX() first creates a row context over the Sales table (see 1 above). It then iterates through this table one row at a time. SUM() is optimized for reducing over column segments and is as such not an iterator.

What do you understand by new CALENDARAUTO() Function in DAX(SSAS)?
CALENDARAUTO function returns a table with a single column named “Date” that contains a contiguous set of dates. The range of dates is calculated automatically based on data in the model.
Example: In this example, the MinDate and MaxDate in the data model are July 1, 2010 and June 30, 2011.
CALENDARAUTO() will return all dates between January 1, 2010 and December 31, 2011.
CALENDARAUTO(3) will actually return all dates between April 1, 2010 and March 31, 2012.

Name any 3 most useful aggregation functions DAX?
DAX has a number of aggregation functions, including the following commonly used functions:
  • SUM
  • MIN
  • MAX
  • SUMX (and other X functions)
These functions work only on numeric columns, and generally can aggregate only one column at a time. However, special aggregation functions that end in X, such as SUMX, can work on multiple columns. These functions iterate through the table, and evaluate the expression for each row.

Which are the three places where an expression can be evaluated and hence a specific context is set?
1. In a pivot table cell. Filter context is set by rows, columns, filters and slicers.
2. In a row cell (calculated column) Row context is set by the row itself.
3. In the measure calculation area of a table. No row, nor filter context is set.

Name any 3 most useful text functions in DAX?
The text functions in DAX include the following:
These text work very similarly to the Excel functions that have the same name, so if you're familiar with how Excel handles text functions, you're already a step ahead. If not, you can always experiment with these functions in Power BI, and learn more about how they behave.

How is filter context propagated through relationships?
Filter context automatically propagates following the filtering of the relationship. It always propagates from the one side of the relationship to the many side. In addition, you also have the <u>option of enabling the propagation from the many side to the one side</u>. No functions are available to force the propagation: Everything happens inside the engine in an automatic way, according to the definition of relationships in the data model.

What are Path() and PathLength() functions in DAX?
PATH(): Syntax PATH(<ID_columnName>, <parent_columnName>) Its Returns a delimited text string with the identifiers of all the parents of the current identifier, starting with the oldest and continuing until current.
PATHLENGTH(): Syntax PATHLENGTH(<path>) Its Returns the number of parents to the specified item in a given PATH result, including self.
Example: I think there is no need to explain you can understand it yourself
Ref: Microsoft

What is the difference between DISTINCT() and VALUES() in DAX? 
Both count the distinct values, but VALUES() also counts a possible implictit virtual empty row because of non matching values in a child table. This is usually in a dimension table.

Which function should you use rather than COUNTROUWS(DISTINCT())?

What is a pattern?
A pattern is a general reusable solution to a commonly occurring problem.In Microsoft Excel, you use patterns every day to build tables, charts, reports, dashboards, and more.

What are DAX patterns?
DAX Patterns is a collection of ready-to-use data models and formulas in DAX, which is the programming language of Power Pivot. Create your Excel data model faster by using a DAX pattern!

RELATED works when you have a row context on the table on the many side of a relationship. RELATEDTABLE works if the row context is active on the one side of a relationship. It is worth noting that both, RELATED and RELATEDTABLE, can traverse a long chain of relationships to gather their result; they are not limited to a single hop.

Explain how a pivot table can be viewd as an MVC system?
1. Model = the Data Model (incl DAX expressions)
2. View = the table (or chart)
3. Controller = rows + columns + filters + slicers
What can you say about automatic filter propagation The filters only ever automatically flow from the "one" side of the relationship to the "many" side of the relationship; from the "arrow" side to the "dot" side; from the lookup table to the data table—whatever terms you use, it's always downhill.
With the lookup tables above and the data tables below, it is a mental cue to help you instantly visualize how automatic filter propagation works.

How does CALCULATE() result in context transition?
1. When in row context it transitions to filter context: the filter on the rows of a specific table propagates through the relationship to the related before the calculation is completed.
E.g. CALCULATE(SUM(OtherTable[column]) in a calculated column.
2. It extends or modifies an existing filter context, by adding a filter as its second parameter.
CALCULATE() always introduces filter context.

What is the difference between MAX and MAXA functions in DAX?
The MAX function takes as an argument a column that contains numeric values. If the
column contains no numbers, MAX returns a blank. If you want to evaluate values that
are not numbers, use the MAXA function.

How are row contexts created?
1. Automatically in a calculated column
2. Programmatically by using iterators.

How are filter contexts created?
1. Automatically by using fields on rows, columns, slicers, and filters.
2. Programmatically by using CALCULATE()

How can you propagate row context through relationships?
Propagation happens manually by using RELATED() and RELATEDTABLE(). These functions need to be used on the correct side of a one-to-many relationship: RELATED() on the many side, RELATEDTABLE() on the one side.

How does SUMMARIZECOLUMNS relate to filtering?
1. SUMMARIZECOLUMNS is not susceptible for outer (external) filters, in contrast to SUMMARIZE
2. you can add a filter (e.g. using FILTER) as a param of SUMMARIZCOLUMNS and it will filter accordingly. It acts as if you've added a filter in a pivot table.

What is the initial filter context?
The initial filter context comes from four areas of a pivot table:
1. Rows
2. Columns
3. Filters
4. Slicers
It is the standard filtering coming from a pivot table before any possible modifications from DAX formulas using CALCULATE().

How to optimize DAX query plan?
Ans: click here

Why don't you use a CALCULATE() in the aggregation expression of a SUMMARIZECOLUMN()?
The CALCULATE() is automatically generated.

What is the difference between having a measure as a second FILTER param and having the original measure expression as a param. FILTER(table, [MEASURE]) vs FILTER(table, SUM(...))?
A measure has always implicit filter context, so the ROW context induced by the FILTER is transferred to filter context in the measure.<br>In case of the expression only, no filter context is imposed on the expression, so the expression is evaluated with an empty filter context! This gives a different result.

DAX nested Functions is equivalent to SQL what?
SQL subqueries

Wednesday, 12 July 2017

What are different types of DataSources in SSRS ?

What are different types of DataSources in SSRS ?

In SSRS we have few options available for choosing the DataSource.

Some of datasources options available are listed below -

Microsoft SQL Server
Microsoft SQL Azure
Microsoft SQL Server Parallel Data Warehouse
Microsoft SQL Server Analysis Services   
Reporting Server Model
Microsoft SharePoint List
SAP NetWeaver BI
Hyperion Essbase

How to display total number of rows in a Single SSRS report?

How to display total number of rows in a Single SSRS report?

We can display the total number of rows in a single SSRS Report with the help of  "CountRows" function. Just inset a textbox and in the expression of this textbox write a code -

NOTE : You can append string with countrows by using "&" for more readability in the Report as I added.

SYNTAX :countrows()


Thursday, 6 July 2017

Top 30 Database Testing Interview Questions Answers for Database Testers

Tuesday, 4 July 2017

Repeat Rows N Times According to Column Value in SQL Server : HCL Interview Question

Suppose you have a table #Temp as shown in the below image(left table) and you want to repeat rows based on "NTimes" column value. Then what will you do to generate output as below(right hand side table), asked during HCL interview


(SELECT Name,[NTimes], CAST(('<val>'+ REPLICATE ( Name+'</val><val>' ,[NTimes]-1 ) +'</val>') AS XML) AS X  FROM #Temp) A
CROSS APPLY A.X.nodes('/val') y(z)

Monday, 3 July 2017

How to find Running Total in SSRS ?

How to find Running Total in SSRS ?

We can find running total in SSRS  with the help of a RunningValue Function .
Running Value Function will return a  running aggregate of all non-null numeric values specified by the expression, evaluated for the given scope.

Syntax :  RunningValue(expression, function, scope) 

Example:Right Click the TextBox and write a code in expression


Sunday, 2 July 2017

What is Drill Through Report in SSRS ?

Drill Through Reports : Drill through reports is when user click on the values ,redirect the user to another detail  report. Drillthrough reports commonly contain details about an item that is contained in the original summary report .These are standard reports that are accessed through a hyperlink on a textbox in the original report.
 You can make a drill through report by right click on the textbox that you want to be hyperlinked.

Thursday, 29 June 2017

Wednesday, 28 June 2017

What is Spool Operator in SQL Server: SQL Server Performance Tuning

Spool operator scans the input and places a copy of each row in a hidden spool table that is stored in the tempdb database and existing only for the lifetime of the query. If the operator is rewound (for example, by a Nested Loops operator) but no rebinding is needed, the spooled data is used instead of re-scanning the input.
Table Spool is a physical operator.

Friday, 16 June 2017

What is Database testing?

Data base Testing is segmented into below four different categories.

  1. Data validity testing
  2. Data integrity testing
  3. Performance related to data base
  4. Testing of Procedure,triggers and functions

What are different types of errors? Explain each.

  • Syntax errors
  • Execution errors
  • Logic Errors

  • Syntax errors: The syntax errors are those that appear as you type the code. Visual Basic checks your code when you type it in the Code Editor window and warns you if you make a mistake, such as mistyping a word or using a language element incorrectly. Syntactic errors are the most frequent errors. They can be easily corrected in the coding environment as soon as they occur.
  • Execution errors: The errors at run-time are those that appear only after compiling and running the code. Errors of this type can be given, for example, in seemingly correct code fragments, for they do not present syntactic errors, but they do not execute correctly. For example, you could correctly type a line of code that opens a file. But, if the file is corrupted, the application will not be able to execute the Open function and will stop its execution. Most of the errors of this type can be corrected by modifying the code that presents errors, then compile it and re-execute it.
  • Logic errors: The logical errors are those that appear when the application is running. They are often unwanted or unexpected in response to user actions. For example, a badly written key or other external influence could cause the application to stop working even if the parameters are correct, or that it simply does not work. Usually, logical errors are the most difficult to correct, since it is not always clear where they originate.

Explain what is Alpha and Beta testing?

When custom software is built for a customer, a series of acceptance tests is performed to allow the customer to validate all requirements. Most software product developers carry out a process called alpha and beta testing to discover bugs that appear to only the end user can discover.

  • Beta test: They are carried out by the end users of the software in the customer workplaces. Unlike the alpha test, the developer is not normally present. Thus, the beta test is a live application of the software in an environment that can not be controlled by the developer. The client logs all problems encountered during the beta test and reports to the developer at regular intervals.
  • Alpha test: They are carried out, by a client, at the development site. The software is used in a natural way with the developer as a user observer and logging errors and usage problems. Alpha tests are performed in a controlled environment.

What do you understand by Integration testing?

Integration testing (sometimes called I & t integration and testing) is the software testing phase in which individual software modules are combined and tested as a group. They are the tests after the unit tests and precede the system test.

What is the exact difference between debugging and testing?

When a test is performed and a defect has been identified, it is the duty of the developers to first locate the defect in the code and then fix it. This process is known as debugging. In other words, debugging is the process of finding, analyzing, and eliminating the causes of software failures.
On the other hand, the test consists of both static and dynamic life-cycle testing activities. It helps determine that the software fulfills the specified requirements and is suitable for the purpose assigned to it.