Hi,

Amongst others, I have the following dimensions:

1. Distributors

2. Customers

3. Country


Both the Distributors and Customers dimensions fields called Regionid,
Stateid, and Cityid (I'll refer to these as Geography fields later in
this post). As you would probably have figured out these are to be
linked with the Country Dimension which has all these fields (Regionid,
Stateid, and Cityid) but with more details for each e.g. Name etc. etc.

The problem which I am facing (really frustrated becuase of it) is that
in the Cube Editor after I have linked the Geography fields of Customers
and Distributors with the Geography fields of the Country Dimension and
when I try to SAVE the cube, I get a message saying "There is a loop in
the schema. Please remove the extra joins".

If I delete the Geography links between either the Distributors
Dimension and the Country Dimension OR between the Customers Dimension
and the Country Dimension OR BOTH, I am then able to save the cube
without any problems.

Please help. Please let me know if I need to add more information for
you to be able to help me solve the problem.

Thanks

Without the specifics of your relational design it is difficult to make a
specific recommendation, but I would recommend that you take each dimension
and create a view which represents the a star schema format (fully
denormalized) for that dimension -- then load Analysis Services from the
view. This has several advantages. First, it is always a good best practice,
because it separates your physical relational design from your logical star
schema. Second, it allows you to fully eliminate loops and other schema
issues because in your case you clearly have a strong linkage between
dimensions -- something which is typically discouraged (but expected in your
design).
--
Dave Wickert [MS]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI Practices Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


"Learner" <wantnospam (AT) email (DOT) com> wrote

Dave,

Appreciate your reply. Thanks.

I am listing down some more details. I'll be very grateful if you can
help me solve the problem.

The DDLs for my underlying tables is given below along with some
IMPORTANT comments (just before the fact tables' DDL):

CREATE TABLE [Geography] (
[RedionCode] [int],
[RegionName] [nvarchar] (35),
[StateCode] [int],
[StateName] [nvarchar] (35),
[CityCode] [int],
[CityName] [nvarchar] (35)
)


Comment:Each Distributor has a type and a sub-type
CREATE TABLE tblDistributors (
[DistribCode] [nvarchar] (20) ,
[DistribName] [nvarchar] (50),
[DistribTypeCode] [int] ,
[DistribTypeName] [nvarchar] (50) ,
[DistribSubTypeCode] [int] ,
[DistrSubTypeName] [nvarchar] (50) ,
[DistrRegionCode] [int] ,
[DistrStateCode] [int] ,
[DistrCityCode] [int] ,
)


Comment:Each Customer also has a type and a sub-type
CREATE TABLE tblCustomers(
[CustCode] [int] (10) ,
[CustName] [nvarchar] (50) ,
[CustTypeCode] [int] ,
[CustTypeName] [nvarchar] (50) ,
[CustSubTypeCode] [int] ,
[CustSubTypeName] [nvarchar] (50) ,
[CustRegionCode] [int] ,
[CustStateCode] [int] ,
[CustCityCode] [int] ,
)


BTW, I also have Time and Products Dimensions and the underlying tables
for them but am skipping the details of these...

Here's my fact table: Important to note is the business logic that sales
are either only to a Distributor or to a Customer BUT via a Distributor
i.e. Direct sales to Customers do not happen in the scenario for which I
am trying to create my data mart and thus the cube. So my fact table
will have records in which the CustomerCode will be NULL... I don't know
what problems (if any) will thus make me run into when linking...

CREATE TABLE [SalesFact] (
[ProdCode] [nvarchar] (35),
[SaleDate] [datetime],
[TotalSaleAmt] [money]
[DistributorCode] [nvarchar] (20),
[CustomerCode] [int] (10) NULL
)

Sorry for the rather lengthy mail but I hope the above details explain
the missing information.

ALL of my dimensions are Star schema based (including the "derived"
one's i.e. Distributor Types and Customer Types)

Please guide how I should proceed and what should I do differently in
order to solve the problem as well as follow a better design approach.

Many thanks in advance.

A couple of points:
1) I would recommend that you use surrogate keys rather than the keys you
have right now. They will work, but don't allow you to merge different
datasources over time; nor will they allow you to implement slowly changing
dimensions. Typically I recommend integer IDENTITY fields from SQL Server.
2) Since you are using the Geography table to do *name* lookups for city and
state, I would create views that hide the join but then expose the names --
I don't see a Geography dimension, but geography levels in the Distributor
and Customer dimensions.
4) Your fact table seems to indicate that you have 4 dimensions: product,
time, distributor, customer. My guess is that you want your geography as a
levels in the distributor and customer dimensions, so you have something
like:

Distributor -> All; Distrib-Region; Distrib-State; Distrib-City; Distributor
For each distributor have two member properties: Type and Subtype.
Then build a virtual dimension with 3 levels called Distributor Type -> All;
Distrib-Type; Distrib-Subtype

Customer -> All; Cust-Region; Cust-State; Cust-City; Customer
For each customer have two member properties: Type and Subtype
Then build a virtual dimension with 3 levels called Customer Type -> All;
Cust-Type; Cust-Subtype

This allows you to do independently analyze the various "types" and the
geographies.

Hope that helps.
--
Dave Wickert [MS]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI Practices Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


"Learner" <wantnospam (AT) email (DOT) com> wrote

Thanks Dave :-)

A couple of questions and comments:

1. I'll try the surrogate key idea....

2. Maybe I forgot to mention but there certainly is a Geography
dimension which I want. Given this should I STILL follow your suggestion
below i.e.
Is the above the solution to the problem which I reported in my original
post? Your reply to this particular point is especially requested.

Any other suggestion regarding the design of my underlying tables?
Kindly advice.


3. Regarding my dimensions:
You are right about some i.e. Products, Time, Distributor, and Customer.
However in addition I also have a) Geogrpahy b) Distributor Type/Subtype
and c)Customer Type/Subtype Dimensions

In the mean while, I'll go ahead and try with your suggestions.

Thanks and waiting for your reply.