Having subtotal after specific number of rows/columns

Hi
I'm working on a Matrix report in which i have a column grouping. I
have added a subtotal for the column group. so this total comes as he
last column i.e. after all the values in that column grouping. I want
to have a subtotal after lets say 10 columns and then again a total at
the end.
Can somebody tell me how to have a subtotal in the middle of a group as
well as in the end of the group?
Ankur MehtaAnkur,
The only way I know how to do this is to add another group which breaks down
your columns into sets of X number, depending on what other factor you use to
group on. Example using the Adventure Works sample database: Group on the
main Products, then do a group on Accessories under the products. Then you
can have a group2 footer which subtotals on the second group and a group1
footer which subtotals on the entire Products line.
Sorry I don't know of another way.
Catadmin
--
MCDBA, MCSA
Random Thoughts: If a person is Microsoft Certified, does that mean that
Microsoft pays the bills for the funny white jackets that tie in the back?
@.=)
"Ankur Mehta" wrote:
> Hi
> I'm working on a Matrix report in which i have a column grouping. I
> have added a subtotal for the column group. so this total comes as he
> last column i.e. after all the values in that column grouping. I want
> to have a subtotal after lets say 10 columns and then again a total at
> the end.
> Can somebody tell me how to have a subtotal in the middle of a group as
> well as in the end of the group?
> Ankur Mehta
>

Having problems with distinct and count

Hi,
Here is the query I am trying to achieve and having syntax issues
Select count(distinct name, number) from results.
To replicate the situation use the following SQL
create table results (name varchar(100), number int)
insert into results values ('test1', 1)
insert into results values ('test1', 1)
insert into results values ('test1', 1)
insert into results values ('test2', 2)
insert into results values ('test2', 2)
insert into results values ('test2', 2)
Basically the return of the query should be 2. I can achieve this by
doing following query
select count(*) from
(select distinct [name], [number] from results) a
but I want to do it one query as the later query is a big hit on the
performance.
On a large sample of data the second query takes around 2 seconds.
Any help would be appreciated.
Thanks
SAIan index on (name, number) might speed it up

Having fun with moint points

To keep the number of driveletters to a minimum, have
the same directory structure on the instances and do
something with disks and performance I tried this:
MSSQL$I1\Data
\Data1 <- mount point
\Tlog
\Backup
\Backup1 <- mount point
MSSQL$I2\Data
\Data1 <- mount point
\Tlog
\Backup
\Backup1
MSSQL$I3\Data
\Data1
\Tlog
\Backup
\Backup1
And ran into the SQL Setup does not support mount points
bug or feature. So I added driveletter to the mounted disk.
I am still thinking off using the mount points next to the drive
letters. Are the any other issues or disadvantages I should be
aware off?
Hans
Hi
Have you looked at http://support.microsoft.com/kb/819546/en-us "SQL Server
2000 support for mounted volumes"?
On non-clustered systems, the base drive needs a drive letter.
Regards
Mike
"Hans de Bruin" wrote:

> To keep the number of driveletters to a minimum, have
> the same directory structure on the instances and do
> something with disks and performance I tried this:
> MSSQL$I1\Data
> \Data1 <- mount point
> \Tlog
> \Backup
> \Backup1 <- mount point
> MSSQL$I2\Data
> \Data1 <- mount point
> \Tlog
> \Backup
> \Backup1
> MSSQL$I3\Data
> \Data1
> \Tlog
> \Backup
> \Backup1
> And ran into the SQL Setup does not support mount points
> bug or feature. So I added driveletter to the mounted disk.
> I am still thinking off using the mount points next to the drive
> letters. Are the any other issues or disadvantages I should be
> aware off?
> --
> Hans
>

HAVING a problem

Hi NG,
I have a problem with a query. I have three columns with relations from
A to B and the number of eg. Orders:
C1 | C2 | Number
A B 17
A C 4
A E 23
B A 22
B G 19
B J 21
What I want is to get from each C1 element (A, B) the relation to the
C2 with the lowest number, in this example:
C1 | C2 | Number
A C 4
B G 19
How can I do this?
Thank you very much,
RudiSELECT c1, c2, number
FROM your_table AS T
WHERE number =
(SELECT MIN(number)
FROM your_table
WHERE c1 = T.c1) ;
David Portas
SQL Server MVP
--

hash in RS

Hi,

I need to take the Social Security number and hash it into a unique identifier because I can't display Social Security numbers on a report.

Does Reporting Services have any encrypting/hashing routines that I can use?

Any suggestions?

Thanks.

You could write some code to do this, however there is no built-in functionality.

If it needs to be secure I would has the data within SQL Server. If you are using SQL 2005 there are some functions to do this.

Here is something that may relate.

http://blogs.msdn.com/lcris/archive/2005/12/22/506931.aspx

cheers!

|||

Maybe you can use the vb code in the following link in your report code and call the function to get the encrypted value:

http://www.freevbcode.com/ShowCode.asp?ID=4398

Or you can write a custom code in any of the .net supported language and use Encryption class in the BCL and refer to this custom code (.dll) and call the function again to get the excrypted value back.

Or in a simple way, display just "###-##-####" for all records instead of displaying the encrypted/hashed value because there is always a possibility that the hacker could decrypt the value.

Shyam

Handling Data Integrity Issues in SQL2000

Hello,
I’m looking for some help with analysis services.
I ahve a very simple fact table which has 2 columns
Account Number and Investment Objective.
Fact table
Account Number Investment Objective
12345678 A
22222222 A
33333333 B
44444444 X
A dimension is needed for the investment objective
So I have a lookup table which is
Value Description
A Growth
B No-Growth
There is not an X value in the lookup table so when I look at the count for
accounts I only get 3. The account 44444444 never shows up.
Is there a way to have 44444444 or any other account that might get a value
not in the lookup table to fall into an ‘Unknown’ type description?
I understand the best way to solve is to make sure I have a value in the
lookup table for every value that is in the investment objective fact, the
problem is I cant control what might get added to it, and we want to be able
to have an unknown description and have everything that falls out of the
range of the lookup go into that. This will allow the users of the cube to
find the bad entries and fix them.
Of course this is sample data and the real tables have millions of records
and 100’s of columns, but I think the basic concept applies.
Any help or a direction to go in would be greatly appreciated. BTW this is
SQL2000
ThanksHello AppDev,
I am assuming you are using a Star schema in a SQL Server database
somewhere. The Star Schema is used as a basis for the OLAP cube you
are creating.
Remember Analysis Services requires all dimension records to be unique
and fact records to have a relationship with all dimensions. This means
Fact records that do not have a correlating dimension record will not
appear in your cube. (This only happens when you included dimensions
that do not have a relationship with all fact records)
There is a quick solution to this. Create an 'Unknown' record in
your dimension table. When populating the fact table you can return the
unknown surrogate key value to join to the unknown dimension record.
This will make unknown records appear in your cube.
Hope this helps
Myles Matheson
Data Warehouse Architect|||(microsoft.public.sqlserver.olap is a better newsgroup for a posting like
this, but here goes)
My first gut feel is that you should not be allowing this to occur. This is
basic RI between a fact table and the dimension. You should be processing
your dimension to pickup additions prior to processing the fact table. This
will ensure that this doesn't occur if RI is in-place on the RDBMS.
The row is disappearing from the fact table because the default SQL
statement is an inner join between the fact table and the dimension table.
Thus the row will not be returned to Analysis Services at all . . . we
simply don't see it. The RDBMS eliminates it before we get it.
In SQL2K, you best option is to create an UNKNOWN member in the base
dimension and then load your fact data through a view. In the view use a
CASE clause with an EXISTS and replace the FK being returned based on
whether or not that key exists. If it doesn't exist, then return the UNKNOWN
member.
In SQL2K5, the system supports an unknown member directly and you can load
data w/ an error configuration which tells it to assign invalid FKs with the
system generated unknown member directly.
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"appdevtech" <appdevtech@.online.nospam> wrote in message
news:44BBA514-66F1-4FDD-A0D2-11E6BB6D2F13@.microsoft.com...
> Hello,
> I'm looking for some help with analysis services.
> I ahve a very simple fact table which has 2 columns
> Account Number and Investment Objective.
> Fact table
> Account Number Investment Objective
> 12345678 A
> 22222222 A
> 33333333 B
> 44444444 X
> A dimension is needed for the investment objective
> So I have a lookup table which is
> Value Description
> A Growth
> B No-Growth
> There is not an X value in the lookup table so when I look at the count
> for
> accounts I only get 3. The account 44444444 never shows up.
> Is there a way to have 44444444 or any other account that might get a
> value
> not in the lookup table to fall into an 'Unknown' type description?
> I understand the best way to solve is to make sure I have a value in the
> lookup table for every value that is in the investment objective fact, the
> problem is I cant control what might get added to it, and we want to be
> able
> to have an unknown description and have everything that falls out of the
> range of the lookup go into that. This will allow the users of the cube
> to
> find the bad entries and fix them.
> Of course this is sample data and the real tables have millions of records
> and 100's of columns, but I think the basic concept applies.
> Any help or a direction to go in would be greatly appreciated. BTW this
> is
> SQL2000
> Thanks
>|||Thanks Dave,
My gut feeling is to fix the RI issues first also, but in this case I can
use the cube to identify the issues, versus catching the data during import
then writing a report to notify the users of the inconsistent data.
When you say "create an UNKNOWN member in the base dimension” do you mean
adding an additional record to my dimension table?
Value Description
A Growth
B No-Growth
X Unknown
Then in the view if it does not exist, return X(Unknown)?
"Dave Wickert [MSFT]" wrote:

> (microsoft.public.sqlserver.olap is a better newsgroup for a posting like
> this, but here goes)
> My first gut feel is that you should not be allowing this to occur. This i
s
> basic RI between a fact table and the dimension. You should be processing
> your dimension to pickup additions prior to processing the fact table. Thi
s
> will ensure that this doesn't occur if RI is in-place on the RDBMS.
> The row is disappearing from the fact table because the default SQL
> statement is an inner join between the fact table and the dimension table.
> Thus the row will not be returned to Analysis Services at all . . . we
> simply don't see it. The RDBMS eliminates it before we get it.
> In SQL2K, you best option is to create an UNKNOWN member in the base
> dimension and then load your fact data through a view. In the view use a
> CASE clause with an EXISTS and replace the FK being returned based on
> whether or not that key exists. If it doesn't exist, then return the UNKNO
WN
> member.
> In SQL2K5, the system supports an unknown member directly and you can load
> data w/ an error configuration which tells it to assign invalid FKs with t
he
> system generated unknown member directly.
> --
> Dave Wickert [MSFT]
> dwickert@.online.microsoft.com
> Program Manager
> BI SystemsTeam
> SQL BI Product Unit (Analysis Services)
> --
> This posting is provided "AS IS" with no warranties, and confers no rights
.
>
> "appdevtech" <appdevtech@.online.nospam> wrote in message
> news:44BBA514-66F1-4FDD-A0D2-11E6BB6D2F13@.microsoft.com...
>
>|||Hello Myles,
For example, you could add a new record in the dimension table with
"value"="unknown". You could create a view fact1 and use this as the fact
table when you create a cube
create view fact1 as
select f.AccountNumber,
case when exists (select d.value from dim1 d where d.value=f.objectvalue )
then f.[Investment Objective]
else 'unknown'
end
as InvestmentObjective,
f.number
from fact f
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: Handling Data Integrity Issues in SQL2000
| thread-index: AcVzZJNkPr6fSXC8RMqPvlgseXtMiA==
| X-WBNR-Posting-Host: 12.155.246.10
| From: "examnotes" <appdevtech@.online.nospam>
| References: <44BBA514-66F1-4FDD-A0D2-11E6BB6D2F13@.microsoft.com>
<#BtbY5rcFHA.1324@.tk2msftngp13.phx.gbl>
| Subject: Re: Handling Data Integrity Issues in SQL2000
| Date: Fri, 17 Jun 2005 10:47:05 -0700
| Lines: 98
| Message-ID: <6CC7E76E-3E1C-49BE-B976-71D0D694B5AB@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 8bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.datawarehouse
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.datawarehouse:1828
| X-Tomcat-NG: microsoft.public.sqlserver.datawarehouse
|
| Thanks Dave,
| My gut feeling is to fix the RI issues first also, but in this case I can
| use the cube to identify the issues, versus catching the data during
import
| then writing a report to notify the users of the inconsistent data.
|
| When you say "create an UNKNOWN member in the base dimension” do you
mean
| adding an additional record to my dimension table?
|
| Value Description
| A Growth
| B No-Growth
| X Unknown
|
| Then in the view if it does not exist, return X(Unknown)?
|
| "Dave Wickert [MSFT]" wrote:
|
| > (microsoft.public.sqlserver.olap is a better newsgroup for a posting
like
| > this, but here goes)
| >
| > My first gut feel is that you should not be allowing this to occur.
This is
| > basic RI between a fact table and the dimension. You should be
processing
| > your dimension to pickup additions prior to processing the fact table.
This
| > will ensure that this doesn't occur if RI is in-place on the RDBMS.
| >
| > The row is disappearing from the fact table because the default SQL
| > statement is an inner join between the fact table and the dimension
table.
| > Thus the row will not be returned to Analysis Services at all . . . we
| > simply don't see it. The RDBMS eliminates it before we get it.
| >
| > In SQL2K, you best option is to create an UNKNOWN member in the base
| > dimension and then load your fact data through a view. In the view use
a
| > CASE clause with an EXISTS and replace the FK being returned based on
| > whether or not that key exists. If it doesn't exist, then return the
UNKNOWN
| > member.
| >
| > In SQL2K5, the system supports an unknown member directly and you can
load
| > data w/ an error configuration which tells it to assign invalid FKs
with the
| > system generated unknown member directly.
| > --
| > Dave Wickert [MSFT]
| > dwickert@.online.microsoft.com
| > Program Manager
| > BI SystemsTeam
| > SQL BI Product Unit (Analysis Services)
| > --
| > This posting is provided "AS IS" with no warranties, and confers no
rights.
| >
| >
| > "appdevtech" <appdevtech@.online.nospam> wrote in message
| > news:44BBA514-66F1-4FDD-A0D2-11E6BB6D2F13@.microsoft.com...
| > > Hello,
| > > I'm looking for some help with analysis services.
| > >
| > > I ahve a very simple fact table which has 2 columns
| > > Account Number and Investment Objective.
| > >
| > > Fact table
| > > Account Number Investment Objective
| > > 12345678 A
| > > 22222222 A
| > > 33333333 B
| > > 44444444 X
| > >
| > > A dimension is needed for the investment objective
| > > So I have a lookup table which is
| > >
| > > Value Description
| > > A Growth
| > > B No-Growth
| > >
| > > There is not an X value in the lookup table so when I look at the
count
| > > for
| > > accounts I only get 3. The account 44444444 never shows up.
| > > Is there a way to have 44444444 or any other account that might get a
| > > value
| > > not in the lookup table to fall into an 'Unknown' type description?
| > >
| > > I understand the best way to solve is to make sure I have a value in
the
| > > lookup table for every value that is in the investment objective
fact, the
| > > problem is I cant control what might get added to it, and we want to
be
| > > able
| > > to have an unknown description and have everything that falls out of
the
| > > range of the lookup go into that. This will allow the users of the
cube
| > > to
| > > find the bad entries and fix them.
| > >
| > > Of course this is sample data and the real tables have millions of
records
| > > and 100's of columns, but I think the basic concept applies.
| > >
| > > Any help or a direction to go in would be greatly appreciated. BTW
this
| > > is
| > > SQL2000
| > > Thanks
| > >
| >
| >
| >
|

Handling Data Integrity Issues in SQL2000

Hello,
I’m looking for some help with analysis services.
I ahve a very simple fact table which has 2 columns
Account Number and Investment Objective.
Fact table
Account NumberInvestment Objective
12345678A
22222222A
33333333B
44444444X
A dimension is needed for the investment objective
So I have a lookup table which is
ValueDescription
AGrowth
BNo-Growth
There is not an X value in the lookup table so when I look at the count for
accounts I only get 3. The account 44444444 never shows up.
Is there a way to have 44444444 or any other account that might get a value
not in the lookup table to fall into an ‘Unknown’ type description?
I understand the best way to solve is to make sure I have a value in the
lookup table for every value that is in the investment objective fact, the
problem is I cant control what might get added to it, and we want to be able
to have an unknown description and have everything that falls out of the
range of the lookup go into that. This will allow the users of the cube to
find the bad entries and fix them.
Of course this is sample data and the real tables have millions of records
and 100’s of columns, but I think the basic concept applies.
Any help or a direction to go in would be greatly appreciated. BTW this is
SQL2000
Thanks
Hello AppDev,
I am assuming you are using a Star schema in a SQL Server database
somewhere. The Star Schema is used as a basis for the OLAP cube you
are creating.
Remember Analysis Services requires all dimension records to be unique
and fact records to have a relationship with all dimensions. This means
Fact records that do not have a correlating dimension record will not
appear in your cube. (This only happens when you included dimensions
that do not have a relationship with all fact records)
There is a quick solution to this. Create an 'Unknown' record in
your dimension table. When populating the fact table you can return the
unknown surrogate key value to join to the unknown dimension record.
This will make unknown records appear in your cube.
Hope this helps
Myles Matheson
Data Warehouse Architect
|||(microsoft.public.sqlserver.olap is a better newsgroup for a posting like
this, but here goes)
My first gut feel is that you should not be allowing this to occur. This is
basic RI between a fact table and the dimension. You should be processing
your dimension to pickup additions prior to processing the fact table. This
will ensure that this doesn't occur if RI is in-place on the RDBMS.
The row is disappearing from the fact table because the default SQL
statement is an inner join between the fact table and the dimension table.
Thus the row will not be returned to Analysis Services at all . . . we
simply don't see it. The RDBMS eliminates it before we get it.
In SQL2K, you best option is to create an UNKNOWN member in the base
dimension and then load your fact data through a view. In the view use a
CASE clause with an EXISTS and replace the FK being returned based on
whether or not that key exists. If it doesn't exist, then return the UNKNOWN
member.
In SQL2K5, the system supports an unknown member directly and you can load
data w/ an error configuration which tells it to assign invalid FKs with the
system generated unknown member directly.
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"appdevtech" <appdevtech@.online.nospam> wrote in message
news:44BBA514-66F1-4FDD-A0D2-11E6BB6D2F13@.microsoft.com...
> Hello,
> I'm looking for some help with analysis services.
> I ahve a very simple fact table which has 2 columns
> Account Number and Investment Objective.
> Fact table
> Account Number Investment Objective
> 12345678 A
> 22222222 A
> 33333333 B
> 44444444 X
> A dimension is needed for the investment objective
> So I have a lookup table which is
> Value Description
> A Growth
> B No-Growth
> There is not an X value in the lookup table so when I look at the count
> for
> accounts I only get 3. The account 44444444 never shows up.
> Is there a way to have 44444444 or any other account that might get a
> value
> not in the lookup table to fall into an 'Unknown' type description?
> I understand the best way to solve is to make sure I have a value in the
> lookup table for every value that is in the investment objective fact, the
> problem is I cant control what might get added to it, and we want to be
> able
> to have an unknown description and have everything that falls out of the
> range of the lookup go into that. This will allow the users of the cube
> to
> find the bad entries and fix them.
> Of course this is sample data and the real tables have millions of records
> and 100's of columns, but I think the basic concept applies.
> Any help or a direction to go in would be greatly appreciated. BTW this
> is
> SQL2000
> Thanks
>
|||Thanks Dave,
My gut feeling is to fix the RI issues first also, but in this case I can
use the cube to identify the issues, versus catching the data during import
then writing a report to notify the users of the inconsistent data.
When you say "create an UNKNOWN member in the base dimension” do you mean
adding an additional record to my dimension table?
Value Description
A Growth
B No-Growth
X Unknown
Then in the view if it does not exist, return X(Unknown)?
"Dave Wickert [MSFT]" wrote:

> (microsoft.public.sqlserver.olap is a better newsgroup for a posting like
> this, but here goes)
> My first gut feel is that you should not be allowing this to occur. This is
> basic RI between a fact table and the dimension. You should be processing
> your dimension to pickup additions prior to processing the fact table. This
> will ensure that this doesn't occur if RI is in-place on the RDBMS.
> The row is disappearing from the fact table because the default SQL
> statement is an inner join between the fact table and the dimension table.
> Thus the row will not be returned to Analysis Services at all . . . we
> simply don't see it. The RDBMS eliminates it before we get it.
> In SQL2K, you best option is to create an UNKNOWN member in the base
> dimension and then load your fact data through a view. In the view use a
> CASE clause with an EXISTS and replace the FK being returned based on
> whether or not that key exists. If it doesn't exist, then return the UNKNOWN
> member.
> In SQL2K5, the system supports an unknown member directly and you can load
> data w/ an error configuration which tells it to assign invalid FKs with the
> system generated unknown member directly.
> --
> Dave Wickert [MSFT]
> dwickert@.online.microsoft.com
> Program Manager
> BI SystemsTeam
> SQL BI Product Unit (Analysis Services)
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "appdevtech" <appdevtech@.online.nospam> wrote in message
> news:44BBA514-66F1-4FDD-A0D2-11E6BB6D2F13@.microsoft.com...
>
>
|||Hello Myles,
For example, you could add a new record in the dimension table with
"value"="unknown". You could create a view fact1 and use this as the fact
table when you create a cube
create view fact1 as
select f.AccountNumber,
case when exists (select d.value from dim1 d where d.value=f.objectvalue )
then f.[Investment Objective]
else 'unknown'
end
as InvestmentObjective,
f.number
from fact f
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| Thread-Topic: Handling Data Integrity Issues in SQL2000
| thread-index: AcVzZJNkPr6fSXC8RMqPvlgseXtMiA==
| X-WBNR-Posting-Host: 12.155.246.10
| From: "=?Utf-8?B?YXBwZGV2dGVjaA==?=" <appdevtech@.online.nospam>
| References: <44BBA514-66F1-4FDD-A0D2-11E6BB6D2F13@.microsoft.com>
<#BtbY5rcFHA.1324@.tk2msftngp13.phx.gbl>
| Subject: Re: Handling Data Integrity Issues in SQL2000
| Date: Fri, 17 Jun 2005 10:47:05 -0700
| Lines: 98
| Message-ID: <6CC7E76E-3E1C-49BE-B976-71D0D694B5AB@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 8bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.datawarehouse
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.datawarehouse:1828
| X-Tomcat-NG: microsoft.public.sqlserver.datawarehouse
|
| Thanks Dave,
| My gut feeling is to fix the RI issues first also, but in this case I can
| use the cube to identify the issues, versus catching the data during
import
| then writing a report to notify the users of the inconsistent data.
|
| When you say "create an UNKNOWN member in the base dimension” do you
mean
| adding an additional record to my dimension table?
|
| Value Description
| A Growth
| B No-Growth
| X Unknown
|
| Then in the view if it does not exist, return X(Unknown)?
|
| "Dave Wickert [MSFT]" wrote:
|
| > (microsoft.public.sqlserver.olap is a better newsgroup for a posting
like
| > this, but here goes)
| >
| > My first gut feel is that you should not be allowing this to occur.
This is
| > basic RI between a fact table and the dimension. You should be
processing
| > your dimension to pickup additions prior to processing the fact table.
This
| > will ensure that this doesn't occur if RI is in-place on the RDBMS.
| >
| > The row is disappearing from the fact table because the default SQL
| > statement is an inner join between the fact table and the dimension
table.
| > Thus the row will not be returned to Analysis Services at all . . . we
| > simply don't see it. The RDBMS eliminates it before we get it.
| >
| > In SQL2K, you best option is to create an UNKNOWN member in the base
| > dimension and then load your fact data through a view. In the view use
a
| > CASE clause with an EXISTS and replace the FK being returned based on
| > whether or not that key exists. If it doesn't exist, then return the
UNKNOWN
| > member.
| >
| > In SQL2K5, the system supports an unknown member directly and you can
load
| > data w/ an error configuration which tells it to assign invalid FKs
with the
| > system generated unknown member directly.
| > --
| > Dave Wickert [MSFT]
| > dwickert@.online.microsoft.com
| > Program Manager
| > BI SystemsTeam
| > SQL BI Product Unit (Analysis Services)
| > --
| > This posting is provided "AS IS" with no warranties, and confers no
rights.
| >
| >
| > "appdevtech" <appdevtech@.online.nospam> wrote in message
| > news:44BBA514-66F1-4FDD-A0D2-11E6BB6D2F13@.microsoft.com...
| > > Hello,
| > > I'm looking for some help with analysis services.
| > >
| > > I ahve a very simple fact table which has 2 columns
| > > Account Number and Investment Objective.
| > >
| > > Fact table
| > > Account Number Investment Objective
| > > 12345678 A
| > > 22222222 A
| > > 33333333 B
| > > 44444444 X
| > >
| > > A dimension is needed for the investment objective
| > > So I have a lookup table which is
| > >
| > > Value Description
| > > A Growth
| > > B No-Growth
| > >
| > > There is not an X value in the lookup table so when I look at the
count
| > > for
| > > accounts I only get 3. The account 44444444 never shows up.
| > > Is there a way to have 44444444 or any other account that might get a
| > > value
| > > not in the lookup table to fall into an 'Unknown' type description?
| > >
| > > I understand the best way to solve is to make sure I have a value in
the
| > > lookup table for every value that is in the investment objective
fact, the
| > > problem is I cant control what might get added to it, and we want to
be
| > > able
| > > to have an unknown description and have everything that falls out of
the
| > > range of the lookup go into that. This will allow the users of the
cube
| > > to
| > > find the bad entries and fix them.
| > >
| > > Of course this is sample data and the real tables have millions of
records
| > > and 100's of columns, but I think the basic concept applies.
| > >
| > > Any help or a direction to go in would be greatly appreciated. BTW
this
| > > is
| > > SQL2000
| > > Thanks
| > >
| >
| >
| >
|