Central Database Design

SQL SERVER 2K
We have 30 databases each for one business units.
Now I am consolidating few tables from 30 databases to one database for
Analysis purpose.
Now my tables in Central db have millions of records.
I need some tips to handle this huge tables for query purpose (already
applied with all Indexes).
Can I go with INDEXED VIEWS '
Thx
ShIndexed views are only really good for aggregations and joining data from
mutiple tables, is this what you need to do? Several of our tables are
over a million rows in our production environment and it all comes down to
indexing. Did you create new indexes designed for queries or are they the
existing indexes from the original tables?
"Shamim" <shamim.abdul@.railamerica.com> wrote in message
news:#vojbnEYDHA.536@.TK2MSFTNGP10.phx.gbl...
> SQL SERVER 2K
> We have 30 databases each for one business units.
> Now I am consolidating few tables from 30 databases to one database for
> Analysis purpose.
> Now my tables in Central db have millions of records.
> I need some tips to handle this huge tables for query purpose (already
> applied with all Indexes).
> Can I go with INDEXED VIEWS '
> Thx
> Sh
>|||I am sorry, I meant PARTITIONED VIEWS
Sh
"Kevin Brooks" <kbrooks@.sagetelecom.net> wrote in message
news:OPszDvEYDHA.2204@.TK2MSFTNGP12.phx.gbl...
> Indexed views are only really good for aggregations and joining data from
> mutiple tables, is this what you need to do? Several of our tables are
> over a million rows in our production environment and it all comes down to
> indexing. Did you create new indexes designed for queries or are they the
> existing indexes from the original tables?
>
> "Shamim" <shamim.abdul@.railamerica.com> wrote in message
> news:#vojbnEYDHA.536@.TK2MSFTNGP10.phx.gbl...
> > SQL SERVER 2K
> >
> > We have 30 databases each for one business units.
> > Now I am consolidating few tables from 30 databases to one database for
> > Analysis purpose.
> >
> > Now my tables in Central db have millions of records.
> > I need some tips to handle this huge tables for query purpose (already
> > applied with all Indexes).
> >
> > Can I go with INDEXED VIEWS '
> >
> > Thx
> > Sh
> >
> >
>|||Partition views can help here. You will be able to keep table sizes down
and this can increase query times when done right. It sounds like you moved
multiple tables into one table. If this is the case you will need to break
them back out into smaller tables, add partitioning columns, modify Primary
Keys and build the view. That might be a hassle. I can query a 20million
record table and get a response back in under a second, I just did a query
on 250million and elasped time was 10sec(only one index on there, I know I
could get quicker:)). Did you change indexes for reporting or not?
"Shamim" <shamim.abdul@.railamerica.com> wrote in message
news:#dGAN7EYDHA.2200@.TK2MSFTNGP09.phx.gbl...
> I am sorry, I meant PARTITIONED VIEWS
> Sh
> "Kevin Brooks" <kbrooks@.sagetelecom.net> wrote in message
> news:OPszDvEYDHA.2204@.TK2MSFTNGP12.phx.gbl...
> > Indexed views are only really good for aggregations and joining data
from
> > mutiple tables, is this what you need to do? Several of our tables
are
> > over a million rows in our production environment and it all comes down
to
> > indexing. Did you create new indexes designed for queries or are they
the
> > existing indexes from the original tables?
> >
> >
> > "Shamim" <shamim.abdul@.railamerica.com> wrote in message
> > news:#vojbnEYDHA.536@.TK2MSFTNGP10.phx.gbl...
> > > SQL SERVER 2K
> > >
> > > We have 30 databases each for one business units.
> > > Now I am consolidating few tables from 30 databases to one database
for
> > > Analysis purpose.
> > >
> > > Now my tables in Central db have millions of records.
> > > I need some tips to handle this huge tables for query purpose (already
> > > applied with all Indexes).
> > >
> > > Can I go with INDEXED VIEWS '
> > >
> > > Thx
> > > Sh
> > >
> > >
> >
> >
>|||Thanks for the suggestions.
Sh
"Kevin Brooks" <kbrooks@.sagetelecom.net> wrote in message
news:OitSnTFYDHA.2032@.TK2MSFTNGP10.phx.gbl...
> Partition views can help here. You will be able to keep table sizes down
> and this can increase query times when done right. It sounds like you
moved
> multiple tables into one table. If this is the case you will need to
break
> them back out into smaller tables, add partitioning columns, modify
Primary
> Keys and build the view. That might be a hassle. I can query a 20million
> record table and get a response back in under a second, I just did a query
> on 250million and elasped time was 10sec(only one index on there, I know I
> could get quicker:)). Did you change indexes for reporting or not?
>
> "Shamim" <shamim.abdul@.railamerica.com> wrote in message
> news:#dGAN7EYDHA.2200@.TK2MSFTNGP09.phx.gbl...
> > I am sorry, I meant PARTITIONED VIEWS
> >
> > Sh
> >
> > "Kevin Brooks" <kbrooks@.sagetelecom.net> wrote in message
> > news:OPszDvEYDHA.2204@.TK2MSFTNGP12.phx.gbl...
> > > Indexed views are only really good for aggregations and joining data
> from
> > > mutiple tables, is this what you need to do? Several of our tables
> are
> > > over a million rows in our production environment and it all comes
down
> to
> > > indexing. Did you create new indexes designed for queries or are they
> the
> > > existing indexes from the original tables?
> > >
> > >
> > > "Shamim" <shamim.abdul@.railamerica.com> wrote in message
> > > news:#vojbnEYDHA.536@.TK2MSFTNGP10.phx.gbl...
> > > > SQL SERVER 2K
> > > >
> > > > We have 30 databases each for one business units.
> > > > Now I am consolidating few tables from 30 databases to one database
> for
> > > > Analysis purpose.
> > > >
> > > > Now my tables in Central db have millions of records.
> > > > I need some tips to handle this huge tables for query purpose
(already
> > > > applied with all Indexes).
> > > >
> > > > Can I go with INDEXED VIEWS '
> > > >
> > > > Thx
> > > > Sh
> > > >
> > > >
> > >
> > >
> >
> >
>

Cell Drillthrough in Reporting Services 2005

Hi,

I’ve build a report in reporting services 2005, based on a MOLAP cube (Analysis Services 2005). In the cube I’ve enabled drillthrough.

I know that we can define drillthrough in the report by clicking on one of the members of the dimension hierarchy, but that’s not what I want. I want to be able to drillthrough on a cell. Is there any way how to do that in Reporting Services 2005?

Thanks,

AbdelSSAS actions are not carried to SSRS reports. One workaround is to create a drillthrough report which gets the context from the master report and executes the DRILLTHROUGH MDX query.|||

Hi Abdel ,

You have written U know how to define a drillthrough in the report by clicking on one of the members of the dimension hierarchy . can you please tell me how we will do that ?

I am trying to make a drillthrough report ,can you please give me a step by step process for enabling drill through in the reports .

I want to jump to a child report & pass the parameters of the parent report to the child report , I am able to get the parameters value in the child report ,but not able to use those values in the dataset query of the child report .

can you please tell me if this is possible or not ? & if yes how ?

Thanks

Rashmi

|||Please note the the original question was asking about a drillthrough action in the cube. If you just want the user to drilll through to another report, you can use the SSRS navigation feature. To do so, right-click on the textbox which will be use for drillthrough and choose Properties. Then go to the Navigation tab and use the Jump to Report feature. Note that that if the second report will retrieve data from the cube as well, you need to pass the parameters in the required format, e.g.; [Date].[Date].&[20070523]

Cell Drillthrough in Reporting Services 2005

Hi,

I’ve build a report in reporting services 2005, based on a MOLAP cube (Analysis Services 2005). In the cube I’ve enabled drillthrough.

I know that we can define drillthrough in the report by clicking on one of the members of the dimension hierarchy, but that’s not what I want. I want to be able to drillthrough on a cell. Is there any way how to do that in Reporting Services 2005?

Thanks,

AbdelSSAS actions are not carried to SSRS reports. One workaround is to create a drillthrough report which gets the context from the master report and executes the DRILLTHROUGH MDX query.|||

Hi Abdel ,

You have written U know how to define a drillthrough in the report by clicking on one of the members of the dimension hierarchy . can you please tell me how we will do that ?

I am trying to make a drillthrough report ,can you please give me a step by step process for enabling drill through in the reports .

I want to jump to a child report & pass the parameters of the parent report to the child report , I am able to get the parameters value in the child report ,but not able to use those values in the dataset query of the child report .

can you please tell me if this is possible or not ? & if yes how ?

Thanks

Rashmi

|||Please note the the original question was asking about a drillthrough action in the cube. If you just want the user to drilll through to another report, you can use the SSRS navigation feature. To do so, right-click on the textbox which will be use for drillthrough and choose Properties. Then go to the Navigation tab and use the Jump to Report feature. Note that that if the second report will retrieve data from the cube as well, you need to pass the parameters in the required format, e.g.; [Date].[Date].&[20070523]