Report Studio Interview Questions..

1.How to create measures and Dimensions?
A:By using the Framework Manager we can create measures and dimensions according to our business needs.

2.What is the difference between List and Crosstab?
A:List report show data in rows and columns versus a cross tab report show data in grids with dimension in rows and columns and measures in cells or in the intersection points.

3.What is the security module used in cognos?
A:The security module used in cognos is cognos access manager.

4.What is report burn and where it occurs?
A:Report can be divided into different parts and sending these different parts of report to different users. It occurs in cognos connection.

5.What is difference between drill through and drill down?
A:Drill Through Allows u to Navigate From Summary TO Detailed Information.

Drill down also similar kind of thing ,

For Example will do drill down on YEAR IF We Drill Down on this he next level Will Come means year contains Quarters, it contains
Months,
MOnths Contain Weeks,
Week Contains Days.

so,we can view all levels through drill down develop same reports to another package.

6.How can I change reports to another package?
A:Open that report and save as that report Select public folder and then select package in which package u want to save then save it.

7.How to create a dynamic column name in Cognos?
A:These are the steps -
i. Create a calculated column which contains the information that the header is to contain, such as "Report for year 1999" (concatenated text and date to string sub string extraction).
ii. Highlight the report, and then right-click.
iii. Select Properties, and then click the Headers/Footers tab.
iv. Clear the Column Title Header check box. This will remove the headers from your columns.
v. Reinsert the rest of the column headers; insert text will work.
vi. For the dynamic column, from the Insert menu, click Data and select the calculated column you created and insert it into the report.
In Cognos 8.0, First create a Calculated Data Item, select the list, associate it with the Query in Which Calculated Data Item is created, then click on Structure and then List Header and Footers. Check List Header and make the Box Type of the Column header as None.
Unlock and then drag the Calculated Data Item into the required header which will look like a Column Header in the report.
For making a column name dynamic the only thing that you have to do is insert a layout calculations from the toolbox tab in report studio.
I came across this answer because I wanted each column in across tabulation to have a text description different from the variable (column's) name. Cognos's gloriously useless documentation says nothing on how to do this and adding text isn't an allowed action in page design mode (the error message you get goes beyond unhelpful).
To have the column titles, row footers, etc.,

8) What r the migration tools available in the market with respect to cognos such as impromptu reports are migrated to cognos reportnet?
A) Reportnet1.1 migration tool is available to migrate the impromptu report to reportnet.

9) Can report net supports cubes?
A) No

10) What are versions of cognos from starting release to latest in the market?
A) Cognos EP7 series,Cognos reportnet1.1 Mr1,MR2,MR3,MR4,cognos8.0,8.1, 8.2,8.3 ,8.4

11.What is IQD? What is contained in IQD?&How you create IQD In ReportNet FrameWork?
A) IQD is impromptu Query Definition. It's a transformer report. To use an IQd in report net framework manager, there is a process called externalization using which you can import the IQD.
IQD Is impromptu query definition, the name it self indicating DAT, it contains the SQL statement. To create the iqd in Frame work manager, create a new query subject with required query items.
Set the Externalise method property of that Query subject to 'iqd'. While the publishing the package select the 'Generate the files for Externalized query subjects'. And publish it to the local machine, which can be used as data source for Transfromer model.

12) How u provide security to reports? How u provide security to packages ?
A) Through Access Manager.


13.How to select multiple values from Type-in prompt?
A:Example - I want to enter into type-in prompt in 'Product name--- Liza' display report data in 'product name is Leza' only OR I want to enter into type-in prompt in 'All', that time display report data in all are report data.
Q) How is possible this Scenario using in type-in prompt.
Here we can enter one value, here it can't shows lovs. In catalog colomns we can select the value for which we can insert the pick list.
Can you be more specific about this. What i understand from the question, you have a prompt page with a text box prompt and the user types the value. When the user types the product name '
Ex: laptop' and clicks 'finish' the report page has to display all the records relevant to laptop. When the user types 'All' in the text box prompt and clicks 'Finish' the report has to display all the products.
BY USING STRING VARIABLE WE CAN SELECT(OR)ENTER MULTIPLE VALUES By using the following condition as the filter for the text box prompt we can get the data for the specified product as well as All products when we type ALL in text box prompt
Product name = ?P_Product Name? or 'ALL' in (?P_Product Name?)

14.What is Defect/Bug/Error Life Cycle?
A:Defect--During requirement phase if we find its called as defect. A defect in requirement.
Bug-----During testing phase if we find its called a BUG.
Error Life Cycle-
1) Open
2) Assigned(assigned to dev)
3) Dev-passed(after fixing)
4) Under testing(when testing team receives the fix)
5) Closed (if working fine)

15.How to create users and permissions in cognos?
A:Users and Permissions can be given in Access manager - Administration.
Individual users can be created using their names or their Ids (if any) in Access manager and then be given permissions.
This tool is loaded when you install Cognos 7.x in your machine.

In content manager--we have one component called cognos process manager, using process manager we can integrate with third party tools like LDAP or NTLM. we cannot create users in report net directly. in real time we can use LDAP.

16.What is the difference between macros and prompt?
A:Macro is set of instructions to run report.

Prompt is just like a dialog box which will ask user to select/enter the information what he needs.

17.What is Cognos Visualizer and Cognos Scripting?
A: Cognos scripting is like a language, with this we will create macros, Macro is a set of instructions to run a report.
Cognos Visualizer is a tool for creating charts(bar, pie, histogram......) & maps using data sources(data files like mdc,iqd,csv,excel etc).

18.What is query subject?
A query subject is the basic building block in Framework Manager. A query subject is a set of query items that have an inherent relationship.

19.What are the Ways to Import Data into Catalog?
A: Create a catalog with .cat file...
Report studio has two SQL tabs one native SQL and another one cognos SQL which one is get more preference? which one we need to consider?
Cognos sql

20.When we save a report in report studio with what extn it save?
A:It saves as .XML
It will save as .CRR
When we save the report it save our specifications like u can choose(in report studio RUN MENU by clinking down icon) HTML,XML,CSV,OR PDF Format if u save the report HTML u can also run report different format as u like or different language also Depends on the format on which the report is being run.
By default its .HTML format. The report can be made to run in any formats like HTML, PDF, XLS, CSV ( Comma separated format) and XML and based on that, report can be saved in either .HTML, .pdf, .xls, .csv or .XML
When you r viewing the report in the package is shown with extension .crr

21.How can I convert a list report/Cross tab Report in Cognos EP series into a bar chart or pie chart etc.?
A:This can be done in Impromptu administrator

22.How many numbers of cubes can we create on a single model? How can we navigate between those cubes?
A: Using a single Model, we can create as many number of cubes u want. By using the dimension views...etc. Regarding the navigation, when we save cubes, these act as separator multidimensional databases. There wont be any relations. Navigate means opening the cubes separately.

23.What is the difference between drill down and drill through?
A:Drill down means it will navigate from summary information to Detail level information within report. Drill through means it will navigate from summary to detail from report to report.

24.What is the difference between group and association?
A:In cognos 'group' is used to suppress the duplicate values. And 'associate' is used to suppress the values if it is one-one relationship.
Group - It Eliminates the Duplicate values from the report break the columns. Its having a one to many relationship.
Association-It Eliminates the Duplicate values from the report and its having one-one relationship.
Both Group & Association eliminate duplicates in a column.But we cannot use Association alone.It should have one to one relationship with grouped column.
Using Association is a performance enhancement technique.

25.What is Cardinality?
A:Cardinality is nothing but relation between tables like
One to One,
One to Many and
Many to Many.
Cardinality is the nature of the relationship. 1-1,1-many relation ships Cardinality is the nature of the relation between two query items

26.How to test reports in cognos?
A:Go to power play transformer, on toolbar select RUN menu. In RUN menu there is one option that is TEST BUILD you can test ur report there.
You may also test the outcome of a report by writing and executing SQL Queries and comparing the outcome with the report outcome.

27.How u create security to reports?
A:Basically there are two types of securities for any object Levels of Securities:
1. Database level Security
2. Row level security
Even we can provide LDAP security too.
By setting up the Governors in frame work manager also we can give the security to the data accessing through the report.

28.How to configure the Cognos configurations to work in the Windows 2000 Server machine?
A:Go to Cognos Configuration and see the settings for cognos in the explorer page. There u can set for win2000 server

29.What is drill down and slicing and dicing what are the difference between them?
Drill down means it will explains the summary level information to lowest level information.
Slicing is nothing but cutting edge of the cube.
Dicing is nothing but viewing the cube in all possible directions.
Drill down is the way to get the more detailed data.
Slicing and dicing is to get the data according to where clause.
30.What are the enhancements in Cognos Report net ?
A:Enhancements in Cognos report net is Cognos 1.0, 1.1, MR1.1, MR1.2 Cognos 1.0,1.1, MR1.1, MR1.2 AND cognos 8, 8.1.1MR, 8.1.2MR, now new version is 8.2
Enhancement in cognos in the list report:-
1) Apply list column title styles
2) Apply list column body styles
3) Apply table styles

31.Explain the different stages in creating a report in cognos report net?
A: Open cognos connection in that select Report studio or query studio it will displays there u have packages(ex: go sales and go retailer (default)) click on that it display Report studio in that select Object pane select required query subject then click run it displays ur report on report viewer screen.

32.What is Report item?
A:Report item is nothing but a query item when it is drag and drop into the work area.
Example in Go sales and retailers package-> Product (is a query subject) and-> Product line is( a query item)->when PL is dropped into the work area then its a report item.

33.How to perform single sign on in Cognos by using URL?
A:In cognos configuration under authentication allow anonymous access should be false. In cgi-bin properties (under iis) the enable anonymous access should be false.

34.What is loop in framework manager?
A:Loop is closed path in report net it called as ambiguous relationship. That means a query subject contains multiple paths to retrieve the data. It is an exception to resolve to create a shortcut on that query subject otherwise it displays wrong results in reports and performance is degrades.
An undefined join between two tables is known as loop. To resolve loop delete the joins, if these joins are necessary then we have to create shortcuts nothing but alias tables. Place the joins in alias tables.
A loop is a closed path in frame work manager due to joins.
we ill resolved it by creating allias and creating short cuts.

35.How you migrate reports to cognos 8 from previous versions?
A:Migration means report net reports to cognos8.rn Upgrade .bat/rs upgrade .sh is a standalone utility that upgrades a single report specification at a time out side of the cognos.

36.What are the filters in Framework Manager and Report Studio?
A:Filters in framework manager are
1. Standalone filters
2. Embedded filters
Report studio Filters are
1. Detail filters
2. Summary filters

37.How u migrate the reports from impromptu to reportnet???
A:It's possible to to migrate impromptu reports to reportnet using the migration tool(own by reportnet
1.1)Using this syntax: migratetocrn
HERE crn----Cognos RoportNet

38.What is IQD? What is contained in IQD?
A:IQD stands for Impromptu query definition. It is report save extension with .iqd it is use for creating a cube in power play transformer
IQD is Impromptu Query Definition. It contains the SQL query to display the data from the database and SQL query is automatically created based on the columns selected from database.
How we check the errors before running the report, Plz let me know the answer?
Before u run a report. U have an option called 'valididate report' in run menu..
Then u can find the errors what u made.
Actually we are migrating one project to ACTUATE business intelligence solution to

39.What are the special features in Cognos reportnet?
CRN is web based tool. So it will very useful to view the reports very easily. So that they preferred CRN

40.How you create IQD In ReportNet Framework?
Open framework manager click on any query subject and go Open the properties dialogue box and u can observe the EXTERNALIZE METHOD u change the iqd.

41.How to pass multiple values from pick list prompt to sub report filter?
#parameter1#. Give this in ur filter where u pass multiple values

42.What is Stitch Query in Reportnet?
Framework Manager generates a separate query for each 'FACT' table and joins the result set. This is called Stitched query.
I think when there is no join between two tables and we are dragging in 2 cols from 2 diff tables which doesn’t have joins then cognos will automatically build 2 or more select clauses with full outer join stitching in the 2 select clauses this is called stitched query

43.What is Snapshot ?
A Snapshot is a Permanent Local Copy Of the Report.Snapshot is Static Data Source it is saved with .imr File it is suitable for Disconnected Network....

44.What is cube size?
2.0 GBIt depends on ur project requirements.

45.What is log in cognos?
While creating reports or creating models logs have all information till session closed.

46.What is associated grouping? And how it works in cognos impromptu?
You can associate one or more data items with a grouped data item. An associated data item should have a one-to-one relationship with the grouped data item. For example, Order No. is a grouped data item.
For each order number, there is an Order Date. Order No. and Order Date have a one-to-one relationship.

47.What are the limitations of cognos reportnet?
In CRN we can't view multi dimensionally,
-we cant' see a report in excel,
-we can't format a report in CRN
Report net does not support drill through and also bursting of reports is no possible in report net. Also it does not support dimensional analysis

48.What is loop in framework manager ?
Loop is an very dangerous exception in framework manager we can resolve the loop create alias table. loop Display wrong results in the reportnet. A Loop is a Closed Path Circuit....
Avoid loops: using shortcuts.We have ambiguous relationships types:Hierarchical relationships Recursive relationships Multi-valid relationships. To avoid these relationships using shortcuts.

49.What are the different ways of adding data in Transformer?
In transformer u import metadata from architect or catalog to create a cube. We just import metadata. we don’t add data to it

50.What are slowly changing dimensions? Why we are used SCD?
Slowly Changing Dimensions are those whose data are not fixed.

SCD types:
SCD type 1: Whose data is not fixed.
Historical data are not saved. Data keeps on changing.-> Current Data
SCD type 2: Whose data is fixed and also save historical data.
SCD type 3: Like SCD type 2 only but save historical data in another table. In one table data will updated and in another table historical data will be there.
Depends what we are going to use a DWH or Database.

51.What r the names of the reports that u prepared?
List report, Cross tab report, pie charts etc....
Master detail, Drill through, Cascading List, Cross tab, Chart, Map report are the basic report of Cognos.


52.What is the importance of Dimension in the cognos.?
Without Dimension and Fact cannot make a relation between tables it could not be use for joins and retrieve the data as a form of reports in cognos. Dimension is a major subject area through which we can analysis our business.

53.What is exact catalog size?

There is no limit for catalog size. It may be 3MB Or 4MB.

54.Give me some examples for Life cycle reporting, I mean which life cycle we will use for reporting?
There is no specific reporting life cycle. We can generate the reports from data warehouse/data marts or we can generate the reports directly from OLTP systems. What I mean generating reports from OLTP systems is loading data into one system and generating the reports. But this is not recommended. This will depends on the business.
1) Generating reports from the OLAP interface system retrieving the data from the data warehouse to generating forms and reports.
2) They can use Business intelligence project life cycle.

Cognos Summaries Functions

Aggregate:

Returns a calculated value using the appropriate aggregation function, based on the aggregation type of the expression.

Syntax: aggregate ( expr [ auto ] )
aggregate ( expr for [ all | any ] expr { , expr } )
aggregate ( expr for report )

Count:
Returns the number of selected data items excluding NULL values. The keyword distinct is available for backward compatibility of expressions used in previous versions of the product.

Syntax: count ( [ distinct ] expr [ auto ] )
count ( [ distinct ] expr for [ all | any ] expr { , expr } )
count ( [ distinct ] expr for report)

Example: count ([gosales_goretailers].[Products].[Product name]) - (o/p: 41, 15, 21)
Count ([Product type] for all [Product line]) ------------ (o/p: 5, 4, 4)
Count ([Product type] for any [Product line]) ----------- (o/p: 5, 4, 4)
Count ([Product type] for Report) ------------------- (o/p: 21 for all)


Maximum:
Returns the maximum value of selected data items. The keyword distinct is available for backward compatibility of expressions used in previous versions of the product.

Syntax: maximum ( [ distinct ] expr [ auto ] )
maximum ( [ distinct ] expr for [ all | any ] expr { , expr } )
maximum ( [ distinct ] expr for report )

Example: maximum ([Quantity]) -------------------------------- (o/p: 962)
maximum ([Quantity] for all [gosales_goretailers].[Products].[Product type])
(o/p: 344,436…)
Maximum ([Quantity] for any [gosales_goretailers].[Products].[Product name])
(o/p: 44,78,….)
Maximum ([Quantity] for report) ------------------- (o/p: 1,646)


Minimum:
Returns the minimum value of selected data items. The keyword distinct is available for backward compatibility of expressions used in previous versions of the product.

Syntax: minimum ( [ distinct ] expr [ auto ] )
minimum ( [ distinct ] expr for [ all | any ] expr { , expr } )
minimum ( [ distinct ] expr for report )

Example: minimum ([Quantity]) -------------- (o/p: 43,330, 142,150)
Minimum ([Quantity] for all [Product type]) ---- (o/p: 6, 4, 4, ….)
Minimum ([Quantity] for any [Product type]) – (o/p: 6, 4, 4...)
Minimum ([Quantity] for report) ----------------- (o/p: 2)

Moving-Average:
Returns the value by adding the values to the previous one based on the numeric_exp.

Syntax: moving-average ( numeric_expr , numeric_expr [ at exp {, expr } ] [ ] [ prefilter ] )
moving-average ( [ distinct ] numeric_expr , numeric_expr [ ] [ prefilter ] )
::= for expr {, expr } | for report | auto

Example: moving-average([Quantity],2)
(o/p:
Quantity Moving average
198,676 198,676
345,096 271,886
95,552 220,324
96,246 95,889


Moving-Total:
Returns the value by adding the values to the previous one based on the numeric_exp

Syntax: moving-total ( numeric_expr , numeric_expr [ at exp {, expr } ] [ ] [ prefilter ] )
moving-total ( [ distinct ] numeric_expr , numeric_expr [ ] [ prefilter ] )
::= for expr {, expr } | for report | auto

Example: moving-total([Quantity],3)
Output:Quantity Moving average
198,676 198,676
345,096 543,772
95,552 639,324
96,246 536,894


Percentage:
Returns the percent of the total value for selected data items. Only used in relational Data sources.

Syntax: percentage ( numeric_expr [ at exp {, expr } ] [ ] [ prefilter ] )
percentage ( [ distinct ] numeric_expr [ ] [ prefilter ] )
::= for expr {, expr } | for report | auto

Example: percentage ([Quantity]) - (o/p: 16%, 12%, 10 %....)

Report: Percentage.

Percentile:
Returns a value, on a scale of one hundred, that indicates the percent of a distribution that is
equal to or below the selected data items.

Syntax: percentile ( numeric_expr [ at exp {, expr } ] [ ] [ prefilter ] )
percentile ( [ distinct ] numeric_expr [ ] [ prefilter ] )
::= for expr {, expr } | for report | auto

Example: percentile ([Quantity]) -------- (o/p: 100%, 95%, 90%)

Quantile:
Returns the rank of a value for a range that you specify. It returns integers to represent any range of ranks, such as 1 (highest) to 100 (lowest).

Syntax: quantile ( numeric_expr, numeric_expr [ at exp {, expr } ] [ ] [ prefilter ] )
quantile ( [ distinct ] numeric_expr, numeric_expr [ ] [ prefilter ] )
::= for expr {, expr } | for report | auto

Example: quantile ([Quantity], 5) --- (o/p: 1, 2, 3, 4, 5)

Quartile:

Returns the rank of a value, represented as integers from 1 (highest) to 4 (lowest), relative to
a group of values.

Syntax: quartile ( numeric_expr [ at exp {, expr } ] [ ] [ prefilter ] )
quartile ( [ distinct ] numeric_expr [ ] [ prefilter ] )
::= for expr {, expr } | for report | auto

Example: quartile ([Quantity]) ---------- (o/p: 1, 2, 3, 4)


Rank:

Returns the rank value of selected data items. If two or more rows tie, then there is a gap in the sequence of ranked values (also known as Olympic ranking).

Syntax: rank ( expr [sort_order] {, expr [sort_order] } [ at exp {, expr } ] [ ] [ prefilter ] )
rank ( [ distinct ] expr [sort_order] {, expr [sort_order] } [ ] [ prefilter ] )
::= for expr {, expr } | for report | auto

Example: rank [Quantity]) ----------------- ( o/p: 1, 2, 3, 4, …..)

Running-Average:

Returns the running average by row (including the current row) for a set of values

Syntax: running-average ( numeric_expr [ at exp {, expr } ] [ ] [ prefilter ] )
running-average ( [ distinct ] numeric_expr [ ] [ prefilter ] )
::= for expr {, expr } | for report | auto

Example: running-average [Quantity]) ------------ (o/p:
Quantity Moving average
198,676 198,676
345,096 271,886
95,552 213,108
96,246 183,892

Running-Count:
Returns the running count by row (including the current row) for a set of values.

Syntax: running-count ( numeric_expr [ at exp {, expr } ] [ ] [ prefilter ] )
running-count ( [ distinct ] numeric_expr [ ] [ prefilter ] )
::= for expr {, expr } | for report | auto

Example: running-count([Quantity]) ------- (o/p: 1, 2, 3, … )


Running-Difference:

Returns a running difference by row, calculated as the difference between the value for the current row and the preceding row, (including the current row) for a set of values.

Syntax: running-difference ( numeric_expr [ at exp {, expr } ] [ ] [ prefilter ] )
running-difference ( [ distinct ] numeric_expr [ ] [ prefilter ] )
::= for expr {, expr } | for report | auto

Example: running-difference ([Quantity]) --(o/p: null, 146420, -249,544, …..)


Running-Maximum:

Returns the running maximum by row (including the current row) for a set of values.

Syntax: running-maximum ( numeric_expr [ at exp {, expr } ] [ ] [ prefilter ] )
running-maximum ( [ distinct ] numeric_expr [ ] [ prefilter ] )
::= for expr {, expr } | for report | auto

Example: running-maximum ([Quantity]) --------- (o/p: 198676, 345096)

Report: Running

Running-Minimum:

Returns the running minimum by row (including the current row) for a set of values.

Syntax: running-minimum ( numeric_expr [ at exp {, expr } ] [ ] [ prefilter ] )
running-minimum ( [ distinct ] numeric_expr [ ] [ prefilter ] )
::= for expr {, expr } | for report | auto

Example: running-minimum ([Quantity]) --------- (o/p: 198676, 198676, 95552, )

Running-Total:

Returns a running total by row (including the current row) for a set of values.

Syntax: running-total ( numeric_expr [ at exp {, expr } ] [ ] [ prefilter ] )
running-total ( [ distinct ] numeric_expr [ ] [ prefilter ] )
::= for expr {, expr } | for report | auto

Example: running-total ([Quantity]) ----------- (o/p: 198676, 543772….)

Standard-Deviation:

Returns the standard deviation of selected data items. The keyword distinct is available for backward compatibility of expressions used in previous versions of the product.

Syntax: standard-deviation ( [ distinct ] expr [ auto ] )
standard-deviation ( [ distinct ] expr for [ all | any ] expr { , expr } )
standard-deviation ( [ distinct ] expr for report )

Example: standard-deviation ([Quantity]) ------ (o/p: 50.628, 69.098 …)
standard-deviation ([Quantity] for all [Product type]) -------- (o/p: 50.628, 69.098, …)
standard-deviation ([Quantity] for any [Product type]) ------ (o/p: 50.628, 69.098 …)
standard-deviation ([Quantity] for report) -------------------- (o/p: 59.70671027)

Report: Standard deviation

Standard-Deviation pop:
Computes the population standard deviation and returns the square root of the population variance. The keyword distinct is available for backward compatibility of expressions used in previous versions of the product.

Syntax: standard-deviation-pop ( [ distinct ] expr [ auto ] )
standard-deviation-pop ( [ distinct ] expr for [ all | any ] expr { , expr } )
standard-deviation-pop ( [ distinct ] expr for report )

Example: standard-deviation-pop ([Quantity]) ------- (o/p: 50.62240898, 69.09035128)
standard-deviation-pop ([Quantity] for all [Product type]) -
(o/p: 50.62240898, 69.09035128)
standard-deviation-pop ([Quantity] for any [Product type])
(o/p: 50.62240898, 69.09035128)
standard-deviation-pop ([Quantity] for report) ---
(o/p: 59.70601702, )


Total:
Returns the total value of selected data items. The keyword distinct is available for backward compatibility of expressions used in previous versions of the product.

Syntax: total ( [ distinct ] expr [ auto ] )
total ( [ distinct ] expr for [ all | any ] expr { , expr } )
total ( [ distinct ] expr for report )

Example: total ([Quantity]) --------------- (o/p: 198,676, 345,096)
Total ([Quantity] for all [Product type]) ------- (o/p: 198,676, 345,096)
Total ([Quantity] for any [Product type]) ----- (o/p: 198,676, 345,096)
Total ([Quantity] for report) --------------- (o/p: 2215354)

Report: Total
Variance:
Returns the variance of selected data items. The keyword distinct is available for backward compatibility of expressions used in previous versions of the product.

Syntax: variance ( [ distinct ] expr [ auto ] )
variance ( [ distinct ] expr for [ all | any ] expr { , expr } )
variance ( [ distinct ] expr for report )

Example: variance ([Quantity]) ----- (o/p: 2563.244, 4774.543…)
variance([Quantity] for all [Product type]) -------- (o/p: 2563.244, 4774.543…)
variance([Quantity] for any [Product type]) ------- (o/p: 2563.244, 4774.543…)
variance([Quantity] for report) ---------------------- (o/p: 3,564.89125097)


Variance-pop:

Returns the population variance of a set of numbers after discarding the nulls in this set.

Syntax: variance-pop ( [ distinct ] expr [ auto ] )
variance-pop ( [ distinct ] expr for [ all | any ] expr { , expr } )
variance-pop ( [ distinct ] expr for report )

Example: variance-pop([Quantity]) ------- (o/p: 2,562.6282905, 4,773.47663957)
variance-pop([Quantity] for all [Product type]) -----------
(o/p: 2,562.6282905, 4,773.47663957)
variance-pop([Quantity] for any [Product type]) --------
(o/p: 2,562.6282905, 4,773.47663957)
variance-pop([Quantity] for report) ------ (o/p: 3,564.80846781)

DatawareHouse Questions

What is data warehouse?

A: Data ware house is one type of database which is specially designed for analysis the business not for transactional purpose.

What is Data mart?

A: Data mart is a subset of Data ware house.

Difference between Data ware house and data mart?

A: Data ware house is maintain the total organization of data, where as data mart is maintained particular subject.

Operational data sources (ODS)?

A: The Operational Data Storage (ODS) system provides a way to save data that can be shared by multiple flow components or flows. ODS is a type of database that serves as a quick-access data storage. An ODS system lets you perform many queries on small amounts of data, and differs from a data warehouse, in which large amounts of information is stored and queries are run on a large volume of data

Dimensional Modeling?

A: Dimensional Modeling is a design concept used by many data warehouse desginers to build thier data warehouse. In this design model all the data is stored in two types of tables - Facts table and Dimension table. Fact table contains the facts/measurements of the business and the dimension table contains the context of measurements ie, the dimensions on which the facts are calculated.

E-R modeling?

A: ER modeling that models an ER diagram represents the entire businesses or applications processes. This diagram can be segregated into multiple Dimensional models

Difference between Dimensional Modeling and E-R modeling?

A: Dimensional Modeling have only physical model, It is used for de-normalizing the rolap/molap design.

E-R modelings have logical model and physical model. It is used for normalizing the OLPT database design.

What is the Difference between OLTP and OLAP?

A: OLTP refers to transactional data or data captured at the point of transaction, whereas, OLAP is a multidimensional representation of data which throws out summary level information.

What is Data Transformation?

A: It is a process of converting the data and cleansing the data into a required format.

1.Data Merging: It is a process of combining the data from the multiple input pipelines (sources) to single output.

2.Data Cleaning: It is a process of removing unwanted data. It is a process of changing the inconsistence data into a consistence format.

3.Data scribing: It is process of deriving new data definitions according to the business required.

4.Data aggregation: It is a process of calculating the summarizes using aggregation values.

What is Data Extraction?

A: It is a process of reading the data from various systems like databases, operational files/sources, and flat files xml files.

What is loading?

A: Is the process of inserting new data into target system.

What is Fact less fact table?

A: Fact less fact table is nothing but a fact table which doesn’t have any measures.

What is schema?

A: schema is collection of database objects including tables, views, indexes, and sysnoms.

What is the difference between star schema and snowflake schema?

A: Star schema contain de-normalized data. It contain less no of joins, so performance will be increase

Snowflake schema contains normalized data. It contain more no of joins, so performance will be poor.

What is Galaxy schema?

A: Galaxy schema is known as fact constellation schema. It requires no of fact tables and dimensional tables.

What is fact table?

Fact table contains the measurements or metrics or facts of business process

Fact is a numeric field which is the key column to the business. But all numeric fields are not facts.

Fact is key performance indicator to analyze the business

It is a table which contains factual information of a business process.

Ex: Quantity, Revenue, Unit cost

Types of facts?

• Additive –   facts that can be added across all dimensions.
• Semi Additive –facts that can be added across some dimensions and not across others.

Ex: Bank Balance

• Non Additive – Facts that cannot be added across any dimension. Non-additive facts are the facts which cannot be summed up for any of the dimensions present in the fact table.

ex: Ratio, avg

What is dimension?

A: Which will give  the description info of the business.

Ex: ordername.orderdate.description

Types of dimensions?

A: Conformed dimension: A dimension which can be shared with multiple fact table

Junk dimension: It is collection of random transactional codes, flags and/or, text attributes that are unrelated to any particular dimension.

Degenerated Dimension: A dimension which can be stored in fact table that is called” Degenerated Dimension”

Degenerated Dimension is a dimension which is derived from the fact table. It doesn’t have own dimension table

What is Staging Area?

A: It is a temporary storage location used for processing the data.

What is a Surrogate key?

A: Surrogate key is a unique identifier. It is used to identify each row of a table uniquely. It also helps to track slowly changing dimensions. It replaces all natural keys. Surrogate keys are system generated keys,they are not derived from any data sources.

Surrogate key is system generated sequence number to be used as a primary key.

What is Metadata?

A: Data about data.

 

What is cube?

A: Cube is a multidimensional representation of data. It is used for analysis purpose. A cube gives multiple views of data.

What is Granularity?

A: Granularity describes, up to what level of detail the measures are stored in the data warehouse. The data with the lowest level of granularity can be reshaped to meet different needs. Granular data can be easily summarized.

What is Candidate key?

A: Candidate Key is a column or a set of columns available in a table which allows distinct values and not null values.

Slowly changing dimension?

A: It explains how to capture the changes over period of time.

Slowly changing dimension Type1: It maintain only current data doesn’t   maintain historical data

Slowly changing dimension Type2: It maintain current data as well as historical data. For every changes in the source if inserts a new record in the target

Slowly changing dimension Type3: It keeps the current and previous information.