Dimensions within Excel Pivot Table

Hi

I have a cube that has dimensions such as year, company, customer, statustext, employee etc.

In the Browse in Analysis Manager - all the dimensions look fine.

When I access the same cube from Excel after dragging and dropping the dimensions during analysis the dimensions in the Page section are not what is show when dragged to the row section.

For example - i have a display Customer as rows, years as columns. I drag the statustext next to customer and shows customer. The filter in the page section for statustext is correct. I have tried moving the dimension back to the Field List and re-adding, refreshing from the cube makes no difference. The only solution I have been able to come up with is rebuild the Pivot table - not what I want users to have to do.

Unfortunately, at teh moment we are limited to Excel for presentation.

Anyone ever seen this or have any suggestions?

Just remembered, I have experienced "Catastrophic Failure" - in Excel not sure if this is related and is server generated or local to Excel.

Steve

What version of SQL Server and what version of Excel are you using?

Also I did not quite understand the problem you are experiencing. Could you be more specific?

|||

Hi

SQL Server 2000 - Analysis Server 2000 SP3

I have a Pivot table from a cube, this pivot table has multiple dimensions.

For example - Account Manager, Status, Customer, Employee

If I display Sales by Customer (row) by Year (col) - that works.

If I then drag Status to the row - either to replace or in addition to customer - the display is actually Account Manager. The dimension Status is no longer in the Page Header but Account Manager is.

However, if I filter in the Page Header for a specific Status this shows correctly.

The only way I can fix it is to go into the Wizard - choose options - remove all dimensions, re-add them.

Thanks

|||

One possibility is that if your Excel file got corrupted (some mixup with dimensions), this would explain current behavior.

Can you re-produce the problem from scratch (blank spreadsheet)? If you can, how long does it take?

|||

It does happen from scratch but I cannot reproduce it intentionally. I did wonder if it may be because the structure of teh cube within Analysis Server changed. I hope this is not the case.

|||

I have seen a thread where somebody said he regularly experiences problems with pivot becoming corrupt when a datasource changes.

He said it's really common in Excel XP, but does not happen as often with Excel 2003. Since I don't know the Excel version you use, one possibility is to go with Excel 2003.

Also another solution is to always build pivots from scratch. You can even automate this by writing some VBA logic. Hope this helps!

dimension records exceeds 64000

I am facing a problem in SSAS, unlike Analysis service 2000 , I cannot find any grouping under level properties for a dimension. Actually one of the dimension is too big, some 1600000 records are there and no hierarchy as such. only way is to club by first alphabet,which we were using in 2000. i.e. by using the grouping property. but in SSAS 2005 I cannot find any, how do i overcome this, otherwise beyond 64000 records its not showing

There has been a change in terminology in AS2005. Instead of grouping this functionality is refered as Discretization.

You need to create a new attribute based on the same column as your main attribute.

Set the DiscretizationMethod to Automatic. ( you can try any other method find if you like it better).

Also take a look at the NamingTemplate property of the attribute. You should be able to use it to get Analysis Server to generate custom names for your grouping (discretized) attribute.

Here is BOL topic on the matter: http://msdn2.microsoft.com/en-us/library/ms174810.aspx

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

Dimension in Analysis Services 2005

Hello,
How must I make if I want concat two fields of a database in a level of a
dimension in Analysis Services 2005?
Sample : id_Collaborator(10) and Name(Gates) give 10_Gates
Thanks for your help!!
Nico
I generally create a view on the table which concatinates the fields, so
there is nothing to do in Analysis Services... HOwever you may go to
properties at the bottom left of the dimension editor and use a SQL
Expression to concatinate the fields there as well.. Take a look at some of
the data fields in the time dimension, and you should see an example..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"pralnico" <pralnico@.discussions.microsoft.com> wrote in message
news:79D4B243-2294-47DB-82F1-9B7833BDB867@.microsoft.com...
> Hello,
> How must I make if I want concat two fields of a database in a level of a
> dimension in Analysis Services 2005?
> Sample : id_Collaborator(10) and Name(Gates) give 10_Gates
> Thanks for your help!!
> Nico
>
>
|||You might get more specialized help if you post in the SQL Server 2005
newsgroups.
http://www.aspfaq.com/sql2005/show.asp?id=1
http://www.aspfaq.com/
(Reverse address to reply.)
"pralnico" <pralnico@.discussions.microsoft.com> wrote in message
news:79D4B243-2294-47DB-82F1-9B7833BDB867@.microsoft.com...
> Hello,
> How must I make if I want concat two fields of a database in a level of a
> dimension in Analysis Services 2005?
> Sample : id_Collaborator(10) and Name(Gates) give 10_Gates
> Thanks for your help!!
> Nico
>
>

Dimension in Analysis Services 2005

Hello,
How must I make if I want concat two fields of a database in a level of a
dimension in Analysis Services 2005'
Sample : id_Collaborator(10) and Name(Gates) give 10_Gates
Thanks for your help!!
NicoI generally create a view on the table which concatinates the fields, so
there is nothing to do in Analysis Services... HOwever you may go to
properties at the bottom left of the dimension editor and use a SQL
Expression to concatinate the fields there as well.. Take a look at some of
the data fields in the time dimension, and you should see an example..
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"pralnico" <pralnico@.discussions.microsoft.com> wrote in message
news:79D4B243-2294-47DB-82F1-9B7833BDB867@.microsoft.com...
> Hello,
> How must I make if I want concat two fields of a database in a level of a
> dimension in Analysis Services 2005'
> Sample : id_Collaborator(10) and Name(Gates) give 10_Gates
> Thanks for your help!!
> Nico
>
>|||You might get more specialized help if you post in the SQL Server 2005
newsgroups.
http://www.aspfaq.com/sql2005/show.asp?id=1
--
http://www.aspfaq.com/
(Reverse address to reply.)
"pralnico" <pralnico@.discussions.microsoft.com> wrote in message
news:79D4B243-2294-47DB-82F1-9B7833BDB867@.microsoft.com...
> Hello,
> How must I make if I want concat two fields of a database in a level of a
> dimension in Analysis Services 2005'
> Sample : id_Collaborator(10) and Name(Gates) give 10_Gates
> Thanks for your help!!
> Nico
>
>

Dimension in Analysis Services 2005

Hello,
How must I make if I want concat two fields of a database in a level of a
dimension in Analysis Services 2005'
Sample : id_Collaborator(10) and Name(Gates) give 10_Gates
Thanks for your help!!
NicoI generally create a view on the table which concatinates the fields, so
there is nothing to do in Analysis Services... HOwever you may go to
properties at the bottom left of the dimension editor and use a SQL
Expression to concatinate the fields there as well.. Take a look at some of
the data fields in the time dimension, and you should see an example..
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"pralnico" <pralnico@.discussions.microsoft.com> wrote in message
news:79D4B243-2294-47DB-82F1-9B7833BDB867@.microsoft.com...
> Hello,
> How must I make if I want concat two fields of a database in a level of a
> dimension in Analysis Services 2005'
> Sample : id_Collaborator(10) and Name(Gates) give 10_Gates
> Thanks for your help!!
> Nico
>
>|||You might get more specialized help if you post in the SQL Server 2005
newsgroups.
http://www.aspfaq.com/sql2005/show.asp?id=1
http://www.aspfaq.com/
(Reverse address to reply.)
"pralnico" <pralnico@.discussions.microsoft.com> wrote in message
news:79D4B243-2294-47DB-82F1-9B7833BDB867@.microsoft.com...
> Hello,
> How must I make if I want concat two fields of a database in a level of a
> dimension in Analysis Services 2005'
> Sample : id_Collaborator(10) and Name(Gates) give 10_Gates
> Thanks for your help!!
> Nico
>
>

Dimension Hierarchy members processed incorrectly

I have an interesting problem with Analysis Services that I was able to resolve (sort of), but I would appreciate some feedback to see if this is known behavior, a bug, or if I'm just setting up my dimension incorrectly.

Basically, the intermediate members of a hierarchy are showing up incorrectly in my date dimension. For example, when I browse the dimension, I'll end up with a tree that looks like this:

All CY
- CY 1998
- Qtr 1 CY 1998
- January 1998
- Jan 1, 1998
- Jan 2, 1998
- etc.
- .....
- .....
- CY 2005
- Qtr 1 CY 1998
- January 1998
- Jan 1, 2005
- Jan 2, 2005
- etc.

As you can see, the years are correct and the lowest element (date) is correct, but the names of the intermediate elements (quarter and month) are incorrect. These intermediate elements are the same for each calendar year (i.e. it's always 1998, which is the earliest year we have data for in the table).

I've included the table/hierarchy structure below for reference.

So the way that I (sort of) resolved the issue was by using the calculated name values as the keys for the attributes instead of using the integer-based keys that I was using. It seems that you can't/shouldn't have key values that repeat for different higher-level elements (i.e. the month_si value of "12" (December) is present in the table for rows from CY 1998, 1999, 2000, etc.). I'm guessing here, but it seems that when it went to build the hierarchy underneath, say, CY 2006, it found key values of "1","2","3", & "4" for [quarter_si] (correctly so). It then pulled the value from [Column15] for those key values, but instead of limiting itself to data underneath CY 2006, it looked at the whole table, and thus pulled the name values for those quarters from the first records in the table, which happened to be "Qtr 1 CY 1998","Qtr 2 CY 1998" and so on. But since "Qtr 1 CY 2006" is only present in CY 2006, using "Qtr 1 CY 2006" as the key and name value gave me the correct hierarchy.

Unfortunately, this resolution doesn't work because the keys won't order correctly (i.e. "June 2006" comes before "May 2006" under "Qtr 2 CY 2006") and this is a show-stopper.

What's odd is that I converted this dimension directly from SSAS 2000, and this was not a problem we encountered in 2000. Is this issue resulting from something new introduced in SSAS 2005? Is there a property flag that controls this behavior?

Or do I just not have a good understanding of how dimensions work? ;)

I would appreciate anyone's feedback or comments.

Thanks in advance,
Jamie C.

TABLE/DIMENSION/HIERARCHY INFO:

The underlying table used for the dimension is "dbo.dci_Date" - the structure of the table (including calculated columns within AS) is this:

[dbo].[dci_Date]
[date_id_si] [smallint] NOT NULL,
[year_si] [smallint] NOT NULL,
[quarter_si] [smallint] NOT NULL,
[month_si] [smallint] NOT NULL,
[date_sd] [smalldatetime] NULL,

The following "virtual" columns are also present as calculated columns - I've included the formulas for reference:

[Column4] [WChar] = left("date_sd",11)
[Column13] [WChar] = 'CY ' + convert(char,DatePart(year,"date_sd"))
[Column15] [WChar] = 'Qtr ' + convert(CHAR, DatePart(quarter,"date_sd")) + 'CY ' + convert(char,DatePart(year,"date_sd"))
[Column17] [WChar] = convert(CHAR, DateName(month,"date_sd")) + convert(char,DatePart(year,"date_sd"))

Here's an example of what a couple of rows from the table look like (including the values for the calculated columns) - I've included the min & max as well:

[date_id_si],[year_si],[quarter_si],[month_si],[date_sd],[Column4],[Column13],[Column15],[Column17]
-729,1998,1,1,1998-01-01 00:00:00Z,Jan 1 1998,CY 1998,Qtr 1 CY 1998,January 1998 ** MIN
2495,2006,4,10,2006-10-30 00:00:00Z,Oct 30 2006,CY 2006,Qtr 4 CY 2006,October 2006
2496,2006,4,10,2006-10-31 00:00:00Z,Oct 31 2006,CY 2006,Qtr 4 CY 2006,October 2006
2497,2006,4,11,2006-11-01 00:00:00Z,Nov 1 2006,CY 2006,Qtr 4 CY 2006,November 2006
4270,2011,3,9,2011-09-09 00:00:00Z,Sep 9 2011,CY 2011,Qtr 3 CY 2011,September 2011 ** MAX

The hierarchy for this dimension (Calendar Year) is built with the following attributes:

Year -- key: [year_si] name: [Column13]
Quarter -- key: [quarter_si] name: [Column15]
Month -- key: [month_si] name: [Column17]
Day -- key: [Column4] name: [Column4]

Hello. You must assure that each attribute in a dimensions has an unique key. Like month, if you represent this with a month number like 1 to 12, this is not unique over years. To solve this problem you simply go to the key property of the attribute and change that to a collection by adding each year to column. By clicking to button to the right for the key column you will see the "Data Item Collection editor".

I would recommed to use numbers or collections of numbers as keys for year, quarters and months. Change the name column to something for informative instead, like a text based description. Check that the attribute is ordered by key, not by name in the properties for each attribute.

HTH

Thomas Ivarsson

|||

Thanks Thomas! I had a feeling that was the case, but it never hurts to be sure.

Best,

Jamie C.

Dimension Design Help: Mix of Parent-Child and List

Hello Analysis Server Users!

I wonder if someone can help me with the setup a dimension that is a parent-child hierarchy first, but the leaves are not joined to the fact table, but instead to another table containing a list of elements for this leaf, and from there fianlly to the facts. Hmm... bad wording and description, sorry.

Maybe an example helps: say I got a list of 5 elements a-e that are in a simple list dimension and act as hosts for fact data. Just simple straight list a, b, c, d and e. This list is often used and must be kept, it is stored in a simple straight table in the database.

I would now like a group in addition to that, as an alternative, say I want to see grouped together b, c and e.

I could do that by adding an attribute "my group" - but that would automatically result in another group "not my group", containing a and d - It would look like this:

(all)

-mygroup

--b

--c

--e

-other group

--a

--d

In my world this group "other" and the data aggregated on it is nonsense, the business does not use the data in that combination.

So what I am looking for is something that looks like this:

(all)

-a

-d

-mygroup

--b

--c

--e

I know how to build that, I did do parent child dimensions before, but the special twist here is that I cannot add the mygroup name to the list of a - e, since than it appears as a new item in the list. Remember, I early said I want to keep the straight list a - e and only want the hierarchy in addition to it.

So, the hierarchy elements "mygroup" needs to be put into another table, and that is when I am stuck... Can I have a self refercing table as top level, then another table below it with the elements, and that finally leads to the 3rd table with the fact data?

Hello,

I think, you can use the parent-child hierarchy as you have build it, and for getting the straight list, you can reference it in MDX with Descendants([All], <last level number>, LEAVES), so for example if the last level is 3 and the top Level is [All]: Descendants ([All], 3, LEAVES), for getting a list a ... e

Hans

|||

I think I understand what you are trying to say, but I admit I cannot see how you "add" the MDX to the dimension.

Do you mean to build one or two dimensions? One, I guess, parent child type.

Where can I put the MDX to make the dimension get the two "apperances", to get the straight list "minus" (filtered out) those elements that form the hierarchy in the other view? Is that a property or?

|||

Hi,

No, I think you make one parent-child dimension with all the grouping you need. Than you make a calculated member under "Calculations" in visual studio with that MDX. Because the "list-view" is only "another view" with that dimension for retrieving data in that way. I think you didn't build this in a dimension, because it's only 2 different ways of viewing at the data. It's the same as in the relational world: you have one table and a lot of different querys fullfilling your nees. Here you have your dimensions and fact-tables an a lot of MDX queries (perhaps encapsulated as calculated members) which bring the data as you need it.

Hans

|||

I am not fully aware of what you would like to do here but visually it looks to me as a problem that dimension writeback can solve.

With dimension writeback you can create members in a parent-child dimension that do not exist in the source table. You can also move existing members in a dimension tree.

I think that you will need the SSAS2005 Enterprise edition for this feature.

Regards

Thomas Ivarsson

Difficulty in creating Time Dimension - SSAS

Hello all,

I am familiar with Analysis Services 2000 in which creating a time dimension is very easy.

In SSAS (2005) , i have a table in which there is field for DateTime which i want to use as time dimesion.

In time dimension wizard , it asks for the column for year , month etc (which is created by default in AS 2000) .

Since i have only one column having datetime datatype, how should i proceed

by creating heirarchy (year,month ,day - same as AS 2000).

Thanks,

deepti

i

You can create a stored procedure that populate a table with "time elements". See this example where I created a procedure that add rows into a table named CALSTD:

create procedure AddCalendar @.nYear int as

if not exists(select * from sysobjects where name='calstd')

CREATE TABLE [dbo].[CalStd](

[Data] [datetime],

[Data_lunii] [datetime],

[An] [smallint],

[Luna] [smallint],

[LunaAlfa] [char](15) ,

[Zi] [smallint],

[Saptamana] [smallint],

[Trimestru] [smallint],

[Zi_alfa] [char](10),

[Camp1] [char](10),

[Camp2] [char](10),

[Camp3] [char](10),

[Fel_zi] [char](1)

) ON [PRIMARY]

DECLARE @.I INT,@.DDATA DATETIME,@.DDATAL DATETIME,@.DDATAST DATETIME,@.NAN INT,@.nZi int,@.cZiChar char(10),@.cLunaAlfa char(15),@.nZileAn int

DELETE FROM CALSTD WHERE YEAR(DATA)=@.NAN

set @.nan=@.nyear

SET @.I=0

SET @.DDATAST=CONVERT(DATETIME,'01/01/'+RTRIM(STR(@.NAN)))

if (@.NAN%4)=0 and (@.nAn%400)<>0

SET @.nZileAn=366

else

set @.nZileAn=365

WHILE @.I<@.nZileAn

BEGIN

SET @.DDATA=DATEADD(DAY,@.i,@.DDATAST)

SET @.DDATAL=dateadd(day, -day(dateadd(month, 1, @.dData)), dateadd(month, 1, @.dData))

Set @.nZi=datepart(weekday,@.dData)

set @.cZiChar=(case when @.nZi=1 then 'Sunday'

when @.nZi=2 then 'Monday'

when @.nZi=3 then 'Tuesday'

when @.nZi=4 then 'Wednesday'

when @.nZi=5 then 'Thursday'

when @.nZi=6 then 'Friday'

else 'Saturday' end)

set @.cLunaAlfa=(case when datepart(month,@.dData)=1 then 'January'

when datepart(month,@.dData)=2 then 'February'

when datepart(month,@.dData)=3 then 'March'

when datepart(month,@.dData)=4 then 'April'

when datepart(month,@.dData)=5 then 'May'

when datepart(month,@.dData)=6 then 'June'

when datepart(month,@.dData)=7 then 'July'

when datepart(month,@.dData)=8 then 'August'

when datepart(month,@.dData)=9 then 'September'

when datepart(month,@.dData)=10 then 'Octomber'

when datepart(month,@.dData)=11 then 'November'

else 'December'

end)

INSERT INTO CALSTD(Data,Data_lunii,An,Luna,LunaAlfa,Zi,Saptamana,Trimestru,Zi_alfa,Camp1,Camp2,Camp3,Fel_zi)

VALUES(@.dData,@.dDatal,datepart(year,@.dData),datepart(month,@.dData),@.cLunaAlfa,datepart(day,@.dData),datepart(week,@.dData),datepart(quarter,@.dData),@.cZiChar,'','','','L')

SET @.I=@.I+1

END

|||

Thanks crysty that was really helpful.

regards,

deepti.

|||

Hi Deepti, I was in the same situation, but I found a simple way to achieve the Time Dimension Generation, please visit the following url, to know something about it. http://www.ssw.com.au/ssw/Standards/Rules/CreatingATimeDimensionIn10EasySteps.aspx

So for more information about sqlserver 2005, i'm building a blog with some resources at: http://grupohix.blogspot.com

Regards

FraGar

Difficulty in creating Time Dimension - SSAS

Hello all,

I am familiar with Analysis Services 2000 in which creating a time dimension is very easy.

In SSAS (2005) , i have a table in which there is field for DateTime which i want to use as time dimesion.

In time dimension wizard , it asks for the column for year , month etc (which is created by default in AS 2000) .

Since i have only one column having datetime datatype, how should i proceed

by creating heirarchy (year,month ,day - same as AS 2000).

Thanks,

deepti

i

You can create a stored procedure that populate a table with "time elements". See this example where I created a procedure that add rows into a table named CALSTD:

create procedure AddCalendar @.nYear int as

if not exists(select * from sysobjects where name='calstd')

CREATE TABLE [dbo].[CalStd](

[Data] [datetime],

[Data_lunii] [datetime],

[An] [smallint],

[Luna] [smallint],

[LunaAlfa] [char](15) ,

[Zi] [smallint],

[Saptamana] [smallint],

[Trimestru] [smallint],

[Zi_alfa] [char](10),

[Camp1] [char](10),

[Camp2] [char](10),

[Camp3] [char](10),

[Fel_zi] [char](1)

) ON [PRIMARY]

DECLARE @.I INT,@.DDATA DATETIME,@.DDATAL DATETIME,@.DDATAST DATETIME,@.NAN INT,@.nZi int,@.cZiChar char(10),@.cLunaAlfa char(15),@.nZileAn int

DELETE FROM CALSTD WHERE YEAR(DATA)=@.NAN

set @.nan=@.nyear

SET @.I=0

SET @.DDATAST=CONVERT(DATETIME,'01/01/'+RTRIM(STR(@.NAN)))

if (@.NAN%4)=0 and (@.nAn%400)<>0

SET @.nZileAn=366

else

set @.nZileAn=365

WHILE @.I<@.nZileAn

BEGIN

SET @.DDATA=DATEADD(DAY,@.i,@.DDATAST)

SET @.DDATAL=dateadd(day, -day(dateadd(month, 1, @.dData)), dateadd(month, 1, @.dData))

Set @.nZi=datepart(weekday,@.dData)

set @.cZiChar=(case when @.nZi=1 then 'Sunday'

when @.nZi=2 then 'Monday'

when @.nZi=3 then 'Tuesday'

when @.nZi=4 then 'Wednesday'

when @.nZi=5 then 'Thursday'

when @.nZi=6 then 'Friday'

else 'Saturday' end)

set @.cLunaAlfa=(case when datepart(month,@.dData)=1 then 'January'

when datepart(month,@.dData)=2 then 'February'

when datepart(month,@.dData)=3 then 'March'

when datepart(month,@.dData)=4 then 'April'

when datepart(month,@.dData)=5 then 'May'

when datepart(month,@.dData)=6 then 'June'

when datepart(month,@.dData)=7 then 'July'

when datepart(month,@.dData)=8 then 'August'

when datepart(month,@.dData)=9 then 'September'

when datepart(month,@.dData)=10 then 'Octomber'

when datepart(month,@.dData)=11 then 'November'

else 'December'

end)

INSERT INTO CALSTD(Data,Data_lunii,An,Luna,LunaAlfa,Zi,Saptamana,Trimestru,Zi_alfa,Camp1,Camp2,Camp3,Fel_zi)

VALUES(@.dData,@.dDatal,datepart(year,@.dData),datepart(month,@.dData),@.cLunaAlfa,datepart(day,@.dData),datepart(week,@.dData),datepart(quarter,@.dData),@.cZiChar,'','','','L')

SET @.I=@.I+1

END

|||

Thanks crysty that was really helpful.

regards,

deepti.

|||

Hi Deepti, I was in the same situation, but I found a simple way to achieve the Time Dimension Generation, please visit the following url, to know something about it. http://www.ssw.com.au/ssw/Standards/Rules/CreatingATimeDimensionIn10EasySteps.aspx

So for more information about sqlserver 2005, i'm building a blog with some resources at: http://grupohix.blogspot.com

Regards

FraGar

different values between Relational and MOLAP in a sum measure... BUG?

Hello,
I'm working in a Windows 2003 Server, SQL Server 2000 and Analysis Services 2000.
I have created two models in Analysis Services (MOLAP):
- MODEL 1 gets info directly from the relational FACT table: "FCT_SALES"
- MODEL 2 gets info from a view with: "SELECT * FROM FCT_SALES"
FCT_SALES has a measure (sum and double - the column in the relational table is float).
This measure has 10 rows in the FCT_SALES.
Problem:
When we agregate the measure rows in Query Analyzer, MODEL 1 and MODEL 2 return 20. --> IT'S OK!
When I use Analysis Services (and ProClarity), MODEL 1 returns 20. --> IT'S OK!
When I use Analysis Services (and ProClarity), MODEL 2 returns 17. -->WRONG!!
WHY?
When I make Drill Through (Drill to Detail) in Analysis Services (or ProClarity) to see the rows that compose the value, and export them to Excel, I get the 20. --> IT'S OK!
So, WHY does Analysis Services (and ProClarity) give me 17 ?!?
If the SUM(rows) give me 20, WHY does Analysis Services give me 17 ?!?
Thanks.
Hi guys,
Yesterday nigth, I found the problem: something that shouldn't happen in the source info, happened! Murphy's Law )))
Analysis Services wasn't causing the info inconsistent.
Thanks anyway.

different values between Relational and MOLAP in a sum measure... BUG?

Hello,
I'm working in a Windows 2003 Server, SQL Server 2000 and Analysis Services
2000.
I have created two models in Analysis Services (MOLAP):
- MODEL 1 gets info directly from the relational FACT table: "FCT_SALES"
- MODEL 2 gets info from a view with: "SELECT * FROM FCT_SALES"
FCT_SALES has a measure (sum and double - the column in the relational table
is float).
This measure has 10 rows in the FCT_SALES.
Problem:
When we agregate the measure rows in Query Analyzer, MODEL 1 and MODEL 2 ret
urn 20. --> IT'S OK!
When I use Analysis Services (and ProClarity), MODEL 1 returns 20. --> IT'S
OK!
When I use Analysis Services (and ProClarity), MODEL 2 returns 17. -->WRONG!
!
WHY?
When I make Drill Through (Drill to Detail) in Analysis Services (or ProClar
ity) to see the rows that compose the value, and export them to Excel, I get
the 20. --> IT'S OK!
So, WHY does Analysis Services (and ProClarity) give me 17 ?!?
If the SUM(rows) give me 20, WHY does Analysis Services give me 17 ?!?
Thanks.Hi guys,
Yesterday nigth, I found the problem: something that shouldn't happen in the
source info, happened! Murphy's Law )))
Analysis Services wasn't causing the info inconsistent.
Thanks anyway.

Different indexes - performance analysis question

Hello

I'm doing some performance analysis for my application. I'm doing 7600 SQL queries based on the following SQL query:

SELECT LocationId, ProductId, BatchId, SUM(Quantity) AS Quantity FROM Logistics WHERE UserId = [number] AND ProductId IN ([productidlist]) GROUP BY LocationId, ProductId, BatchId;

Data in table Logistics has LocationId = 1 and BatchId = 0 for absolute all rows in this test, UserId and ProductId may be different. For each SQL Query it's doing, it's also inserting new rows in the table. The table starts with 0 rows for the first SQL query above, ends with 35 000 rows. Execution for both Editions below is exactly the same (same data inserts)

The graph below is showing the time used in milliseconds (y axis) for each query (x axis) - both editions is doing the exactly the same query but with different indexes.

URL to graph: http://www.lostfields.com/sqlindexing.gif

Edition 2 has the following priority on the PK: UserId, LocationId, ProductId, BatchId
Edition 2 Optimized has the following priority on the PK: UserId, ProductId, BatchId, LocationId.

How come Edition 2 have to scan over a lot more indexes than Edition 2 Optimized? As I can see it this shouldn't have happened since LocationId = 1 all the time. Or am I missing something?

[edit] <img> tag didn't work so I have to just paste the url

What happens if you put locationId = 1 in your query. Its all about selectivity.

What other columns are on the table. I would also look at what happens if you include userid in the group by.

Can you capture the two query plans.

|||

Yes, thank you

It did help a lot to include LocationId = 1 in the query, when I did this in Edition 2 it became just similar to Edition 2 Optimized. I couldn't see any performance increase by including UserId in the GROUP BY.

The other fields/columns are just a TransactionDate (date for the insert) and TransactionId (identity to make it unqiue for stopping a duplicate insert).

The two Execution Plans can be found at http://www.lostfields.com/sqlindexing_plan.gif

I'm not sure why Edition 2 Optimized has a sort method there though, as Edition 2 doesn't, but have a filter (since LocationId isn't in where clause I guess).