Is it ok to create a list report out of a cube. Is it a rule to only use crosstabs?
I have a report that has first column as country and then various products as the other columns...the products are available in a heirarchy, so is country
Country Product1 Product2
US 100 101
UK 100 101
Aus
Italy
There is no issue in creating a list report using a cube,because anyway the cube is going to improve the report performance.
The purpose of cube is for pre aggregation which allows fast response to queries that would have required grouping,summarizing of rows.Crosstabs are generally use for comparative analysis and even if you use list reports the results would be fast.
but the major factor to consider is the resulting cube size i.e limit of 2Gb
I would note the 2G limitation is only a file size limit. You can have multi-file cubes where each file is <2G; I have had cubes as large as ~7G across maybe 5-10 files. Of course there can be performance issues based on server memory, category member counts, how it is partitioned, etc.
But as Arpit said, you are not restricted to crosstabs off a cube. They are just the most common.
How do you decide if list is apt for dimensional report. Could you cite an example
Lists are typically detailed data, which wont be the case with cubes, becasue they have summarized data
Well list is generally used to display detailed data that is true,but cube is not used to show only Summarized data. Cubes pre-aggregate the measures by the different levels of categories in the dimensions to enable the quick response time,so it would have data in its granular form also if required.
The drilling and slicing and dicing that an analyst would want to perform to explore the data would be immediate using a cube
The choice in creating list Vs Cross tab varies on the User requirements and depends on who will consume them, and when they will be consumed
Comparison
Use list reports to show detailed information from your database, such as product lists and customer
lists.A list report is a report that shows data in rows and columns. Each column shows all the values
for a data item in the database or a calculation based on data items in the database.
Use crosstab reports to show information in a more compact form than in a grouped list. For example, create a crosstab report to show total sales by product line generated by each sales representative. Like list reports, crosstab reports are reports that show data in rows and columns. However, the
values at the intersection points of rows and columns show summarized information rather than
detailed information.
Hope it helps :)
say my first column is product ( which is a dimension), second is current date and next 3 are measures. Would that be cross tab or list
Quote from: dssd on 22 Sep 2011 12:29:06 PM
say my first column is product ( which is a dimension), second is current date and next 3 are measures. Would that be cross tab or list
That would be a list. IMO, a typical crosstab would be products in the rows, measures in the columns with the current date stacked above the measures column or something similar to that.
What you're thinking of (4 or 5 columns lined up next to each other) is typically a list. But, you would have to ensure that you don't just select the root members when dropping the dimension into a list column so that the children and parents both show up.
My confusion stems from the fact that we do have nested crosstabs. So, having two dimensions side by side won't be wrong. Or am i missing somethin