#### Topic: Communication with measurement many to many

Good afternoon!
Faced the task which most likely demands not the obvious decision. Though I think it is spread enough.
There is  a structure:
The organization - Clinic - Separation
In separations there are specializations, communication many to many as in one separation can be some specializations, and one specialization can be in several separations.
Further it is even more interesting under specific specialization there are doctors, again communication many to many, i.e. some doctors can concern one specialization, and one doctor can be in different specializations and accordingly work in different separations.
In initial system there is one label (we name its sheaf) which connects  the doctor,  specializations,  separations.
In storage as I understand tables bridge for the communication organization between separations and specializations, and one more between specializations and doctors are necessary.
Let's consider for example the organization of communication of separation - specializations: if the table bridge to make with 2 fields  separations and  specialization if the facts will lie on specialization that we we do not understand on what to precisely separation they as some separations are anchored to specialization.
All facts under the total lie on doctors, i.e. there is a doctor, it rendered services, on such that the total how correctly to organize the data in the fact table, and communications to many that could be traced many that the fact on such that to the doctor has been made on the given specialization in the given separation, I can not understand.
To store in tables a bridge and the facts one more pointer on  from the table a sheaf, precisely to understand on what specialization and separation this fact but as then dimension tables and tables bridge should look.
Thanks big if help,  couples with this task.

#### Re: Communication with measurement many to many

The novel Logachyov;
At first sight communication many to many is not necessary to you.
The fact is rendering by the doctor of service.
And for this fact it is possible to define unambiguously both the doctor, and separation, and specialization.
Simply three different measurements.

#### Re: Communication with measurement many to many

s_ustinov wrote:

the Novel Logachyov;
At first sight communication many to many is not necessary to you.
The fact is rendering by the doctor of service.
And for this fact it is possible to define unambiguously both the doctor, and separation, and specialization.
Simply three different measurements.

Yes, such approach takes place to be - advantages -
Simplicity and convenience for specific tasks.
If (... We precisely do not know) are necessary  type:
How many doctors on a specific speciality are in such separation
Or "what doctors do not use some specializations",
Here it will be necessary sheaves, but not as a bridge and as the lawful facts:
(under the diploma, without a binding with hospital),
(the specific person accepts as such expert in such hospital)
I will repeat: additional  are necessary if they are necessary

#### Re: Communication with measurement many to many

Logachyov wrote:

Good afternoon!
Faced the task which most likely demands not the obvious decision....
All facts under the total lie on doctors, i.e. there is a doctor, it rendered services, on such that the total how correctly to organize the data in the fact table, and communications to many that could be traced many that the fact on such that to the doctor has been made on the given specialization in the given separation, I can not understand.
...

You need to be able in a business application or ETL to define type of service upon its rendering. That is record about service rendering should have following attributes:
The doctor
Service
Where it is rendered (in what separation, an office and )
To whom it is rendered
On service you can define specialization.
These are the facts, and the analytics cannot synthesize or invent them. That I can offer - you can try  the data in ETL process somehow.
As told earlier, the analytics can answer a question "as doctors on the specializations" work or "as services of doctors are connected to separations", recovery of the facts - not the analytics task.

#### Re: Communication with measurement many to many

Thanks, the colleague for answers, but and I will not understand what decision of the task.
I will make still pair input why decided to do communications so it because it is necessary to make hierarchy of a type:
The organization - clinic - separation - specialization - the doctor
For the organization of a filtration of parameters in reports etc.
The moment with the organization of 3 separate measurements is worked and implemented already, a question in that to connect these measurements for the organization of a normal filtration, selected clinic were filtered, separations, selected separation specializations etc. were filtered
Not absolutely understood about sheaves but not bridge and type type the lawful facts, it as.
As I see in the facts there is a service, the doctor and a sign in what separation is rendered service . .  from the sheaf table in initial system but how further to anchor the facts to measurements of doctors, on two keys it turns out?? The doctor and  a sheaf sign??

#### Re: Communication with measurement many to many

The novel Logachyov;
Complexities at this stage a minimum.
Measurements:  (the educational institution can be lowered),  (medical institution), Specialization, the Expert, Date
The facts: formation, the list of staff, services
I.e.
The expert got education on Specialization in
The expert works on the Speciality in  under the Schedule (Date)
The expert rendered services in the Speciality in  in Date
Communication m2m can be made between formation and the list of staff and-or rendering of services
The hierarchy "the organization - clinic - separation - specialization - the doctor" is a little decided.
Better 3 measurements "the organization - clinic - separation", "specialization", "doctor" differently 1 and the same Expert in the presence of several places of operation will be actually different people (key). The same and about the Speciality.

#### Re: Communication with measurement many to many

Logachyov wrote:

I will make still pair input why decided to do communications so it because it is necessary to make hierarchy of a type:
The organization - clinic - separation - specialization - the doctor
For the organization of a filtration of parameters in reports etc.
The moment with the organization of 3 separate measurements is worked and implemented already, a question in that to connect these measurements for the organization of a normal filtration, selected clinic were filtered, separations, selected separation specializations etc. were filtered
...

As earlier told, your hierarchy leads to problems. It is better to break it into 3 measurements.
At you a question not on design, and on the environment of display of reports. That at a separation choice - in the list of specializations the active disappeared not.
How to solve (on your example) - I did certain dummy group of measures - which 1 for all separations, and anchored it through M2M to specializations. Through measurements of sheaves (separation-code of the order and the order-specialization code) approach M2M and in Reporting Services - requested that is selected in hierarchies (separation). Further - did request to a measure dummy sheaves that it deduced nonzero lines. Of result of the answer - formed the list of specializations. It worked in SSRS as there there is a mechanism of the bound parameters and it is possible difficult  to update the list of values of dependent parameter.

#### Re: Communication with measurement many to many

Logachyov wrote:

not absolutely understood about sheaves but not bridge and type type the lawful facts, it as.

Shortly:
The bridge is an auxiliary element, a variant , it it is possible
The fact - the main "" round which spins  the task.
Was specific on a subject:
If at you is the schedule of doctors and there is a task
Under the analysis of employment of doctors - that will be useful for having  as the fact.
If such 4 it is not necessary and at you only basis  -
That "" can be made as auxiliary a bridge
it only from the facts "visit to the doctor"
Similarly "doctors and specialities" - if are the independent list
Also there are tasks under the analysis  the list (whether "doctors work on
The ") that it is faster than a speciality (holes) the fact.
If there are no tasks and there is no list - that it can be a bridge.
---------
Besides I support all previous  which
I.e. the possible decision (without ):
FactAppointment - DimDoctor, DimService, DimSpeciality, DimLocation, DimDate
If it is necessary:
FactSpecialization - DimDoctor, DimSpeciality
FactSchedule - DimDoctor, DimLocation, DimDate, DimSpeciality
Service and Speciality can be one , but
It seems to me to divide is better, it depends from
Visit, , specializations.
That at you it is written down in visit: "visit to the oculist" or
"Visit to the oculist: (1) surveys of an eye bottom, (2) selection of points (3) selection of lenses"?
DimLocation - -  an office-hospital-organization...
Either directly here or separately it is possible to fasten geography...

#### Re: Communication with measurement many to many

Logachyov wrote:

the moment with the organization of 3 separate measurements is worked and implemented already, a question in that to connect these measurements for the organization of a normal filtration, selected clinic were filtered, separations, selected separation specializations etc.

were filtered
...Question an edge: to you are necessary
() only the data on real visits?
() the data on visits and the analysis of idle times of doctors (the doctor was on duty but accepted anybody)
(It as normal JOIN  LEFT JOIN)
If () all sheaves already are available in main FaktVizit,
I.e. the fact  is a bridge! Filter through the fact on health!
(Well if only not 100  records)
If () that the sheaf from an independent source is necessary on any.
Personally I would name  FaktRaspisanie are is not simple 2 a bridge;
And as  the 4-parties a sheaf the Doctor-speciality-office-date

#### Re: Communication with measurement many to many

ShIgor wrote:

hierarchy "the organization - clinic - separation - specialization - the doctor" is a little decided.
Better 3 measurements "the organization - clinic - separation", "specialization", "doctor" differently 1 and the same Expert in the presence of several places of operation will be actually different people (key). The same and about the Speciality.

+1
That is it is possible to make the separate fact table "rendered services"
And each line of this fact table at you consists of links to three measurements
- Separation
- Specialization
- The doctor
This fact will mean that (hierarchical dimension "the organization - clinic - separation") the given doctor renders services in the given specialization on the given separation. If the same doctor renders services in other specialization on the same separation - is added one more line, where separation and the doctor the same, and specialization another.