a couple reporting/ normalization questions

I have one DB in specific that fails to even be in 1NF, much less 3rd. It is
used only for reporting, no OLTP. The queries are quite slow to say the
least. Every day this data is truncated and repopulated from a mainframe.
From what I can tell there are a few options here.
1; Normalize it. The problem here is there are 150+ Stored Procs dependant
on the existing structure, and of course nobody has time to re-write them.
Theres probably software that can do this for us, (?) but if so, does it
really work well? Also, the DTS Package that populates this DB every day
would need total overhauling.
2; Forget this pile of crap. Let it stay how it is for existing stuff. But
have new stuff go into Analysis Services, and then use Reporting Services.
Everyone here want to use AS and RS anyways, so thats the way we are
leaning. The problem is I have little experience with either of these tools.
Im open to the challenge and we move fairly slow anyways, so I think it can
work. Now my questions.
1; Can Reporting Services connect/report from Analysis Services?
2, Can AS be populated from a totally de-normalized DB? Can I design the
cubes how they should be, and take the data from several different tables to
populate them?
3; If #2 isnt hopefull, would I be better off to create a new, well designed
db, populate that, and from there populate AS?
All insights are appreciated.
--
TIA,
ChrisRto answer number2
You should use DTS to do this
Create the structure that you think will work for you amd then you can
create your cubes (fact and dimension tables) however you want
http://sqlservercode.blogspot.com/
"ChrisR" wrote:
> I have one DB in specific that fails to even be in 1NF, much less 3rd. It is
> used only for reporting, no OLTP. The queries are quite slow to say the
> least. Every day this data is truncated and repopulated from a mainframe.
> From what I can tell there are a few options here.
> 1; Normalize it. The problem here is there are 150+ Stored Procs dependant
> on the existing structure, and of course nobody has time to re-write them.
> Theres probably software that can do this for us, (?) but if so, does it
> really work well? Also, the DTS Package that populates this DB every day
> would need total overhauling.
> 2; Forget this pile of crap. Let it stay how it is for existing stuff. But
> have new stuff go into Analysis Services, and then use Reporting Services.
> Everyone here want to use AS and RS anyways, so thats the way we are
> leaning. The problem is I have little experience with either of these tools.
> Im open to the challenge and we move fairly slow anyways, so I think it can
> work. Now my questions.
> 1; Can Reporting Services connect/report from Analysis Services?
> 2, Can AS be populated from a totally de-normalized DB? Can I design the
> cubes how they should be, and take the data from several different tables to
> populate them?
> 3; If #2 isnt hopefull, would I be better off to create a new, well designed
> db, populate that, and from there populate AS?
> All insights are appreciated.
> --
> TIA,
> ChrisR|||Chris,
1. Yes you can use AS as a source of data to RS.
2 & 3. Depends on how the data is structured. A normalized structure to a
denormalized (star or snowflake) structure would be prefered.
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:BE095EAD-5738-43EC-A2EC-1BF20E837BEF@.microsoft.com...
>I have one DB in specific that fails to even be in 1NF, much less 3rd. It
>is
> used only for reporting, no OLTP. The queries are quite slow to say the
> least. Every day this data is truncated and repopulated from a mainframe.
> From what I can tell there are a few options here.
> 1; Normalize it. The problem here is there are 150+ Stored Procs dependant
> on the existing structure, and of course nobody has time to re-write them.
> Theres probably software that can do this for us, (?) but if so, does it
> really work well? Also, the DTS Package that populates this DB every day
> would need total overhauling.
> 2; Forget this pile of crap. Let it stay how it is for existing stuff. But
> have new stuff go into Analysis Services, and then use Reporting Services.
> Everyone here want to use AS and RS anyways, so thats the way we are
> leaning. The problem is I have little experience with either of these
> tools.
> Im open to the challenge and we move fairly slow anyways, so I think it
> can
> work. Now my questions.
> 1; Can Reporting Services connect/report from Analysis Services?
> 2, Can AS be populated from a totally de-normalized DB? Can I design the
> cubes how they should be, and take the data from several different tables
> to
> populate them?
> 3; If #2 isnt hopefull, would I be better off to create a new, well
> designed
> db, populate that, and from there populate AS?
> All insights are appreciated.
> --
> TIA,
> ChrisR|||Do both of you mean that I can, or cannot, populate well designed cubes from
my poorly designed tables? I think you are both saying I can, but want to
clarify.
Thanks, ChrisR
"ChrisR" wrote:
> I have one DB in specific that fails to even be in 1NF, much less 3rd. It is
> used only for reporting, no OLTP. The queries are quite slow to say the
> least. Every day this data is truncated and repopulated from a mainframe.
> From what I can tell there are a few options here.
> 1; Normalize it. The problem here is there are 150+ Stored Procs dependant
> on the existing structure, and of course nobody has time to re-write them.
> Theres probably software that can do this for us, (?) but if so, does it
> really work well? Also, the DTS Package that populates this DB every day
> would need total overhauling.
> 2; Forget this pile of crap. Let it stay how it is for existing stuff. But
> have new stuff go into Analysis Services, and then use Reporting Services.
> Everyone here want to use AS and RS anyways, so thats the way we are
> leaning. The problem is I have little experience with either of these tools.
> Im open to the challenge and we move fairly slow anyways, so I think it can
> work. Now my questions.
> 1; Can Reporting Services connect/report from Analysis Services?
> 2, Can AS be populated from a totally de-normalized DB? Can I design the
> cubes how they should be, and take the data from several different tables to
> populate them?
> 3; If #2 isnt hopefull, would I be better off to create a new, well designed
> db, populate that, and from there populate AS?
> All insights are appreciated.
> --
> TIA,
> ChrisR|||Chris,
It's kinda like asking can I get 'there' from 'here' without being able to
accurately qualify where 'here' is. AS cubes are built off of denormalized
structures (star and snowflake). I'm "somewhat" sure with views and DTS you
can populate the structure tables for the AS cubes or possibly the AS cubes
themselves. Would it be easier if you were working with a normalized design
to start with? Would there be less coding and less possiblity for error?
I'd say yes to both.
HTH
Jerry
"ChrisR" <ChrisR@.discussions.microsoft.com> wrote in message
news:1C1291EB-FAD5-41E1-8655-5E3BC7B7B695@.microsoft.com...
> Do both of you mean that I can, or cannot, populate well designed cubes
> from
> my poorly designed tables? I think you are both saying I can, but want to
> clarify.
> Thanks, ChrisR
>
> "ChrisR" wrote:
>> I have one DB in specific that fails to even be in 1NF, much less 3rd. It
>> is
>> used only for reporting, no OLTP. The queries are quite slow to say the
>> least. Every day this data is truncated and repopulated from a mainframe.
>> From what I can tell there are a few options here.
>> 1; Normalize it. The problem here is there are 150+ Stored Procs
>> dependant
>> on the existing structure, and of course nobody has time to re-write
>> them.
>> Theres probably software that can do this for us, (?) but if so, does it
>> really work well? Also, the DTS Package that populates this DB every day
>> would need total overhauling.
>> 2; Forget this pile of crap. Let it stay how it is for existing stuff.
>> But
>> have new stuff go into Analysis Services, and then use Reporting
>> Services.
>> Everyone here want to use AS and RS anyways, so thats the way we are
>> leaning. The problem is I have little experience with either of these
>> tools.
>> Im open to the challenge and we move fairly slow anyways, so I think it
>> can
>> work. Now my questions.
>> 1; Can Reporting Services connect/report from Analysis Services?
>> 2, Can AS be populated from a totally de-normalized DB? Can I design the
>> cubes how they should be, and take the data from several different tables
>> to
>> populate them?
>> 3; If #2 isnt hopefull, would I be better off to create a new, well
>> designed
>> db, populate that, and from there populate AS?
>> All insights are appreciated.
>> --
>> TIA,
>> ChrisR