Showing posts with label structure. Show all posts
Showing posts with label structure. Show all posts

Friday, March 23, 2012

Many to many or not ?

Hello I have a question regarding many to many diemensions.

I have a fact table with the following structure :

- fact_id

- act_cat

- amount

The dimension table 'PL structure' has the following structure :

- pl id

- report_view

- act_cat

- name

One line from the fact table relates to two lines from the dimension table.

Example:

the act_cat 'Holiday' frol the fact table relates to the act_cat 'Holiday' in the dimension table. But in the dimension table the act_cat 'Holdiay' exists two times, one time with report_view 'View 1' and one time with report_view 'View 2'

The report_view will become a parameter on the reports. So the end-user must always select one report_view.

Now is the question. how can I solve this problem in Analysis Services. Can I do this only with many to many dimensions, or is there another solution.

The name "Report_view" suggest me that you may face this scenario in a different way, but assuming that you really have to handle this situation, the many-to-many approach should be working (below I suggest you how).

But please let me explain one thing: it is always strange when you have a dimension (PL structure) that has an ID (pl id) that is not referenced into the fact table. You don't have the star schema, and when your relational model is not star-schema based, you always have some hidden issue that was not solved in the relationa design.

Anyway, if for whatever reason you model is the best one (or is the only you can use...) then this is a possible solution.

You have to define one named query (or a view) that I name Factless_ActCat_PL_Id:

SELECT pl_id, act_cat FROM [PL Structure]

Then you have to define another named query (or view) that I name Dim_Act_Cat:

SELECT DISTINCT act_cat FROM [PL Structure]

At this point you have two fact tables and two dimensions.

You define the Data Source View with these 4 tables/views and you have to define this logical primary key by hand if the wizard doesn't find them:

act_cat must be the primary key for Dim_Act_Cat.

Then you define 2 dimension (PL structure and Dim_Act_Cat) and two measure groups (original fact table and Factless_Act_Cat_PL_Id). Your fact table has a regular relationship with Dim_Act_Cat and a many-to-many relationship with PL Structure - to build that, the Factles_Act_Cat_PL_Id measure group must have two regular relationships with PL structure and Dim_Act_Cat dimensions.

Please read my paper on many-to-many dimensions if you are in trouble with these concepts:
http://www.sqlbi.eu/manytomany.aspx

Let me know if it works as you expected.

Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi

Many to many or not ?

Hello I have a question regarding many to many diemensions.

I have a fact table with the following structure :

- fact_id

- act_cat

- amount

The dimension table 'PL structure' has the following structure :

- pl id

- report_view

- act_cat

- name

One line from the fact table relates to two lines from the dimension table.

Example:

the act_cat 'Holiday' frol the fact table relates to the act_cat 'Holiday' in the dimension table. But in the dimension table the act_cat 'Holdiay' exists two times, one time with report_view 'View 1' and one time with report_view 'View 2'

The report_view will become a parameter on the reports. So the end-user must always select one report_view.

Now is the question. how can I solve this problem in Analysis Services. Can I do this only with many to many dimensions, or is there another solution.

The name "Report_view" suggest me that you may face this scenario in a different way, but assuming that you really have to handle this situation, the many-to-many approach should be working (below I suggest you how).

But please let me explain one thing: it is always strange when you have a dimension (PL structure) that has an ID (pl id) that is not referenced into the fact table. You don't have the star schema, and when your relational model is not star-schema based, you always have some hidden issue that was not solved in the relationa design.

Anyway, if for whatever reason you model is the best one (or is the only you can use...) then this is a possible solution.

You have to define one named query (or a view) that I name Factless_ActCat_PL_Id:

SELECT pl_id, act_cat FROM [PL Structure]

Then you have to define another named query (or view) that I name Dim_Act_Cat:

SELECT DISTINCT act_cat FROM [PL Structure]

At this point you have two fact tables and two dimensions.

You define the Data Source View with these 4 tables/views and you have to define this logical primary key by hand if the wizard doesn't find them:

act_cat must be the primary key for Dim_Act_Cat.

Then you define 2 dimension (PL structure and Dim_Act_Cat) and two measure groups (original fact table and Factless_Act_Cat_PL_Id). Your fact table has a regular relationship with Dim_Act_Cat and a many-to-many relationship with PL Structure - to build that, the Factles_Act_Cat_PL_Id measure group must have two regular relationships with PL structure and Dim_Act_Cat dimensions.

Please read my paper on many-to-many dimensions if you are in trouble with these concepts:
http://www.sqlbi.eu/manytomany.aspx

Let me know if it works as you expected.

Marco Russo
http://www.sqlbi.eu
http://www.sqljunkies.com/weblog/sqlbi

Monday, February 20, 2012

Management Studio: Project Hierarchy

I created a project type "SQL Server Scripts" in "Microsoft SQL Server
Management Studio", and the default structure created was:
+ Project Name
+ Connections (Type 2)
+ Queries (Type 0)
+ Miscellaneous (Type 3)
How can I create new folders? I'd like to organize my queries by subject
(Finance, Human Resources, Marketing, etc)
I tried to edit the project file (*.ssmssqlproj) to add new Logical Folders,
but it seems those folders are based on "Type" and you cannot have multiple
folders of the same type.
<LogicalFolder Name="Queries" Type="0">
After frustrating attempts, I decided to use the existing hierarchy. I added
my existing stored procedures to the "Queries" folder and realized the files
were not in alphabetical order. It's impossible to find anything when list
contains more than a thousand queries.
Am I using the SQL MANAGEMENT STUDIO correctly? If so, what's the purpose of
creating a SQL SCRIPT PROJECT if you cannot organize the files the way you
want? Please help!!!
I posted the same question a few days ago, and I also played around with the
project file to no avial. Someone replied then that this is an outstanding
issue with Microsoft. Please go to the site, vote on the issue, and add your
voice. If we don't complain, MS isn't going to do anything about it:
http://lab.msdn.microsoft.com/produc...7-be25db5080df
My feeling is that the whole VS 2005 suite, including Management Studio and
Team System, was rushed out and shipped half cooked.
Having replaced the graceful Query Analyzer and Enterprise Manager with a
lumberous piece of bloatware (VS 2005 engine), you'd expect them to
compensate the developers/DBA's by allowing them to integrate SSMS with VS
2005 solutions or at least give them the ability to organize their projects
in a meaningful way.
"YYZRic" wrote:

> I created a project type "SQL Server Scripts" in "Microsoft SQL Server
> Management Studio", and the default structure created was:
> + Project Name
> + Connections (Type 2)
> + Queries (Type 0)
> + Miscellaneous (Type 3)
> How can I create new folders? I'd like to organize my queries by subject
> (Finance, Human Resources, Marketing, etc)
> I tried to edit the project file (*.ssmssqlproj) to add new Logical Folders,
> but it seems those folders are based on "Type" and you cannot have multiple
> folders of the same type.
> <LogicalFolder Name="Queries" Type="0">
> After frustrating attempts, I decided to use the existing hierarchy. I added
> my existing stored procedures to the "Queries" folder and realized the files
> were not in alphabetical order. It's impossible to find anything when list
> contains more than a thousand queries.
> Am I using the SQL MANAGEMENT STUDIO correctly? If so, what's the purpose of
> creating a SQL SCRIPT PROJECT if you cannot organize the files the way you
> want? Please help!!!
>
>
>
|||Thanks Buddie, I cast my vote!
"Homam" wrote:
[vbcol=seagreen]
> I posted the same question a few days ago, and I also played around with the
> project file to no avial. Someone replied then that this is an outstanding
> issue with Microsoft. Please go to the site, vote on the issue, and add your
> voice. If we don't complain, MS isn't going to do anything about it:
> http://lab.msdn.microsoft.com/produc...7-be25db5080df
> My feeling is that the whole VS 2005 suite, including Management Studio and
> Team System, was rushed out and shipped half cooked.
> Having replaced the graceful Query Analyzer and Enterprise Manager with a
> lumberous piece of bloatware (VS 2005 engine), you'd expect them to
> compensate the developers/DBA's by allowing them to integrate SSMS with VS
> 2005 solutions or at least give them the ability to organize their projects
> in a meaningful way.
>
>
> "YYZRic" wrote: