*If necessary
Paging data in your application, be it thin or thick client, is a frequent need: list of customers, address directory, invoice details, etc. – any number of situations may crop up where the paging grid in the UI becomes a necessity.
If your data is big enough to be paged in your display, then, for the most part, it is a large enough set that you are not advised to pull all of the set back at once to be cached by the UI, particularly if your storage medium is SQL Server. A lot of you are probably familiar with using row_number, the OVER statement and maybe even a CTE to resolve ordering the data and grabbing groups at a time for the application. But what do you do if you need to support paging, and you are not sure what the table is going to be?
I will point out that the title is asterisked, my caveat to this whole discussion is: should this be necessary. Beside that point though, it is a relatively interesting issue. How would you implement a paging query when you don't know what table you might want, or when the schema might be unknown to you? (Again, this should not be used widespread, but scenarios can come up where this makes sense).
The first requirement in this scenario is getting table meta-data back to your application; in SQL 2005 and above, this is a simple matter of querying sys.tables. After you have the table list, the other two variables to declare in this scenario are your page size and what page in the paged data you desire. The full script is provided below – most of it is pretty straight forward, except that you will notice that I pull a little stunt in getting the column(s) to do the order by on for the row_number and over functions.
Given that we need something to order this data by, and we don't know the schema, I query the information_schema tables to get the tables primary key column or columns – if none, it will just take the first column. Not the most advanced, but then again, this script shoots blind – which is the point of this example.
The other little bonus is that it also removes column types from the query that would likely choke an auto-column generating .Net control – varbinary, xml, etc. are excluded in the columns dynamically selected from the table parameter – to get a closer look at this, you can modify the script and print the @TableSelectColumns variable.
I would be interested to hear how others might apply this scenario, so if you are up for it, post how you used this or a similar script!
declare
@TableObjectId int, @PageSize int,
@PageIndex int, @TableRows int,
@TableName nvarchar(75), @KeyOrderColumn nvarchar(50),
@StrSQL nvarchar(4000), @PageRowStart int,
@PageRowEnd int, @TableSelectColumns nvarchar(2000),
@Params nvarchar(50)
-- Set the parameters
set @TableName = Your Table Name Here
set @PageSize = Your Page Size Here
set @PageIndex = Your Page Index here
set @StrSQL = 'select @columnsOUT = coalesce(@columnsOUT + '','', '''')+ ''[''+c.name+'']''
from sys.columns c
inner join sys.types t on t.system_type_id = c.system_type_id
where c.object_id = OBJECT_ID(''' + @TableName + ''')
and t.system_type_id not in (34,35,98,99, 165,173,241,231)'
set @Params = '@columnsOUT nvarchar(2000) output'
exec sp_executesql @StrSQL,@Params, @columnsOUT = @TableSelectColumns output
if(@PageIndex < 2)
set @PageRowStart = 0
if(@PageIndex > 1)
set @PageRowStart = ((@PageIndex * @PageSize) - @PageSize)
set @PageRowEnd = (@PageIndex * @PageSize)
set @TableObjectId = (select OBJECT_ID(@TableName))
-- select the key column(s)
select @KeyOrderColumn = coalesce(@KeyOrderColumn + ', ', '')
+ '['+convert(nvarchar(25),B.COLUMN_NAME,20)+']'
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS A
, INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B
where CONSTRAINT_TYPE = 'PRIMARY KEY'
and A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
and object_id(A.TABLE_NAME) = @TableObjectId
-- Grab the first column since we have no key column declared
if(len(@KeyOrderColumn) < 1 or @KeyOrderColumn is null)
begin
set @KeyOrderColumn = ( select name from sys.syscolumns where id = @TableObjectId and colorder = 1)
end
set @TableRows = (select rows from sys.partitions where object_id = @TableObjectId and index_id < 2)
if(@PageSize > @TableRows)
begin
set @StrSQL = 'select ' + @TableSelectColumns + ' from ' + @TableName + ' order by ' + @KeyOrderColumn
end
if(@PageSize < @TableRows)
begin
set @StrSQL = 'select ' + @TableSelectColumns
+ ', RowNumber into #Results from (select *, row_number() over(order by '
+ @KeyOrderColumn + ' ) as RowNumber from '
+ @TableName + ') SortTable where RowNumber > '
+ cast(@PageRowStart as nvarchar) + ' and RowNumber < '
+ cast(@PageRowEnd as nvarchar)
+ ' alter table #Results drop column RowNumber '
+ ' select * from #Results'
end
exec sp_executesql @StrSQL
Tags: paging,
t-sql
Categories: SQL Programming
It has been a terribly busy month – I actually started writing this post at the end of January, but only now am I able to finish it and fire it off to my blog. Feel free to post or e-mail me with any follow up questions
I ran across an issue at one client where a calculated measure was not behaving as desired (though it was behaving as designed!). Since this is a topic not likely to be broached in bar conversation or in the break room over hot tea, I wanted to cover a few cogent points here that might be useful to others.
Though several items were at the root of the measure not being calculated as expected, the issue that is common to anyone using calculated members is a misunderstanding of calculation passes and solve order (more here on MSDN). The MSDN article does a great job of showing calculated members in an MDX query, using solve order and the different result sets you will get with differing solve order sets, however the article feels a little dearth in its explanation of calculation passes and why solve order sometimes is necessary for correct calculations in your queries.
Analysis Services loads data into the cube in passes – on the first pass (pass 0) leaf members are loaded from fact data, the second pass (pass 0.5) loads the values of cells associated with sum, count, min, max, distinct and distinct count aggregate functions. After this is completed, then the load process can start on the cube calculations represented in the calculations tab. This is where, though intuitively you may recognize this, it is important to understand how calculation passes and solve order come into play.
For this example, I will reference the ubiquitous AdventureWorks example, now available via CodePlex:
In this cube, the calculated member [Total Sales Ratio to All Products] has a calculation reference to [Total Sales Amount]. Calculation pass comes into play here via the order of the calculated sets in the script. Notice that [Total Sales Amount] is at line 2 and [Total Sales Ratio to All Products] – just via placement in the calculation pane, you are declaring the pass order for these cube calculations. This is easy to see in this example, however on the customer that was experiencing a calculation problem, they literally had hundreds of cube calculations, with multiple developers having touched the product, so the last person in to work on new calculations and tweak old ones had not noticed the order of referenced calculations and thus had a hard time finding where the calc had gone wrong.
It is also important to remember that the last, or highest pass trumps previous passes when it comes to calculations. In the AdventureWorks cube, if we added another measure at line three that named [Total Sales Amount Revised], with the expression = [Total Sales Amount], then provided nothing else changed, we would then have two measures that had the same value. If we add to the script down at the last line that [Total Sales Amount] equals 20, then both [Total Sales Amount] and [Total Sales Amount Revised] would now equal 20 as the last pass wins in assigning values to the calculated members.
This is where FREEZE comes into play – in the scenario above, we can say: FREEZE([Total Sales Amount Revised]) on the line right after we set [Total Sales Amount Revised]'s value, and then when [Total Sales Amount] gets reset at the end of the script, the [Total Sales Amount Revised] will retain its values as calculated in the earlier pass. This can be incredibly useful for using role playing calculations in financial scenarios (which was part of my client's mission).
Solve order then, is used when you are writing out your query or sub-cube in MDX and need to replicate the calculation passes that the Calculations Tab manages in Visual Studio. You do not normally use solve_order in calculations in the cube designer, however it can be used to give Analysis Services your desired calculation path if in one script block on the calculations pane, you are doing two more advanced calculations that are linked. Since these calculations would be solved in the same pass, i.e., you placed them within the same scope statement, etc, then solve_order would explicitly spell out the order for the engine to calculate. Mostly, however, solve_order is used in MDX query examples as provided in the MSDN article.
Tags: analysis services,
mdx
Categories: Business Intelligence |
SSAS