I build a local cube from a relation database. In the database there are 1:n relations.
Is there a way to handle 1:n relations?
I have a table LOGGEDFLAW and a table LOGGEDREASON with a 1:n relation between them. We create a select statement of these tables and as an result we get duplicate records of LOGGEDFLAW each time more than 1 record of LOGGEDREASON are associated to 1 record of LOGGEDFLAW - this is the standard result I get with an relational JOIN operation. Now I want to count the LOGGEDFLAWs without the duplicates generated by the 1:n relationship.
CREATE VIEW myView99
SELECT o.OrderId, od.Quantity
FROM Orders o INNER JOIN [Order Details] od
ON o.OrderId = od.OrderId
SELECT COUNT(DISTINCT OrderId), COUNT(*)
DROP VIEW myView99