Virtual graph SMS Mapping for multiple entities in a de-normalized table

Support
1

Hi

I have one table that looks like this, which has multiple platforms, multiple business functions and headcounts for each, and a business function supports a platform:
platform_taxonomy_level_4 = platform
profit_center_level_4 = business function

platform_taxonomy_level_4 profit_center_level_4 total_headcount addressable_headcount
PL1 BF1 100 5
PL1 BF2 200 6
PL2 BF1 100 7
PL2 BF3 300 9

I have 2 classes Platform and BusinessFunction. I want to be able to query headcount for Platform seprately, Business Function separately and Platform and Business Function together. What is the best way to do the SMS mapping for this? My mapping file looks like this:

prefix mscore: <http://ontologies.ms.com/mscore/>
prefix msrpc: <http://ontologies.ms.com/o/msrpc/>
prefix msF2B: <http://ontologies.ms.com/o/msF2B#>
PREFIX skos: <http://www.w3.org/2004/02/skos/core#>

# A basic SMS2 mapping.
MAPPING msF2B:Platform
FROM SQL {
    select platform_taxonomy_level_4, sum(total_headcount) as total_headcount, sum(addressable_headcount) as addressable_headcount
    from dbo.f2b_resource_extract
    group by  platform_taxonomy_level_4
}
TO {
    ?platform a msF2B:Platform ;
           msF2B:platformName ?platform_taxonomy_level_4 ;
           msF2B:totalHeadcount ?total_headcount ;
           msF2B:addressableHeadcount ?addressable_headcount .
}
WHERE {
    BIND(template("http://ontologies.ms.com/o/msF2B/PL_{platform_taxonomy_level_4}") AS ?platform)
}

;

MAPPING msF2B:BusinessFunction
FROM SQL {
    select profit_center_level_4, sum(total_headcount) as total_headcount, sum(addressable_headcount) as addressable_headcount
    from dbo.f2b_resource_extract
    group by profit_center_level_4
}
TO {
    ?businessFunction a msF2B:BusinessFunction ;
                msF2B:businessFunctionName ?profit_center_level_4 ;
                msF2B:totalHeadcount ?total_headcount ;
                msF2B:addressableHeadcount ?addressable_headcount .
}
WHERE {
    BIND(template("http://ontologies.ms.com/o/msF2B/BF_{profit_center_level_4}") AS ?businessFunction)
}

;

MAPPING :PlatformBusinessFunctionMapping
FROM SQL {
    select profit_center_level_4, platform_taxonomy_level_4, sum(total_headcount) as total_headcount, sum(addressable_headcount) as addressable_headcount
    from dbo.f2b_resource_extract
    group by profit_center_level_4, platform_taxonomy_level_4
}
TO {
    ?profit_center_level_4 mscore:supports ?platform_taxonomy_level_4 .
                # msF2B:businessFunctionName ?profit_center_level_4 .
                # msF2B:totalHeadcount ?total_headcount .
                # msF2B:addressableHeadcount ?addressable_headcount .
}
WHERE {
    BIND(template("http://ontologies.ms.com/o/msF2B/BF_{profit_center_level_4}") AS ?businessFunction)
    BIND(template("http://ontologies.ms.com/o/msF2B/PL_{platform_taxonomy_level_4}") AS ?platform)
}

This gives me a only 1 record when i run this SPARQL query when it is supposed to give all records:

PREFIX mscore: <http://ontologies.ms.com/mscore/>
prefix msrpc: <http://ontologies.ms.com/o/msrpc/>
prefix msF2B: <http://ontologies.ms.com/o/msF2B#>
PREFIX skos: <http://www.w3.org/2004/02/skos/core#>

SELECT ?platformName ?businessFunctionName ?totalHeadcount
WHERE {
    GRAPH <virtual://sybase> {
        ?platform a msF2B:Platform ;
                msF2B:platformName ?platformName ;
                msF2B:totalHeadcount ?totalHeadcount .
        ?businessFunction a msF2B:BusinessFunction ;
                msF2B:businessFunctionName ?businessFunctionName ;
                msF2B:totalHeadcount ?totalHeadcount .
        ?businessFunctionName mscore:supports ?platformName .
    }
}

How can I achieve this for the data which is one big de-normalized table?

2

I'm confused about the information you've given. You "have one table that looks like this", but it doesn't match what's in your mapping file.

"But this does not work. Because my mapping table is actually the main table" What doesn't work. What do you mean by "main table" and why is that a problem?

It would probably help if you were more explicit about what data you have and what you're looking to do.

3

I have tried to make it more clear, please let me know if that helps

4

The mapping is clearly not correct for a scenario where there is a many to many relationship and headcount is split at both levels (platform and business function). I'm not sure how to do this mapping in the SMS file when i need to get headcounts at platform and business function individually and together.

5

I was able to resolve this issue, the final mapping needed updating:

MAPPING :PlatformBusinessFunctionMapping
FROM SQL {
    select profit_center_level_4, platform_taxonomy_level_4, sum(total_headcount) as total_headcount, sum(addressable_headcount) as addressable_headcount
    from dbo.f2b_resource_extract
    group by profit_center_level_4, platform_taxonomy_level_4
}
TO {
    # ?profit_center_level_4 mscore:supports ?platform_taxonomy_level_4 .
    ?businessFunction mscore:support ?platform .
 
    ?mapping a :PlatformBusinessMapping ;
             :hasBusinessFunction ?businessFunction ;
             :hasPlatform ?platform ;
             msF2B:totalHeadcount ?total_headcount_int .
}
WHERE {
    BIND(template("http://support.stardog.com/stephen/{profit_center_level_4}_TO_{platform_taxonomy_level_4}") as ?mapping)
    BIND(template("http://ontologies.ms.com/o/msF2B/BF_{profit_center_level_4}") AS ?businessFunction)
    BIND(template("http://ontologies.ms.com/o/msF2B/PL_{platform_taxonomy_level_4}") AS ?platform)
    BIND(xsd:integer(?TOTAL_HEADCOUNT) AS ?total_headcount_int)
}
6

This topic was automatically closed 14 days after the last reply. New replies are no longer allowed.