How to build a business user friendly framework manager package for query studio

Check list of all posts

While star scheme is mostly defined for report, it may not be the best way to expose FM for business users to use in query studio. This document presents a model for business user. It would be perfect if model gives business all subject areas. Behind the scene, it uses multiple fact tables and stitches them together to present a comprehensive view.

Business user friendly new look other than star scheme

Please see attached screen shot below, the traditional star scheme


This model is difficult to use to get a comprehensive data view for data for CRM, Sales and other area.
The following screenshot gives business another view other than simple star scheme

This model above is much easier to use, however, for no conformed dimension, it could be confused. To help business, all dimensions associated with a fact are given by tooltips

Use Tooltip

To get this tooltips, you can use following SQL script to get all associated dimensions for each fact


--1. Save mode data as xml data type in database

GO
--1.1. create table to hold xml
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FM]') AND type in (N'U'))
DROP TABLE [dbo].[FM]
GO
CREATE TABLE [dbo].[FM](
[model] [xml] NULL
)
GO
--1.2. Import file to table
insert into [FM]
SELECT CAST(x AS XML)
FROM OPENROWSET( BULK 'D:\model.xml', SINGLE_BLOB) AS T(x)



--2. Query Meta data as requested
--2.1. extact fact and Dim to table
declare @x xml
select  @x = model from FM
declare @idoc int
execute sp_xml_preparedocument @idoc output, @x
SELECT * into #tmp
FROM  OPENXML (@idoc, 'project/namespace/namespace/namespace/folder/namespace/shortcut',2)
WITH (
Fact  varchar(50) '../name',
Dim  varchar(50) 'name'

)
execute sp_xml_removedocument @idoc

GO
--2.2. get all associated dimesions for each fact table with comma delimited
drop table #tmp1
select * into #tmp1 from #tmp where dim not like '%FILTER%' order by fact, dim

select fact, 'Dimensions: ' +
REPLACE (
stuff(
(
select ', '+ [dim] from #tmp1 where fact = t.fact for XML path('')
),1,1,''), fact+',','')
from (select distinct fact from #tmp1  )t