none
query cross federated tables in different federation members

    Question

  • If I want to query customer order history based on custom zip code: 98052,  However, customer w/ 98052 zip code are in number of Federation members.  Is it even possible query directly ? if so, how

    Max

    Wednesday, August 22, 2012 11:41 PM

Answers

  • Hi Max,

    You should be able to achieve what you described using Fan-out Querying for Federations in SQL Azure. There is a very nice (2 part) article, written by Cihan Biyikoglu, about this technique:

    What is a fan-out query?

    Federations provide a model for partitioning parts of your schema over to multiple member databases for harnessing scalability of many nodes. However applications still need for querying all of the data across federation members. Fan-out is a technique for querying data in your federation, across many federation members. Fan-out queries are much like map/reduce in that it is formed in 2 parts;

    Member query is the piece that is sent over to all members involved in the query and
    Summary query is the query that is the post processing piece to allow condensing the results from the member query to desired final result-set.


    With fan-out queries the member query is always there but summary query may not be needed. For example if you are simply doing DML (we’ll have some examples like data pruning or reference data management etc) or DDL (we’ll look at schema deployment in detail below), fan-out would only have a member query but no summary query is needed. It is only when you need post processing, you need the summary query.

    You can find the article here: Part 1, Part 2.

    Hope this helps!


    Best Regards,
    Carlos Sardo

    Thursday, August 23, 2012 7:35 PM

All replies

  • Hi Max,

    You should be able to achieve what you described using Fan-out Querying for Federations in SQL Azure. There is a very nice (2 part) article, written by Cihan Biyikoglu, about this technique:

    What is a fan-out query?

    Federations provide a model for partitioning parts of your schema over to multiple member databases for harnessing scalability of many nodes. However applications still need for querying all of the data across federation members. Fan-out is a technique for querying data in your federation, across many federation members. Fan-out queries are much like map/reduce in that it is formed in 2 parts;

    Member query is the piece that is sent over to all members involved in the query and
    Summary query is the query that is the post processing piece to allow condensing the results from the member query to desired final result-set.


    With fan-out queries the member query is always there but summary query may not be needed. For example if you are simply doing DML (we’ll have some examples like data pruning or reference data management etc) or DDL (we’ll look at schema deployment in detail below), fan-out would only have a member query but no summary query is needed. It is only when you need post processing, you need the summary query.

    You can find the article here: Part 1, Part 2.

    Hope this helps!


    Best Regards,
    Carlos Sardo

    Thursday, August 23, 2012 7:35 PM
  • i could be wrong, but it seems only using fan-out query utility page (http://federationsutility-scus.cloudapp.net/) to get results from all members ? I need to do it pragmatically, mean in my code. I can't use this utility page.

    i also need do data aggregation like count, ave, max/min, like summary queries


    Max

    Thursday, August 23, 2012 9:40 PM
  • Hi Max,

    You can find the code in the article bellow (Part 1), therefore, you can do it programatically. The web utility tool is a proof-of-concept. 

    You can also perform data aggregation using summary queries. I strongly advise you reading the complete article!

    Hope this helps!


    Best Regards,
    Carlos Sardo

    • Marked as answer by Iric WenModerator Thursday, August 30, 2012 8:01 AM
    • Unmarked as answer by RedmondMax Monday, January 28, 2013 12:11 AM
    Friday, August 24, 2012 11:51 AM
  • thanks.. will try out and confirm

    Max

    Thursday, August 30, 2012 5:37 PM
  • Carlos, like I mentioned in my post that I do not want to use the utility page. It is application approach. I need query over SQL management studio. Likes look not possible.


    Max

    Monday, January 28, 2013 12:29 AM