Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

dynamic-partition

  • 20 August 2013
  • Author: Anil
  • Number of views: 10252
  • 0 Comments
  Rownum	Quries	Location
1 select * from FactInternetSales
WHERE $PARTITION.PART_FUNC(OrderDateKey) = '1'
; E:\Anil Research\AdventureWorks
2 select * from FactInternetSales
WHERE $PARTITION.PART_FUNC(OrderDateKey) = '2'
; E:\Anil Research\AdventureWorks
3 select * from FactInternetSales
WHERE $PARTITION.PART_FUNC(OrderDateKey) = '3'
; E:\Anil Research\AdventureWorks
4 select * from FactInternetSales
WHERE $PARTITION.PART_FUNC(OrderDateKey) = '4'
; E:\Anil Research\AdventureWorks
5 select * from FactInternetSales
WHERE $PARTITION.PART_FUNC(OrderDateKey) = '5'
; E:\Anil Research\AdventureWorks
6 select * from FactInternetSales
WHERE $PARTITION.PART_FUNC(OrderDateKey) = '6'
; E:\Anil Research\AdventureWorks
7 select * from FactInternetSales
WHERE $PARTITION.PART_FUNC(OrderDateKey) = '7'
; E:\Anil Research\AdventureWorks
8 select * from FactInternetSales
WHERE $PARTITION.PART_FUNC(OrderDateKey) = '8'
; E:\Anil Research\AdventureWorks
9 select * from FactInternetSales
WHERE $PARTITION.PART_FUNC(OrderDateKey) = '9'
; E:\Anil Research\AdventureWorks
10 select * from FactInternetSales
WHERE $PARTITION.PART_FUNC(OrderDateKey) = '10'
; E:\Anil Research\AdventureWorks
11 select * from FactInternetSales
WHERE $PARTITION.PART_FUNC(OrderDateKey) = '11'
; E:\Anil Research\AdventureWorks
12 select * from FactInternetSales
WHERE $PARTITION.PART_FUNC(OrderDateKey) = '12'
; E:\Anil Research\AdventureWorks
13 select * from FactInternetSales
WHERE $PARTITION.PART_FUNC(OrderDateKey) = '13'
; E:\Anil Research\AdventureWorks
14 select * from FactInternetSales
WHERE $PARTITION.PART_FUNC(OrderDateKey) = '14'
; E:\Anil Research\AdventureWorks
15 select * from FactInternetSales
WHERE $PARTITION.PART_FUNC(OrderDateKey) = '15'
; E:\Anil Research\AdventureWorks
16 select * from FactInternetSales
WHERE $PARTITION.PART_FUNC(OrderDateKey) = '16'
; E:\Anil Research\AdventureWorks
17 select * from FactInternetSales
WHERE $PARTITION.PART_FUNC(OrderDateKey) = '17'
; E:\Anil Research\AdventureWorks
18 select * from FactInternetSales
WHERE $PARTITION.PART_FUNC(OrderDateKey) = '18'
; E:\Anil Research\AdventureWorks
19 select * from FactInternetSales
WHERE $PARTITION.PART_FUNC(OrderDateKey) = '19'
; E:\Anil Research\AdventureWorks
20 select * from FactInternetSales
WHERE $PARTITION.PART_FUNC(OrderDateKey) = '20'
; E:\Anil Research\AdventureWorks
21 select * from FactInternetSales
WHERE $PARTITION.PART_FUNC(OrderDateKey) = '21'
; E:\Anil Research\AdventureWorks
22 select * from FactInternetSales
WHERE $PARTITION.PART_FUNC(OrderDateKey) = '22'
; E:\Anil Research\AdventureWorks
23 select * from FactInternetSales
WHERE $PARTITION.PART_FUNC(OrderDateKey) = '23'
; E:\Anil Research\AdventureWorks
24 select * from FactInternetSales
WHERE $PARTITION.PART_FUNC(OrderDateKey) = '24'
; E:\Anil Research\AdventureWorks
25 select * from FactInternetSales
WHERE $PARTITION.PART_FUNC(OrderDateKey) = '25'
; E:\Anil Research\AdventureWorks
26 select * from FactInternetSales
WHERE $PARTITION.PART_FUNC(OrderDateKey) = '26'
; E:\Anil Research\AdventureWorks
27 select * from FactInternetSales
WHERE $PARTITION.PART_FUNC(OrderDateKey) = '27'
; E:\Anil Research\AdventureWorks
28 select * from FactInternetSales
WHERE $PARTITION.PART_FUNC(OrderDateKey) = '28'
; E:\Anil Research\AdventureWorks
29 select * from FactInternetSales
WHERE $PARTITION.PART_FUNC(OrderDateKey) = '29'
; E:\Anil Research\AdventureWorks
30 select * from FactInternetSales
WHERE $PARTITION.PART_FUNC(OrderDateKey) = '30'
; E:\Anil Research\AdventureWorks
31 select * from FactInternetSales
WHERE $PARTITION.PART_FUNC(OrderDateKey) = '31'
; E:\Anil Research\AdventureWorks
32 select * from FactInternetSales
WHERE $PARTITION.PART_FUNC(OrderDateKey) = '32'
; E:\Anil Research\AdventureWorks
33 select * from FactInternetSales
WHERE $PARTITION.PART_FUNC(OrderDateKey) = '33'
; E:\Anil Research\AdventureWorks
34 select * from FactInternetSales
WHERE $PARTITION.PART_FUNC(OrderDateKey) = '34'
; E:\Anil Research\AdventureWorks
35 select * from FactInternetSales
WHERE $PARTITION.PART_FUNC(OrderDateKey) = '35'
; E:\Anil Research\AdventureWorks
36 select * from FactInternetSales
WHERE $PARTITION.PART_FUNC(OrderDateKey) = '36'
; E:\Anil Research\AdventureWorks
37 select * from FactInternetSales
WHERE $PARTITION.PART_FUNC(OrderDateKey) = '37'
; E:\Anil Research\AdventureWorks
38 select * from FactInternetSales
WHERE $PARTITION.PART_FUNC(OrderDateKey) = '38'
; E:\Anil Research\AdventureWorks

------------------------------------------------------
IF OBJECT_ID ( 'GetPartitionQueries', 'P' ) IS NOT NULL
DROP PROCEDURE GetPartitionQueries;
------------------------------------------------------
GO
------------------------------------------------------
CREATE PROCEDURE [dbo].[GetPartitionQueries](
@table_name nvarchar(100)
)
AS
BEGIN

DECLARE @CreateQuery VARCHAR(MAX)

SET @CreateQuery ='IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = ''Partition_'+@table_name+'_quries'')
DROP TABLE Partition_'+@table_name+'_quries;
CREATE TABLE [dbo].[Partition_'+@table_name+'_quries](
Rownum INT IDENTITY(1,1),
Quries [VARCHAR](MAX),
Location [VARCHAR](50)
);
'

EXECUTE(@CreateQuery)

-- Initialize variables!

DECLARE @SelectQuery VARCHAR(MAX),
@InsertQuery VARCHAR(MAX),
@iNextRowId int,
@iCurrentRowId int,
@iLoopControl int,
@PartitionCol varchar(100),
@PartitionFunc varchar(200)

SELECT @iLoopControl = 1
SELECT @iNextRowId = MIN(PartitionID)
FROM
[Partition_Detail_Fact]
where
Table_Name = ''+@table_name+''

-- Make sure the table has data.
IF ISNULL(@iNextRowId,0) = 0
BEGIN
SELECT 'No data is found in table!'
RETURN
END

-- Retrieve the first row
SELECT
@iCurrentRowId = PartitionID,
@PartitionCol = Partition_column,
@PartitionFunc = Function_Name
FROM
[Partition_Detail_Fact]
WHERE
Table_Name = ''+@table_name+''
and
PartitionID = @iNextRowId

-- start the main processing loop.
WHILE @iLoopControl = 1
BEGIN
-- This is where you perform your detailed row-by-row
-- processing.
-- Reset looping variables.

SET @SelectQuery= 'select * from '+ @table_name + '
WHERE $PARTITION.'+@PartitionFunc+'('+ @PartitionCol + ') = '''''+ cast(@iCurrentRowId as varchar) +'''''
;'
--print(@Partitiondate)



SET @InsertQuery =
' INSERT INTO Partition_'+@table_name+'_quries(Quries,Location)
SELECT '''+@SelectQuery+''',''E:\Anil Research\AdventureWorks''
;'
--print @InsertQuery
EXEC ( @InsertQuery )


SELECT @iNextRowId = NULL
-- get the next iRowId
SELECT @iNextRowId = MIN(PartitionID)
FROM
[Partition_Detail_Fact]
WHERE
Table_Name = ''+@table_name+''
and
PartitionID > @iCurrentRowId

-- did we get a valid next row id?
IF ISNULL(@iNextRowId,0) = 0
BEGIN
BREAK
END
-- get the next row.
SELECT @iCurrentRowId = PartitionID,
@PartitionCol = Partition_column,
@PartitionFunc = Function_Name
FROM
[Partition_Detail_Fact]
WHERE
Table_Name = ''+@table_name+''
and
PartitionID = @iNextRowId

END
RETURN
END
------------------------------------------------------
GO
------------------------------------------------------

EXEC dbo.GetPartitionQueries @table_name = 'FactInternetSales'

 

Table_Name	PartitionID	ObjectID	Partition_Column	Function_Name
FactInternetSales 1 405576483 OrderDateKey PART_FUNC
FactInternetSales 2 405576483 OrderDateKey PART_FUNC
FactInternetSales 3 405576483 OrderDateKey PART_FUNC
FactInternetSales 4 405576483 OrderDateKey PART_FUNC
FactInternetSales 5 405576483 OrderDateKey PART_FUNC
FactInternetSales 6 405576483 OrderDateKey PART_FUNC
FactInternetSales 7 405576483 OrderDateKey PART_FUNC
FactInternetSales 8 405576483 OrderDateKey PART_FUNC
FactInternetSales 9 405576483 OrderDateKey PART_FUNC
FactInternetSales 10 405576483 OrderDateKey PART_FUNC
FactInternetSales 11 405576483 OrderDateKey PART_FUNC
FactInternetSales 12 405576483 OrderDateKey PART_FUNC
FactInternetSales 13 405576483 OrderDateKey PART_FUNC
FactInternetSales 14 405576483 OrderDateKey PART_FUNC
FactInternetSales 15 405576483 OrderDateKey PART_FUNC
FactInternetSales 16 405576483 OrderDateKey PART_FUNC
FactInternetSales 17 405576483 OrderDateKey PART_FUNC
FactInternetSales 18 405576483 OrderDateKey PART_FUNC
FactInternetSales 19 405576483 OrderDateKey PART_FUNC
FactInternetSales 20 405576483 OrderDateKey PART_FUNC
FactInternetSales 21 405576483 OrderDateKey PART_FUNC
FactInternetSales 22 405576483 OrderDateKey PART_FUNC
FactInternetSales 23 405576483 OrderDateKey PART_FUNC
FactInternetSales 24 405576483 OrderDateKey PART_FUNC
FactInternetSales 25 405576483 OrderDateKey PART_FUNC
FactInternetSales 26 405576483 OrderDateKey PART_FUNC
FactInternetSales 27 405576483 OrderDateKey PART_FUNC
FactInternetSales 28 405576483 OrderDateKey PART_FUNC
FactInternetSales 29 405576483 OrderDateKey PART_FUNC
FactInternetSales 30 405576483 OrderDateKey PART_FUNC
FactInternetSales 31 405576483 OrderDateKey PART_FUNC
FactInternetSales 32 405576483 OrderDateKey PART_FUNC
FactInternetSales 33 405576483 OrderDateKey PART_FUNC
FactInternetSales 34 405576483 OrderDateKey PART_FUNC
FactInternetSales 35 405576483 OrderDateKey PART_FUNC
FactInternetSales 36 405576483 OrderDateKey PART_FUNC
FactInternetSales 37 405576483 OrderDateKey PART_FUNC
FactInternetSales 38 405576483 OrderDateKey PART_FUNC


Print
Categories: Analysis Services
Tags:
Rate this article:
No rating
Anil

AnilAnil

Highly motivated Business Intelligence Engineer having leadership abilities and team work skills as well as the ability to accomplish tasks under minimal direction and supervision. Has more than 5 years of development & implementation experience in HealthCare Data Analytics and Telecommunication

Other posts by Anil

Full biography Contact author

Please login or register to post comments.