Creating a range lookup table from a file of millions of rows

I have a file with the item id and the item type. The data looks as follows
:
ItemID ItemType
1 A
2 A
3 A
4 B
5 B
6 C
7 C
8 A
9 A
I want to create a lookup tables as follows:
Start End ItemType
1 3 A
4 5 B
6 7 C
8 9 A
Please keep in mind the file I have the ids on is millions of rows. Also,
there are gaps in the ids, (i.e. may jump from 4 to 6 no 5). Gaps are
acceptable as long as they are not too large.
Thanks in advance for any tips you can provide.I'm not sure what you mean by "gaps are acceptable as long as they are
not too large". Apparently no gaps in your sample data anyway. See if
this meets your requirements:
SELECT MIN(itemid), MAX(itemid), itemtype
FROM
(SELECT T1.itemid, T1.itemtype,
MIN(T2.itemid) AS x_itemid
FROM tbl AS T1
LEFT JOIN tbl AS T2
ON T1.itemtype <> T2.itemtype
AND T1.itemid < T2.itemid
GROUP BY T1.itemid, T1.itemtype) AS T
GROUP BY itemtype, x_itemid
If performance is an issue then you could do this for smaller subsets
of rows and then combine the results.
David Portas
SQL Server MVP
--|||David
Can I ask you, why did you join the table?
create table #test
(
itemid int not null primary key,
itemtype char(1) not null
)
insert into #test values (1,'a')
insert into #test values (2,'a')
insert into #test values (3,'a')
insert into #test values (4,'b')
insert into #test values (5,'b')
insert into #test values (6,'c')
insert into #test values (7,'c')
insert into #test values (8,'d')
select min(itemid),max(itemid),itemtype
from #test group by itemtype
What is differ between these queries?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1123000386.349876.22890@.g47g2000cwa.googlegroups.com...
> I'm not sure what you mean by "gaps are acceptable as long as they are
> not too large". Apparently no gaps in your sample data anyway. See if
> this meets your requirements:
> SELECT MIN(itemid), MAX(itemid), itemtype
> FROM
> (SELECT T1.itemid, T1.itemtype,
> MIN(T2.itemid) AS x_itemid
> FROM tbl AS T1
> LEFT JOIN tbl AS T2
> ON T1.itemtype <> T2.itemtype
> AND T1.itemid < T2.itemid
> GROUP BY T1.itemid, T1.itemtype) AS T
> GROUP BY itemtype, x_itemid
> If performance is an issue then you could do this for smaller subsets
> of rows and then combine the results.
> --
> David Portas
> SQL Server MVP
> --
>|||Hi Uri,
Replace
insert into #test values (8,'d')
with
insert into #test values (8,'a')
and see the difference
With warm regards
Jatinder Singh|||So David's script gave me a wrong output.
"jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
news:1123050090.479481.14310@.z14g2000cwz.googlegroups.com...
> Hi Uri,
> Replace
> insert into #test values (8,'d')
> with
> insert into #test values (8,'a')
> and see the difference
> With warm regards
> Jatinder Singh
>|||Hi Uri,
It gave correct ouput to me.
Start End ItemType
8 9 A -- (1)
1 3 A -- (2)
4 5 B
6 7 C
The only thing is (1) appears at top which can be easily adjusted by
using ored by clause
With warm regards
Jatinder Singh|||Hi
Should not be 1 for MIN and 9 for MAX for A?
"jsfromynr" <jatinder.singh@.clovertechnologies.com> wrote in message
news:1123056485.365685.300130@.g14g2000cwa.googlegroups.com...
> Hi Uri,
> It gave correct ouput to me.
> Start End ItemType
> 8 9 A -- (1)
> 1 3 A -- (2)
> 4 5 B
> 6 7 C
> The only thing is (1) appears at top which can be easily adjusted by
> using ored by clause
> With warm regards
> Jatinder Singh
>|||Hi Uri,
Again Let us see this
ItemID ItemType
1 A -- *
2 A -- * One Group with ItemType='a' Here
min(itemid)= 1 and max is 3
3 A -- * 1 3 A (One Row of Reuired Result)
4 B -- ^ Another Group with ItemType='b' Here
min(itemid)= 4 and max is 5
5 B -- ^ 4 5 B (Another Row of Reuired Result)
6 C -- ~Another Group with ItemType='c' Here
min(itemid)= 6 and max is 7 7 C -- 6 7 C
(Another Row of Reuired Result)
8 A -- Again 'A' is repeated but there is gap so it
is to be considerd as a
9 A -- Fresh Group
-- 8 9 A
So the resultant output produced by David's Query is Correct
Start End ItemType
1 3 A
4 5 B
6 7 C
8 9 A
I hope it made the author's requirements more clear.
With warm regards
Jatinder Singh|||The difference is that your query only gives one row per ItemType
rather than one row per contiguous sequence on ItemType. I call my
query a "condensed" or "stepped" sequence rather than an aggregation.
The point is that it shows the regions or periods over which a
particuar ItemType applies. In my interpretation that's what BTJ was
asking for.
David Portas
SQL Server MVP
--|||David
Thanks, I got it
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1123058956.842173.89690@.o13g2000cwo.googlegroups.com...
> The difference is that your query only gives one row per ItemType
> rather than one row per contiguous sequence on ItemType. I call my
> query a "condensed" or "stepped" sequence rather than an aggregation.
> The point is that it shows the regions or periods over which a
> particuar ItemType applies. In my interpretation that's what BTJ was
> asking for.
> --
> David Portas
> SQL Server MVP
> --
>

Creating a Calculated Member Based on a Range

Is there a way to create a calculated member based on a range of values?

For example, I have two measures in my cube: ResponseTime and TransactionCount.

What I want to do is create a calculated member that returns the total number of Transactions where the ResponseTime is between a certain range, i.e. between 1 and 2 seconds.

Here is what I've tried to do:

IIF( [Measures].[ResponseTime] > 1 And [Measures].[ResponseTime] < 2, [Measures].[TransactionCount], 0)

However, when I browse the cube, all the values for this calculated member are blank. Any ideas?

This problem can easily can generalized. My main problem is just finding a way to group values based on a certain range of values.

Thanks!!

Joel,

Given that you want to look at the "response time" for each individual transaction in your fact table and the cube is going to show you values aggregated based on selections that the end user makes, you may want to consider the following solution:

In your data source view:

Add a name calculation to your fact table named "ResponseTimeRangeKey" using a CASE statement:

CASE

WHEN ResponseTime <= 1 THEN 1

WHEN ResponseTime > 1 AND ResponseTime < 2 THEN 2

ELSE 3

END CASE

Next you can create a dimension table that can be used to build a cube dimension for your response time ranges using either a database view or a named query with a simple SQL statement something like this:

SELECT

1 AS ResponseTimeRangeKey,

'1 sec or less' AS ResponseTimeRangeName

UNION

SELECT

2 AS ResponseTimeRangeKey,

'1-2 sec' AS ResponseTimeRangeName

UNION

SELECT

3 AS ResponseTimeRangeKey,

'more than 2 sec' AS ResponseTimeRangeName

You can then relate your fact table to the new dimension in the data source view. Next you can create your response time range dimension and add it to your cube.

This solution has the benefit of allowing users to look at any measure in your cube using the response time ranges and will accurately aggregate your values for all dimensions.

HTH,

Steve

|||

Thanks for the help. I tried it out, and it worked brilliantly.

|||

I have somewhat the same scenario, I need to find a way to group values based on certain range of values. I have a calculated member, YTD investment. From my fact table I created a named calculation field for SegmentID with initial value of -1 to relate it with Segment Dimension. The key -1 has "Unknown" as the caption. This SegmentID is my dimension key on the fact table and the key of Segment Dimension. The Segment Dimension has the Min and Max attributes where the minimum and maximum range of values for a particular dimension member is defined.

Is there a way for me to populate or assign values to the named calculation field SegmentID by getting the dimension key of segment dimension where the YTD Investment falls on segment dim's min and max attributes/range?

Thanks in advance,
May Lanie

|||

May,

This is can be tricky depending on the dimensionality of your cube and how you want the YTD to roll up. I have done this type of "dynamic" bucketing before using a separate measure group that only contains the row count for the fact table. You can then use the SCOPE function to allocate values along a pre-defined range of buckets according to the value you want to use for distribution purposes. Here is an example of what I am talking about:

Scope([Measures].[Customer Count by Bucket]);

([Bucket].[Bucket Key].[< 50K]) =

Filter(

Existing Customer.[Customer Name].[Customer Name].Members,

([Measures].[Sales Amt],[Bucket].[Bucket Key].[All Bucket]) <= 50000).Count;

([Bucket].[Bucket Key].[50K - 100K]) =

Filter(

Existing Customer.[Customer Name].[Customer Name].Members,

([Measures].[Sales Amt],[Bucket].[Bucket Key].[All Bucket]) > 50000 AND

([Measures].[Sales Amt],[Bucket].[Bucket Key].[All Bucket]) <= 100000).Count;

....

End Scope;

The "Measures.[Customer Count by Bucket]" is the fact table row count measure. There is a predefined "Bucket" dimension that has key values indicating a range, but you could just as easily use your "low" and "high" member values. The "Filter" statements are used to count the number of customers that fall into the buckets using "Sales Amt". You would need to substitute your "YTD Amount" calculation.

HTH,

Steve

Create view question

I want to create view to join several tables.
As I want to select the data by the date range , Can I pass the date
condition as parameter to it ?
Please tell me how to do .
I never use View before.
Thanks a lot"UDF's in SQL Server 2000 would give you the capability to do what you want
with a parameterized view... table valued UDF's accept parameters and can be
used anyway a table/view can be used..."
But you can use a parameter from your query to query your view (from your
client or from a procedure)
Select * from YourView
Where Yourconditionscolname = @.Somevar
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Agnes" <agnes@.dynamictech.com.hk> schrieb im Newsbeitrag
news:%23CjkJamTFHA.3188@.TK2MSFTNGP09.phx.gbl...
>I want to create view to join several tables.
> As I want to select the data by the date range , Can I pass the date
> condition as parameter to it ?
> Please tell me how to do .
> I never use View before.
> Thanks a lot
>|||Thank Jens. However, Select * from YourView
Your view is a view also, How Can I set the parameter during create YourView
'
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> glsD:OA62IimTF
HA.2128@.TK2MSFTNGP15.phx.gbl...
> "UDF's in SQL Server 2000 would give you the capability to do what you
> want
> with a parameterized view... table valued UDF's accept parameters and can
> be
> used anyway a table/view can be used..."
> But you can use a parameter from your query to query your view (from your
> client or from a procedure)
> Select * from YourView
> Where Yourconditionscolname = @.Somevar
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Agnes" <agnes@.dynamictech.com.hk> schrieb im Newsbeitrag
> news:%23CjkJamTFHA.3188@.TK2MSFTNGP09.phx.gbl...
>|||> Your view is a view also, How Can I set the parameter during create YourVi
ew
The short version: You can't short of scripting the entire Create View (and
Drop
View) statement each time you need it (which also means your users will have
to
be granted those rights). The better solution is to use a user-defined funct
ion
instead. This *will* allow you to pass a parameter and add forking condition
s.
HTH
Thomas
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:Ozm1xfsTFHA.2768@.tk2msftngp13.phx.gbl...
> Thank Jens. However, Select * from YourView
> Your view is a view also, How Can I set the parameter during create YourVi
ew
> '
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de>
> glsD:OA62IimTFHA.2128@.TK2MSFTNGP15.phx.gbl...
>|||Hi Agnes
You can do as:
CREATE VIEW <view_name>
AS
SELECT <Fiends>
FROM <Tables>
[WHERE <Conditions>]
to see the result, u can use
SELECT * FROM <view_name>
Please let me know if this answered the problem
thanks and regards
Chandra
"Agnes" wrote:

> I want to create view to join several tables.
> As I want to select the data by the date range , Can I pass the date
> condition as parameter to it ?
> Please tell me how to do .
> I never use View before.
> Thanks a lot
>
>|||No you cant do that while setting up the view the example was some client
code to query the view with conditions
Select * from YourView
Where Yourconditionscolname = [Placeyourvariablehere]
You view Would look like some "normal" select:
SELECT...
FROM
JOIN
ORDER
and so on.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Agnes" <agnes@.dynamictech.com.hk> schrieb im Newsbeitrag
news:Ozm1xfsTFHA.2768@.tk2msftngp13.phx.gbl...
> Thank Jens. However, Select * from YourView
> Your view is a view also, How Can I set the parameter during create
> YourView '
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de>
> glsD:OA62IimTFHA.2128@.TK2MSFTNGP15.phx.gbl...
>