Indexing in SQL Server Star Scheme Data Warehouse

Hi all,

Our star schema design has one fact table and 3 dimensions.

The FK's in the fact do not necessarily make up the primary key. So I have an identifier in the fact table as PK. Here is my index assignment:

Fact Table - Clustered Index on PK
Non Clustered Index 1 on FK1
Non Clustered Index 2 on FK2
Non Clustered Index 3 on FK3

Each Dimension Table - Clustered Index on PK
Non Clustered Index on Attribute. This is the attribute that will be used in reports / cubes.

Is the above design good to start with?

Thanks,

VThe indexing looks fine, but as to whether this is a good design or not you only need to check my sig below to get my opinion...|||Thanks Blindman.

The one issue that we are encountering is, we didnt create a separate time dimension (a mistake in design). We have a smalldatetime field (72 distinct values only, one for each month, so 6 years in total) in the fact table.

We are not able to query this smalldate time field efficiently because we didnt index it (as it was not part of the dimension). We would like to change the design now.

We would like to create a time dimension using the following:

1. Create Time Dimension Table
2. Create new column Time_Key in Fact Table
3. Create Non clustered Index on smalldatetime field in fact table.
4. Join on smalldatetime fields in Time Dimension and Fact table to populate time_key in 2 from Time Dimension table.
5. Drop the index and column of smalldatetime field in fact and reassign non clustered index to Time_Key (FK)

Let me know how the above approach sounds to you guys.

V|||My gut feeling is that creating and dropping the temporary index on the smalldatetime column will take as long as doing a non-indexed join. Generally, indexes are only valuable because they are used more than once, so the investment involved in creating them is saved over each subsequent operation.
An index on a set of 72 discreet values may not even give you much performance boost across millions of records.|||I suppose the question I would pose to you, more than your design, would be, have you chosen the right granularity for your fact table? I haven't seen too many fact tables that stop at a monthly level unless they are being used for forecasting or budgeting purposes and even then they align to pre-existing warehouses, like a sales warehouse. My best recommendation would be to take some time and study warehousing and ensure you are providing a solution that isn't going to have to be reworked a couple of months down the road when the end users want to be able to drill down into details.

Indexed Views over a Partitioning scheme table

Hello all, I was wondering if anyone as found any information on weather or
not you can place a index view over a partitioning scheme in SQL server 2005.
I have successfully setup a 89 partitioned scheme in SQL 2005 (that works
well) though I also need to build a materialized view over that newly
partitioned data. Oddly I continue to receive an error when I attempt to
place the clustered index on the view.
ERROR:
TITLE: Microsoft SQL Server Management Studio
--
Create failed for Index 'cidx_client_monthcode'. (Microsoft.SqlServer.Smo)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Index&LinkId=20476
--
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
--
Cannot create the clustered index 'cidx_client_monthcode' on view
'Partition_DB.dbo.mv_SummarybyClientAndMonth' because the select list of the
view contains an expression on result of aggregate function or grouping
column. Consider removing expression on result of aggregate function or
grouping column from select list. (Microsoft SQL Server, Error: 8668)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.2047&EvtSrc=MSSQLServer&EvtID=8668&LinkId=20476
Any thoughts
Thanks
Eric"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:C5A6A5B8-FE3A-400F-A6B2-CBA75C8BBCF2@.microsoft.com...
> Hello all, I was wondering if anyone as found any information on weather
> or
> not you can place a index view over a partitioning scheme in SQL server
> 2005.
>
Yes.
drop view iv_T
drop table t
drop partition scheme ps1
drop partition function pf1
go
CREATE PARTITION FUNCTION pf1 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000);
GO
CREATE PARTITION SCHEME ps1
AS PARTITION pf1
ALL TO ( [primary] );
create table t(id int primary key, description varchar(50) null)
on ps1(id)
go
insert into t(id,description)
values (1,'a')
insert into t(id,description)
values (2,null)
go
create view iv_T
with schemabinding
as
select id, description
from dbo.t where description is not null
go
create unique clustered index ix_iv_t
on iv_T(id)
on [primary]
go
select * from t
David|||Hi David, thanks for the quick reply.
Oddly your syntax isn't much different then what I hadâ?¦ Though I did notice
that you had used a single file group. (Though that shouldn't make a
difference right?)
Thanks
Eric
"David Browne" wrote:
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:C5A6A5B8-FE3A-400F-A6B2-CBA75C8BBCF2@.microsoft.com...
> > Hello all, I was wondering if anyone as found any information on weather
> > or
> > not you can place a index view over a partitioning scheme in SQL server
> > 2005.
> >
> Yes.
>
> drop view iv_T
> drop table t
> drop partition scheme ps1
> drop partition function pf1
> go
> CREATE PARTITION FUNCTION pf1 (int)
> AS RANGE LEFT FOR VALUES (1, 100, 1000);
> GO
> CREATE PARTITION SCHEME ps1
> AS PARTITION pf1
> ALL TO ( [primary] );
> create table t(id int primary key, description varchar(50) null)
> on ps1(id)
>
> go
>
> insert into t(id,description)
> values (1,'a')
>
> insert into t(id,description)
> values (2,null)
> go
> create view iv_T
> with schemabinding
> as
> select id, description
> from dbo.t where description is not null
> go
> create unique clustered index ix_iv_t
> on iv_T(id)
> on [primary]
> go
> select * from t
>
> David
>
>|||Sorry in addition, I have a calculation in my view that needs to be there
seeing that the view is being generated at a higher level then the partition
tables grain level.
Here is the code to reproduce the error:
--start code
drop view iv_T
drop table t
drop PARTITION SCHEME ps1
drop PARTITION FUNCTION pf1
go
CREATE PARTITION FUNCTION pf1 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000,10000,100000,1000000,10000000);
go
CREATE PARTITION SCHEME ps1
AS PARTITION pf1
ALL TO ([primary])
go
create table t(id int primary key, description varchar(50) null, amt int null)
on ps1(id)
insert into t(id,description,amt)
values (1,'a', 100)
insert into t(id,description,amt)
values (200,'a', 100)
insert into t(id,description,amt)
values (3000,'b', 100)
insert into t(id,description,amt)
values (40000,'b', 100)
insert into t(id,description,amt)
values (500000,'c', 100)
go
create view iv_T
with schemabinding
as
select description, isnull(sum(amt),0) as amt, count_big(*) as rc
from dbo.t where description is not null
group by description
go
create unique clustered index ix_iv_t
on iv_T(description)
on [primary]
go
--end code
thanks
eric
"David Browne" wrote:
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:C5A6A5B8-FE3A-400F-A6B2-CBA75C8BBCF2@.microsoft.com...
> > Hello all, I was wondering if anyone as found any information on weather
> > or
> > not you can place a index view over a partitioning scheme in SQL server
> > 2005.
> >
> Yes.
>
> drop view iv_T
> drop table t
> drop partition scheme ps1
> drop partition function pf1
> go
> CREATE PARTITION FUNCTION pf1 (int)
> AS RANGE LEFT FOR VALUES (1, 100, 1000);
> GO
> CREATE PARTITION SCHEME ps1
> AS PARTITION pf1
> ALL TO ( [primary] );
> create table t(id int primary key, description varchar(50) null)
> on ps1(id)
>
> go
>
> insert into t(id,description)
> values (1,'a')
>
> insert into t(id,description)
> values (2,null)
> go
> create view iv_T
> with schemabinding
> as
> select id, description
> from dbo.t where description is not null
> go
> create unique clustered index ix_iv_t
> on iv_T(id)
> on [primary]
> go
> select * from t
>
> David
>
>|||"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:42E4A88F-66C6-4D3F-8BE5-254ECF59003C@.microsoft.com...
> Sorry in addition, I have a calculation in my view that needs to be there
> seeing that the view is being generated at a higher level then the
> partition
> tables grain level.
> Here is the code to reproduce the error:
> --start code
> drop view iv_T
> drop table t
> drop PARTITION SCHEME ps1
> drop PARTITION FUNCTION pf1
> go
>
Try this eqivilent formulation:
create view iv_T
with schemabinding
as
select description, sum(isnull(amt,0)) as amt, count_big(*) as rc
from dbo.t where description is not null
group by description
go
create unique clustered index ix_iv_t
on iv_T(description)
on [primary]
David|||Wow! I think I won the dummy of the year award... :)
That was it!
Do you realize how many hours I sat here trying to figure this out? Lets
just say Iâ'm on my 12 red bull and Iâ'm starting to get a sun burn from my
monitorâ?¦LOL.
Thanks David
Eric
"David Browne" wrote:
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:42E4A88F-66C6-4D3F-8BE5-254ECF59003C@.microsoft.com...
> > Sorry in addition, I have a calculation in my view that needs to be there
> > seeing that the view is being generated at a higher level then the
> > partition
> > tables grain level.
> >
> > Here is the code to reproduce the error:
> > --start code
> > drop view iv_T
> > drop table t
> > drop PARTITION SCHEME ps1
> > drop PARTITION FUNCTION pf1
> > go
> >
>
> Try this eqivilent formulation:
> create view iv_T
> with schemabinding
> as
> select description, sum(isnull(amt,0)) as amt, count_big(*) as rc
> from dbo.t where description is not null
> group by description
> go
> create unique clustered index ix_iv_t
> on iv_T(description)
> on [primary]
> David
>
>

Indexed Views over a Partitioning scheme table

Hello all, I was wondering if anyone as found any information on weather or
not you can place a index view over a partitioning scheme in SQL server 2005
.
I have successfully setup a 89 partitioned scheme in SQL 2005 (that works
well) though I also need to build a materialized view over that newly
partitioned data. Oddly I continue to receive an error when I attempt to
place the clustered index on the view.
ERROR:
TITLE: Microsoft SQL Server Management Studio
--
Create failed for Index 'cidx_client_monthcode'. (Microsoft.SqlServer.Smo)
For help, click:
http://go.microsoft.com/fwlink?Prod...ex&LinkId=20476
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
Cannot create the clustered index 'cidx_client_monthcode' on view
'Partition_DB.dbo.mv_SummarybyClientAndMonth' because the select list of the
view contains an expression on result of aggregate function or grouping
column. Consider removing expression on result of aggregate function or
grouping column from select list. (Microsoft SQL Server, Error: 8668)
For help, click:
http://go.microsoft.com/fwlink?Prod...68&LinkId=20476
Any thoughts
Thanks
Eric"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:C5A6A5B8-FE3A-400F-A6B2-CBA75C8BBCF2@.microsoft.com...
> Hello all, I was wondering if anyone as found any information on weather
> or
> not you can place a index view over a partitioning scheme in SQL server
> 2005.
>
Yes.
drop view iv_T
drop table t
drop partition scheme ps1
drop partition function pf1
go
CREATE PARTITION FUNCTION pf1 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000);
GO
CREATE PARTITION SCHEME ps1
AS PARTITION pf1
ALL TO ( [primary] );
create table t(id int primary key, description varchar(50) null)
on ps1(id)
go
insert into t(id,description)
values (1,'a')
insert into t(id,description)
values (2,null)
go
create view iv_T
with schemabinding
as
select id, description
from dbo.t where description is not null
go
create unique clustered index ix_iv_t
on iv_T(id)
on [primary]
go
select * from t
David|||Hi David, thanks for the quick reply.
Oddly your syntax isn't much different then what I had… Though I did notic
e
that you had used a single file group. (Though that shouldn't make a
difference right?)
Thanks
Eric
"David Browne" wrote:

> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:C5A6A5B8-FE3A-400F-A6B2-CBA75C8BBCF2@.microsoft.com...
> Yes.
>
> drop view iv_T
> drop table t
> drop partition scheme ps1
> drop partition function pf1
> go
> CREATE PARTITION FUNCTION pf1 (int)
> AS RANGE LEFT FOR VALUES (1, 100, 1000);
> GO
> CREATE PARTITION SCHEME ps1
> AS PARTITION pf1
> ALL TO ( [primary] );
> create table t(id int primary key, description varchar(50) null)
> on ps1(id)
>
> go
>
> insert into t(id,description)
> values (1,'a')
>
> insert into t(id,description)
> values (2,null)
> go
> create view iv_T
> with schemabinding
> as
> select id, description
> from dbo.t where description is not null
> go
> create unique clustered index ix_iv_t
> on iv_T(id)
> on [primary]
> go
> select * from t
>
> David
>
>|||Sorry in addition, I have a calculation in my view that needs to be there
seeing that the view is being generated at a higher level then the partition
tables grain level.
Here is the code to reproduce the error:
--start code
drop view iv_T
drop table t
drop PARTITION SCHEME ps1
drop PARTITION FUNCTION pf1
go
CREATE PARTITION FUNCTION pf1 (int)
AS RANGE LEFT FOR VALUES (1, 100, 1000,10000,100000,1000000,10000000);
go
CREATE PARTITION SCHEME ps1
AS PARTITION pf1
ALL TO ([primary])
go
create table t(id int primary key, description varchar(50) null, amt int nul
l)
on ps1(id)
insert into t(id,description,amt)
values (1,'a', 100)
insert into t(id,description,amt)
values (200,'a', 100)
insert into t(id,description,amt)
values (3000,'b', 100)
insert into t(id,description,amt)
values (40000,'b', 100)
insert into t(id,description,amt)
values (500000,'c', 100)
go
create view iv_T
with schemabinding
as
select description, isnull(sum(amt),0) as amt, count_big(*) as rc
from dbo.t where description is not null
group by description
go
create unique clustered index ix_iv_t
on iv_T(description)
on [primary]
go
--end code
thanks
eric
"David Browne" wrote:

> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:C5A6A5B8-FE3A-400F-A6B2-CBA75C8BBCF2@.microsoft.com...
> Yes.
>
> drop view iv_T
> drop table t
> drop partition scheme ps1
> drop partition function pf1
> go
> CREATE PARTITION FUNCTION pf1 (int)
> AS RANGE LEFT FOR VALUES (1, 100, 1000);
> GO
> CREATE PARTITION SCHEME ps1
> AS PARTITION pf1
> ALL TO ( [primary] );
> create table t(id int primary key, description varchar(50) null)
> on ps1(id)
>
> go
>
> insert into t(id,description)
> values (1,'a')
>
> insert into t(id,description)
> values (2,null)
> go
> create view iv_T
> with schemabinding
> as
> select id, description
> from dbo.t where description is not null
> go
> create unique clustered index ix_iv_t
> on iv_T(id)
> on [primary]
> go
> select * from t
>
> David
>
>|||"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:42E4A88F-66C6-4D3F-8BE5-254ECF59003C@.microsoft.com...
> Sorry in addition, I have a calculation in my view that needs to be there
> seeing that the view is being generated at a higher level then the
> partition
> tables grain level.
> Here is the code to reproduce the error:
> --start code
> drop view iv_T
> drop table t
> drop PARTITION SCHEME ps1
> drop PARTITION FUNCTION pf1
> go
>
Try this eqivilent formulation:
create view iv_T
with schemabinding
as
select description, sum(isnull(amt,0)) as amt, count_big(*) as rc
from dbo.t where description is not null
group by description
go
create unique clustered index ix_iv_t
on iv_T(description)
on [primary]
David|||Wow! I think I won the dummy of the year award...
That was it!
Do you realize how many hours I sat here trying to figure this out? Lets
just say I’m on my 12 red bull and I’m starting to get a sun burn from m
y
monitor…LOL.
Thanks David
Eric
"David Browne" wrote:

> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:42E4A88F-66C6-4D3F-8BE5-254ECF59003C@.microsoft.com...
>
> Try this eqivilent formulation:
> create view iv_T
> with schemabinding
> as
> select description, sum(isnull(amt,0)) as amt, count_big(*) as rc
> from dbo.t where description is not null
> group by description
> go
> create unique clustered index ix_iv_t
> on iv_T(description)
> on [primary]
> David
>
>