none
USERELATIONSHIP + SUMX - MODELO TABULAR RRS feed

  • Pregunta

  • MODELO TABULAR


    Hola a todos buenas tardes,  quiero saber como puedo hacer lo siguiente:

    Relacioné mi tabla DimClients con 3 campos de la tabla FactDeals (Buyer, Seller, Swap) de tal manera que el campo Buyer quedó como relación principal, de tal forma que cuando compilo mi cubo y desde Excel arrastro al cliente a la pivot + el campo suma comision (ChargedFee) solo me muestra los clientes que corresponden con el campo Buyer. 

    No puedo ver los clientes que se encuentran en el campo Seller ni los clientes del campo SWAP,

    ¿cómo puedo hacer eso?

    Lo que se ve en la imagen (REPORTE DE CUBOS) también deseo incluir a VECTOR_FONDOS (lo pinté más tenue)

    muchas gracias por sus aportaciones




    • Editado MANDRAKE2001 miércoles, 13 de enero de 2016 20:35
    martes, 12 de enero de 2016 22:14

Respuestas

Todas las respuestas

  • puedes hacer una suma, pero tienes que usar la clausula que tu  mismo pones en el titulo del post, userelationship

    https://www.sqlbi.com/articles/userelationship-in-calculated-columns/

    este es el link al  enlace de marco russo donde lo explica


    Comparte lo que sepas, aprende lo que no sepas (FGG)
    portalSQL
    El rincón del DBA

    miércoles, 13 de enero de 2016 16:35
    Moderador
  • el USERELATIONSHIP lo utilizo en el momento de hacer la suma y es correcta,

    lo que no se es cómo utilizar el USERELATIONSHIP

    para que al momento de jalar el cliente de la dimensión DimClients desde Excel

    me jale la lista de todos los clientes que se encuentran en las 3 columnas.

    Solo me está jalando los clientes del BUYER que son los que tienen la relación principal.

    soy novato, y en la liga que propones no viene un ejemplo similar, te digo que la suma si la hago correcta.

    miércoles, 13 de enero de 2016 17:05
  • eso es el concepto de role playing que si no estoy equivocado no lo tiene, puedes importar la tabla 3 veces y tener los 3 roles, de otra forma cuando excel muestre los datos los mostrará por la relaccion activa.

    Sin embargo si haces un campo que sea algo a´si como compra total, incluyendo los 3 sumX, quizá arrastrándolo consigas lo que buscas. (la verdad es que es difícil saber que buscas en realidad)


    Comparte lo que sepas, aprende lo que no sepas (FGG)
    portalSQL
    El rincón del DBA

    miércoles, 13 de enero de 2016 17:20
    Moderador
  • Te doy detalle:

    Nuestra empresa es intermediaria para que los Bancos de México operen entre si, por cada operación que se haga, nosotros nos quedamos con una comisión.

    Entonces, en una operación un cliente Vende y otro Compra (un Monto de un X Producto)

    Cada Banco (cliente) tiene un contacto (corredor).

    Hay veces que se necesita un Banco de Trabajo (BT) es decir, el comprador y vendedor ya no tienen mas "linea" y necesitan pasar por un banco extra, de tal forma que se hacen dos operaciones (marqué en rojo el ejemplo)

    VECTOR Y SANTANDER quieren operar entre ellos pero ya no pueden en directo por lo que hacen lo siquiente:

    VECTOR  - BANAMEX    (1a operación) Vector compra
    BANAMEX - SANTANDER  (2a operación) Santander vende
    
    y Banamex vende y compra sin cobrarle comisión
    


    Ahora lo que quiero es hacer lo siguiente bajo la siguiente tabla.

    Calcular la sumatoria de cada cliente, ya sea que esté comprando o vendiendo, en el ejemplo superior he puesto a SANTANDER, por lo que su comisión ttl es de $10,500.00

    Lo quiero granulado por producto y contacto también.

    Cuando desde Excel arrastro la Dimensión cliente y la medida SumaComision

    quiero que me aparezcan todos los clientes que al menos participaron en una operación hayan o no cobrado comisión.

    Lamentablemente tengo como relación principal al CLIENTE_COMPRADOR y alli es donde tengo el problema que te cuento al principio.

    Mira, para mejorar la ayuda que me estás haciendo, he creado un script que genera un DW muy simple pero muy util para poder resolver ésta bronca.

    el script que te dejo crea la siguiente estructura.

    Espero que puedas correr el script para que te sea más fácil ayudarme, tu dime

    USE master;
    GO
    -- =======================================================================================================
    -- AUTOR:			MANUEL OMAR OLGUÍN HDEZ
    -- FECHA:			2016 - 01 13
    -- VERSIÓN SCRIPT:	1.0
    -- REQUERIMIENTO:	ASESORÍA
    -- DESCRIPCIÓN:		CREA UNA BASE DE DATOS DE TIPO DATA WAREHOUSE PARA UTILIZARLA COMO EJEMPLO Y
    --					SOLICITAR ASESORÍA VÍA FOROS
    --
    -- NOTA:			CÓDIGO CREADO EN SQL SERVER 2014 DEVELOPER EDITION
    -- =======================================================================================================
    
    
    
    ------------------------------------------------------------------------------- CREA BASE DE DATOS
    CREATE DATABASE DW_temp;
    GO
    
    USE DW_temp;
    GO
    
    ------------------------------------------------------------------------------ CREA LA ESTRUCTURA 
    CREATE TABLE DimClientes
      (
         IdCliente		TINYINT			NOT NULL,
    	 NombreCliente	VARCHAR (30)	NOT NULL,
    	 CONSTRAINT		PK_DimClientes	PRIMARY KEY (IdCliente),
      );
    
       
       
    CREATE TABLE DimContactos
     (
      IdContacto		TINYINT	NOT NULL,
      NombreContacto	VARCHAR (30),
      CONSTRAINT		PK_DimContactos	PRIMARY KEY (IdContacto),
     )
    
    
     
    CREATE TABLE DimProductos
     (
      IdProducto		TINYINT	NOT NULL,
      NombreProducto	VARCHAR (30),
      CONSTRAINT		PK_DimProductos	PRIMARY KEY (IdProducto),
     )
    
    
    
    CREATE TABLE FactDeals
     (
       IdDeal					INT						NOT NULL,
       IdCliente_Comprador		TINYINT					NOT NULL,
       IdCliente_Vendedor		TINYINT					NOT NULL,
       IdCliente_BT				TINYINT					NULL,
       Monto					INT						NOT NULL,
       Tasa						DECIMAL (18,8)			NOT NULL,
       IdProducto				TINYINT					NOT NULL,
       Comision_Comprador		DECIMAL (18,8)			NOT NULL,
       Comision_Vendedor		DECIMAL (18,8)			NOT NULL,
       IdContacto_Comprador		TINYINT					NOT NULL,
       IdContacto_Vendedor		TINYINT					NOT NULL,
       CONSTRAINT				PK_FactDeals			PRIMARY KEY (IdDeal),
       CONSTRAINT				FK_Cliente_Comprador	FOREIGN KEY (IdCliente_Comprador)	REFERENCES DimClientes (IdCliente),
       CONSTRAINT				FK_Cliente_Vendedor		FOREIGN KEY (IdCliente_Vendedor)	REFERENCES DimClientes (IdCliente),
       CONSTRAINT				FK_Cliente_BT			FOREIGN KEY (IdCliente_BT)			REFERENCES DimClientes (IdCliente),
       CONSTRAINT				FK_Producto				FOREIGN KEY (IdProducto)			REFERENCES DimProductos (IdProducto),
       CONSTRAINT				FK_IdContacto_Comprador	FOREIGN KEY (IdContacto_Comprador)	REFERENCES DimContactos (IdContacto),
       CONSTRAINT				FK_IdContacto_Vendedor	FOREIGN KEY (IdContacto_Vendedor)	REFERENCES DimContactos (IdContacto),
    
      );
      GO
    
    
    
    
    
     -- CREAR VISTA PARA VISUALIZAR LOS HECHOS
     CREATE VIEW V_Deals
     AS
     SELECT		A.IdDeal, B.NombreCliente AS Cliente_Comprador, C.NombreCliente AS Cliente_Vendedor
    			,F.NombreCliente AS Cliente_BT, D.NombreContacto as Contacto_Comprador, E.NombreContacto as Contacto_Vendedor
    			,A.Comision_Comprador, A.Comision_Vendedor
    			,G.NombreProducto
     FROM		FactDeals		AS A
     INNER JOIN	DimClientes		AS B ON A.IdCliente_Comprador = B.IdCliente
     INNER JOIN	DimClientes		AS C ON A.IdCliente_Vendedor = C.IdCliente
     INNER JOIN	DimContactos	AS D ON A.IdContacto_Comprador = D.IdContacto
     INNER JOIN DimContactos	AS E ON A.IdContacto_Vendedor = E.IdContacto
     LEFT JOIN	DimClientes		AS F ON A.IdCliente_BT = F.IdCliente
     INNER JOIN DimProductos	AS G ON A.IdProducto = G.IdProducto
     GO
    
     --------------------------------------------------------------------- LLENA LOS DATOS
    
     INSERT INTO DimClientes VALUES
     (1,'HSBC'),
     (2,'SANTANDER'),
     (3,'BANCOMER'),
     (4,'JP MORGAN'),
     (5,'BARCLAYS'),
     (6,'VECTOR'),
     (7,'BANAMEX');
    
    
     INSERT INTO DimContactos VALUES
     (1,'Roberto Garza'),
     (2,'Daniel Barrios'),
     (3,'Juan Huerta'),
     (4,'Luis Estrada'),
     (5,'Gerardo Vargas'),
     (6,'Leonardo Moreno');
    
    
      INSERT INTO DimProductos VALUES
      (1,'FIJA'),
      (2,'CETE'),
      (3,'UDIBONO'),
      (4,'BANCARIO'),
      (5,'CORPORATIVO'),
      (6,'IPABONO');
    
    
     INSERT INTO FactDeals VALUES
     (1,5,2,null, 10000,6.25,1,3500,4500,4,5),
     (2,5,1,null, 20000,6.26,1,4500,4000,4,6),
     (3,2,3,null, 10000,6.35,2,3000,2805,5,1),
     (4,4,6,1, 100000,16.788,3,3800,8005,2,3),
     (5,4,1,5, 100000,16.788,4,3800,8005,2,6),
     (6,6,2,7,50000,18.39,6,5500,3000,3,5),
     (7,1,4,NULL,45000,17.39,5,300,400,6,2);
    
    
     ------------------------------------------------------------------------- VER LOS REGISTROS INSERTADOS
    
     SELECT * FROM V_Deals ORDER BY IdDeal
    
    
    

    MUCHAS GRACIAS POR TU APOYO Y VALIOSO TIEMPO

    miércoles, 13 de enero de 2016 20:30
  • Es justo lo que te decía, en lugar de tener solo dim cliente, usa 3 veces la dimension, importa tu tabla tres veces

    lo que he hecho e importarla y ponerle el nombre del rol que juega, el diagrama queda tal que así

    y el resultado creo que es el que tu esperas queda así

    he modificado la tabla dinámica para ponerla en vista clásica y quitado los subtotales, pero vamos el resultado se parece bastante a lo que buscas


    Comparte lo que sepas, aprende lo que no sepas (FGG)
    portalSQL
    El rincón del DBA

    jueves, 14 de enero de 2016 12:24
    Moderador
  • Hola buenos días =)

    Gracias que nada por tu asesoría, vale mil!,

    Ahora, tengo que disculparme por que no he sido muy claro,

    Mira, he creado un cuadro en Excel para mostrarte el resultado que en realidad espero:

    Es una sola columna de clientes, y cada cliente tiene la suma de la comisión cobrada ya sea que haya cobrado como comprador o como vendedor. (la suma de cualquiera de esos dos campos )

    Ve como SANTANDER a veces actua como como comprador o a veces como vendedor y al final  toma su comisión y calcula la sumatoria. Así mismo SANTANDER Participó 3 veces por lo que en Participaciones le pongo 3.

    Algo importante que quisiera recalcar es que BANAMEX no tiene comisiones ni contactos relacionados, es solo un Banco de trabajo, por lo que la sumatoria debe ser cero o blanco y no deben aparecer contactos debajo de el. PERO SI Participaciones, porque está una vez como banco de trabajo.

    Donde veo que no fui muy claro es que quiero una sola lista de todos los clientes que se encuentran en las 3 columnas.  =)  sorry.

    Mi problema de inicio, sin tomar en cuenta tu consejo del role playing, es que al arrastrar desde EXCEL al cliente, por default solo se trae la lista de clientes que aparecen en el campo comprador, porque tiene un relationship principal con ese campo.


    Cómo ves? es difícil? 



    jueves, 14 de enero de 2016 13:59
  • he conseguido la sumatoria de comisión con el siguiente calculo:

    Comision:=CALCULATE(
         CALCULATE( SUMX(FactDeals,FactDeals[Comision_Comprador]),USERELATIONSHIP(FactDeals[IdCliente_Comprador],DimClientes[IdCliente])),
        USERELATIONSHIP(FactDeals[IdContacto_Comprador],DimContactos[IdContacto])
     )
                              +
        CALCULATE (
         CALCULATE( SUMX(FactDeals,FactDeals[Comision_Vendedor]),USERELATIONSHIP(FactDeals[IdCliente_Vendedor],DimClientes[IdCliente])),
         USERELATIONSHIP(FactDeals[IdContacto_Vendedor],DimContactos[IdContacto])
    )

    Con eso, se desglosan perfectamente hasta sus contactos,

    ahora me hace falta que cuando le ponga Participaciones, agregue al cliente BANAMEX con 1 participación y sin comision, eso no lo puedo conseguir

    jueves, 14 de enero de 2016 14:12
  • en lugar del sumx usa distinccount sobre el campo idcliente probablemente te funcione

    Comparte lo que sepas, aprende lo que no sepas (FGG)
    portalSQL
    El rincón del DBA

    jueves, 14 de enero de 2016 15:12
    Moderador
  •  Distinct count siempre me va a dar 1

    necesito saber el total de operaciones en las que participó.

    Bueno, gracias, voy a seguir buscando


    jueves, 14 de enero de 2016 15:55
  • distinct count del id de operación (ideal) no te dará uno

    Comparte lo que sepas, aprende lo que no sepas (FGG)
    portalSQL
    El rincón del DBA

    jueves, 14 de enero de 2016 16:50
    Moderador