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
to 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...[vbcol=seagreen]
> 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:
sql
Showing posts with label oltp. Show all posts
Showing posts with label oltp. Show all posts
Sunday, March 25, 2012
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
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
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 tool
s.
> Im open to the challenge and we move fairly slow anyways, so I think it ca
n
> 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 design
ed
> 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 tool
s.
> Im open to the challenge and we move fairly slow anyways, so I think it ca
n
> 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 design
ed
> 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...[vbcol=seagreen]
> 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:
>
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 tool
s.
> Im open to the challenge and we move fairly slow anyways, so I think it ca
n
> 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 design
ed
> 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 tool
s.
> Im open to the challenge and we move fairly slow anyways, so I think it ca
n
> 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 design
ed
> 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...[vbcol=seagreen]
> 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:
>
Tuesday, March 20, 2012
A challenging CUBE related question
Hi,
I have data coming in from our OLTP's view which has the following sales related information:
1) Date of Sale
2) Product Sold
3) Customer to which the Product was sold
4) QTy. sold
5) Total Sale Amount
I am bringing in this information into my "staging area" and from there I am building my Customer, Product, and Time Dimension and obviously my FACT table. The measures in my fact table were TotalSalesand Total QtySold and everything was working fine and I was able to analyze, slice, dice my cube in many ways...
Now the problem:
I also have planning related data available from another OLTP based view which contains the following information:
1) Plan Year
2) Plan Month (using Plan Year and Plan month,I "derived" a date which for any month was the 1st of that month)
3) Planned Product
4) Planned Sale (Amount)
5) Planned Qty (for sale)
You would have noticed that planning is NOT done at "Customer Level" and the planning data is only for product and time level (and that too only upto month level).
After populating my FAct table from the Sales data, I appended the planning related data into my fact table hoping that as long as I do not select any thing in the Customer dimesion and only go as low as the month level on my Time dimension, I will be able to see all of the following (measures) for any/all products:
Planned Sale, Actual Sale, Planned Qty., Actual Qty,
However I am unable to get anything displayed in the Planned Sale and Planned Qty despite applying all of the little knowledge that Ihave so far in the world of BI and making data marts
...
I really hope that my questions and requirement is clear.Can someone please help me get to
the solution.
Many many TIA.I hope I understand you correctly. If I did, I would have thought of doing the following (thinking from OLAP standpoint):
Dimensions:
- Create shared dimension for Time to support Date of Sale and Plan Month, Year
- Create shared dimension for Product
Cubes/facts:
- Create two separate cubes one for Actual and one for Planned using the shared dimensions. The Actual can have the Customer as a Private Dimension (it may help to design the customer also as a shared dimension).
- Create a virtual cube on top of the Actual and Planned.
- Create calculated measures for differentials between Actual and Planned.
Murthy
I have data coming in from our OLTP's view which has the following sales related information:
1) Date of Sale
2) Product Sold
3) Customer to which the Product was sold
4) QTy. sold
5) Total Sale Amount
I am bringing in this information into my "staging area" and from there I am building my Customer, Product, and Time Dimension and obviously my FACT table. The measures in my fact table were TotalSalesand Total QtySold and everything was working fine and I was able to analyze, slice, dice my cube in many ways...
Now the problem:
I also have planning related data available from another OLTP based view which contains the following information:
1) Plan Year
2) Plan Month (using Plan Year and Plan month,I "derived" a date which for any month was the 1st of that month)
3) Planned Product
4) Planned Sale (Amount)
5) Planned Qty (for sale)
You would have noticed that planning is NOT done at "Customer Level" and the planning data is only for product and time level (and that too only upto month level).
After populating my FAct table from the Sales data, I appended the planning related data into my fact table hoping that as long as I do not select any thing in the Customer dimesion and only go as low as the month level on my Time dimension, I will be able to see all of the following (measures) for any/all products:
Planned Sale, Actual Sale, Planned Qty., Actual Qty,
However I am unable to get anything displayed in the Planned Sale and Planned Qty despite applying all of the little knowledge that Ihave so far in the world of BI and making data marts
...
I really hope that my questions and requirement is clear.Can someone please help me get to
the solution.
Many many TIA.I hope I understand you correctly. If I did, I would have thought of doing the following (thinking from OLAP standpoint):
Dimensions:
- Create shared dimension for Time to support Date of Sale and Plan Month, Year
- Create shared dimension for Product
Cubes/facts:
- Create two separate cubes one for Actual and one for Planned using the shared dimensions. The Actual can have the Customer as a Private Dimension (it may help to design the customer also as a shared dimension).
- Create a virtual cube on top of the Actual and Planned.
- Create calculated measures for differentials between Actual and Planned.
Murthy
Subscribe to:
Posts (Atom)