[ARCHIVED] schema.json constraints

[julian_ebeli]

Hi

We're setting up an Azure mirror of our Canvas Data.

We're using the schema.json to help build out the required tables, indexes and foreign key constraints.

My question is about the relationship between a dim and a fact table.

Why does pseudonym_fact(pseudonym_id) not have a dimension attribute (like user_id) when the description says its a foreign key? I don't know weather to believe the description or not?

The warehouse architecture requires it, I would have thought.

                  "pseudonym_fact":  {
                                          "dw_type":  "fact",
                                          "columns":  [
                                                          {
                                                              "type":  "bigint",
                                                              "description":  "Foreign key to pseudonym dimension table",
                                                              "name":  "pseudonym_id"
                                                          },
                                                          {
                                                              "type":  "bigint",
                                                              "description":  "Foreign key to user associated with this pseudonym",
                                                              "name":  "user_id",
                                                              "dimension":  {
                                                                                "name":  "user",
                                                                                "id":  "id",
                                                                                "role":  "user"
                                                                            }
                                                          }, ...

[robotcars]

The JSON file is autogenerated as is the documentation, so there are some issues. The dimension exists. I use a modified version of  @James  canvas-data repo to generate my MSSQL schema. There are quite a bit of 'overwrites' and corrections his code makes to generate a proper file. Assuming Azure is consuming the JSON file to generate your warehouse, you might need to 'patch' the latest.json before importing.

canvancement/canvas-data/php at master · jamesjonesmath/canvancement · GitHub 

[julian_ebeli]

Hi Robert

Thanks for the link

I can see that James is definitely dealing with the foreign key between the dim and fact tables that are not explicit in the schema.json file.

I can't understand why the schema.json file doesn't represent this. Is it implicit in the data warehouse architecture?

[robotcars]

The docs are machine generated from/for the api and schema, just seems fail at certain points.

All the star schema/join conventions seem to work fine despite the docs.

[julian_ebeli]

Hi Robert not sure what you mean with

All the star schema/join conventions seem to work fine despite the docs

"course_dim"    "wiki_id"    "Foreign key to the wiki_dim table."

The course_dim table has a field 'wiki_id' described as a foreign key.

There is no 'dimension' attribute for this entry so I should ignore the description?

How should I interpret it?

Thanks

[James]

 @julian_ebeli ,

I'm not sure what you mean when you say there is no dimension attribute for this entry.

The course_dim table has a wiki_id column and the wiki_dim table has an id column and you can join the two on course_dim.wiki_id = wiki_dim.id.

In all but a couple of tables, if table xxx_dim or xxx_fact has a column called yyy_id, then table yyy_dim will have a column called id that can be joined with yyy_id.

[julian_ebeli]

Hi James

Thanks for your reply, I have been following the formula !

I'm now just venting !

When I say there is no 'dimension' attribute, I mean in the schema.json file.

wiki_id in the course_dim table has no 'dimension' attribute

I think it should be there!

The formula can determine a lot of foreign keys, but you still have to ask the data if the string 'foreign key' is in the description!

What happens when it says: '(Not implemented) Foreign key to the user agent dimension table.'!

When the key is' external_tool_id' it needs to be pointed at external_tool_activation_dim.

This requires manual intervention! Formula doesn't work

In addition even if there is a 'dimension' attribute it references the key name of the table in the schema not its actual name.

So you get 'account' instead of 'account_dim'

These are all gotcha's that are causing me a lot of work I wouldn't have to do if the schema.json file was coherent!

Their 'schema.json' file should be called 'a schema_like_doc.json'!

Canvas should fix it.

[James]

Thanks for the clarification on what you meant by dimension.

I agree that there are issues with the schema.json file. My biggest issue was the various ways they listed the enumerated fields, but that's probably because I never even looked at the other attributes like dimension. That could be my ignorance since this was the first exposure to dimension and fact tables and wasn't (still am not) completely sure what the difference is. A foreign key sounds like it belongs in the fact table rather than the dimension table and that's where you find most of them. However, there is no course_fact table so it had to go into the course_dim table.