HELP WITH RANKING !( please)

Hello Everybody:

Somebody know how I can create a nested ranking from a analysis services
cube, using MDX?.. i mean, sort a sales ranking that show .how % of my
clients....b.buy only 100 articles......how many clients ...expressed in %
, buy just 200 articles parent level using a measure and then sort
a child level using the same measure... something like:

I got it nex table :
Location client_id Sales article vendor_id
city 1 122230 100 01
City1 122231 200 05
City1 122232 500 02
City1 122233 100 04
City 2 122234 100 02
City2 122235 100 02
City2 122236 200 03
City3 122237 300 01
City3 122238 400 01
City3 122239 200 03

I want show this : 40% clients . Buy 100 articles
30% clients . Buy 200 articles
10% clients .Buy 300 articles
10% clients . Buy 400 articles
10% clients . Buy 500 articles

or this : the 70 % of clients buy between 100 and 200 aticles !!

i.m use sql server 2000 , analysis services 8.0 ,olap , and mdx

PLEASE HELP ME MADE THIS!!!!

write me too at lealy_lenn@.hotmail.com

--
hi all !

Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forum...eneral/200509/1Use this logic

declare @.t table(i int)
insert into @.t values(100)
insert into @.t values(200)
insert into @.t values(300)
insert into @.t values(100)
insert into @.t values(400)
insert into @.t values(100)
insert into @.t values(300)
insert into @.t values(600)
insert into @.t values(200)
insert into @.t values(600)
select i, s*100/Total as percentage from (
select i,count(i) as s ,(select count(i) from @.t) as total from @.t
group by i) T

Madhivanan|||Heathon via SQLMonster.com wrote:
> Hello Everybody:
> Somebody know how I can create a nested ranking from a analysis services
> cube, using MDX?.. i mean, sort a sales ranking that show .how % of my
> clients....b.buy only 100 articles......how many clients ...expressed in %
> , buy just 200 articles parent level using a measure and then sort
> a child level using the same measure... something like:
> I got it nex table :
> Location client_id Sales article vendor_id
> city 1 122230 100 01
> City1 122231 200 05
> City1 122232 500 02
> City1 122233 100 04
> City 2 122234 100 02
> City2 122235 100 02
> City2 122236 200 03
> City3 122237 300 01
> City3 122238 400 01
> City3 122239 200 03
>
> I want show this : 40% clients . Buy 100 articles
> 30% clients . Buy 200 articles
> 10% clients .Buy 300 articles
> 10% clients . Buy 400 articles
> 10% clients . Buy 500 articles
>
> or this : the 70 % of clients buy between 100 and 200 aticles !!
> i.m use sql server 2000 , analysis services 8.0 ,olap , and mdx
> PLEASE HELP ME MADE THIS!!!!
> write me too at lealy_lenn@.hotmail.com

You might get a better response in microsoft.public.sqlserver.olap

Simon

Help with Logical Fragmentation

I am having a look at the performance issues on a clients Website analysis
tool database which currently stands at 440 GB and as a result have not run
the optimisation or integrity checking parts of the maintenance plan for a
long time. When I run DBCC SHOWCONTIG (finland_Visits) WITH FAST,
TABLERESULTS, ALL_INDEXES, NO_INFOMSGS, one of the results returned has a
blank Indexname and also reports a 99.993% Logical Fragmentation. I presume
that this is what is hampering the query perfomance. I ran DBCC DBREINDEX
(finland_Visits, '', 0) which reduced the fragmentation of the 4 indexes on
the table but did not touch the first row. So what I want to know is A) What
this first row represents and check that it should be as low as possible and
B) what command will reduced it.
Thanks in Advance RussellThis is on a SQL Server 2000 SP3a server.
"Russell" wrote:
> I am having a look at the performance issues on a clients Website analysis
> tool database which currently stands at 440 GB and as a result have not run
> the optimisation or integrity checking parts of the maintenance plan for a
> long time. When I run DBCC SHOWCONTIG (finland_Visits) WITH FAST,
> TABLERESULTS, ALL_INDEXES, NO_INFOMSGS, one of the results returned has a
> blank Indexname and also reports a 99.993% Logical Fragmentation. I presume
> that this is what is hampering the query perfomance. I ran DBCC DBREINDEX
> (finland_Visits, '', 0) which reduced the fragmentation of the 4 indexes on
> the table but did not touch the first row. So what I want to know is A) What
> this first row represents and check that it should be as low as possible and
> B) what command will reduced it.
> Thanks in Advance Russell|||The largest table with issue is globalvb_Visits and when I run DBCC
SHOWCONTIG (globalvb_Visits) WITH FAST, TABLERESULTS, NO_INFOMSGS. The
output I get is listed below.
ObjectId
1382950102
IndexName
IndexId
0
Level
0
Pages
547631
Rows
27929035
MinimumRecordSize
149
MaximumRecordSize
149
AverageRecordSize
149
ForwardedRecords
0
Extents
68465
ExtentSwitches
68464
AverageFreeBytes
395.0390015
AveragePageDensity
95.11936188
ScanDensity
99.9839334
BestCount
68454
ActualCount
68465
LogicalFragmentation
99.99981689
ExtentFragmentation
27.22558975|||From the results ...it looks globalvb_Visits has no indexes?
The scandensity looks fine.
That should be between the 75 and 100%.
"Russell" wrote:
> The largest table with issue is globalvb_Visits and when I run DBCC
> SHOWCONTIG (globalvb_Visits) WITH FAST, TABLERESULTS, NO_INFOMSGS. The
> output I get is listed below.
> ObjectId
> 1382950102
> IndexName
> IndexId
> 0
> Level
> 0
> Pages
> 547631
> Rows
> 27929035
> MinimumRecordSize
> 149
> MaximumRecordSize
> 149
> AverageRecordSize
> 149
> ForwardedRecords
> 0
> Extents
> 68465
> ExtentSwitches
> 68464
> AverageFreeBytes
> 395.0390015
> AveragePageDensity
> 95.11936188
> ScanDensity
> 99.9839334
> BestCount
> 68454
> ActualCount
> 68465
> LogicalFragmentation
> 99.99981689
> ExtentFragmentation
> 27.22558975
>|||I would also check out your OS disk file fragmentation. If default settings
for size and growth were used for the initial database, you will have
several hundred thousand 1MB fragments of your data file all over the disk.
I had a client with that problem and a simple defrag resulted in an 18%
throughput improvement!!
--
TheSQLGuru
President
Indicium Resources, Inc.
"Russell" <Russell@.discussions.microsoft.com> wrote in message
news:697EE670-697D-4484-A105-9B1D3A9CDCEB@.microsoft.com...
>I am having a look at the performance issues on a clients Website analysis
> tool database which currently stands at 440 GB and as a result have not
> run
> the optimisation or integrity checking parts of the maintenance plan for a
> long time. When I run DBCC SHOWCONTIG (finland_Visits) WITH FAST,
> TABLERESULTS, ALL_INDEXES, NO_INFOMSGS, one of the results returned has a
> blank Indexname and also reports a 99.993% Logical Fragmentation. I
> presume
> that this is what is hampering the query perfomance. I ran DBCC DBREINDEX
> (finland_Visits, '', 0) which reduced the fragmentation of the 4 indexes
> on
> the table but did not touch the first row. So what I want to know is A)
> What
> this first row represents and check that it should be as low as possible
> and
> B) what command will reduced it.
> Thanks in Advance Russell|||I will have a look at the disk fragmentation.
There are 4 indexes on the table and running DBCC DBRENINDEX against the
table reduces their logical fragmentation to 0%.
"TheSQLGuru" wrote:
> I would also check out your OS disk file fragmentation. If default settings
> for size and growth were used for the initial database, you will have
> several hundred thousand 1MB fragments of your data file all over the disk.
> I had a client with that problem and a simple defrag resulted in an 18%
> throughput improvement!!
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Russell" <Russell@.discussions.microsoft.com> wrote in message
> news:697EE670-697D-4484-A105-9B1D3A9CDCEB@.microsoft.com...
> >I am having a look at the performance issues on a clients Website analysis
> > tool database which currently stands at 440 GB and as a result have not
> > run
> > the optimisation or integrity checking parts of the maintenance plan for a
> > long time. When I run DBCC SHOWCONTIG (finland_Visits) WITH FAST,
> > TABLERESULTS, ALL_INDEXES, NO_INFOMSGS, one of the results returned has a
> > blank Indexname and also reports a 99.993% Logical Fragmentation. I
> > presume
> > that this is what is hampering the query perfomance. I ran DBCC DBREINDEX
> > (finland_Visits, '', 0) which reduced the fragmentation of the 4 indexes
> > on
> > the table but did not touch the first row. So what I want to know is A)
> > What
> > this first row represents and check that it should be as low as possible
> > and
> > B) what command will reduced it.
> >
> > Thanks in Advance Russell
>
>|||Hi Russell
IndexID 0 indicates this table is a heap, which by definition has no
organization to it.
The purpose of DBCC INDEXDEFRAG is to make the physical order of pages in an
INDEX match the logical order, but since your table has no logical order,
DBCC INDEXDEFRAG will have no effect.
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Russell" <Russell@.discussions.microsoft.com> wrote in message
news:9E83AC0A-1706-46A9-A525-DCD1B8E9B392@.microsoft.com...
> The largest table with issue is globalvb_Visits and when I run DBCC
> SHOWCONTIG (globalvb_Visits) WITH FAST, TABLERESULTS, NO_INFOMSGS. The
> output I get is listed below.
> ObjectId
> 1382950102
> IndexName
> IndexId
> 0
> Level
> 0
> Pages
> 547631
> Rows
> 27929035
> MinimumRecordSize
> 149
> MaximumRecordSize
> 149
> AverageRecordSize
> 149
> ForwardedRecords
> 0
> Extents
> 68465
> ExtentSwitches
> 68464
> AverageFreeBytes
> 395.0390015
> AveragePageDensity
> 95.11936188
> ScanDensity
> 99.9839334
> BestCount
> 68454
> ActualCount
> 68465
> LogicalFragmentation
> 99.99981689
> ExtentFragmentation
> 27.22558975
>