There's a couple of ways to do this. The way I use the most is to union the datasets;

SELECT Product, SUM(TotalSales) as TotalSales, null as OpenOrders

FROM ProductSales

GROUP BY Product

UNION ALL

SELECT Product, null as TotalSales, SUM(OpenOrders) as OpenOrders

FROM ProductOpenOrders

GROUP BY Product

Another method would be to join from a consolidated product table to the sales and open orders i.e.

SELECT p.Product, s.TotalSales as TotalSales, o.OpenOrders

FROM Products p

LEFT JOIN (SELECT Product, SUM(TotalSales) as TotalSales from ProductSales Group By Product) s on p.Product = s.Product

LEFT JOIN (SELECT Product, SUM(OpenOrders) as OpenOrders from ProductOpenOrders Group By Product) o on p.Product = o.Product

Without understanding exactly what you're doing and the structure, you can definitely use the UNION approach to bring in the row level info and manipulate in Tableau. Whether or not it would make sense, I think only you can answer that once you jump into the data.

I think you'd be looking at something like;

SELECT Product, SaleID, OrderNumber, SaleAmount, 'Closed' as OrderStatus

FROM Sales

UNION ALL

SELECT Product, null as SaleID, OrderNumber, SaleAmount, 'Open' as OrderStatus

FROM OpenOrders

null as is the best in my experience.

Create a view, do it once and you'll never have to worry about it again.