In the past couple months, this scenario has occurred twice. So thought I'd share a quick remedy ~ and save for my future reference when this occurs again :).
In Star Schemas, there are sometimes occasions where the data quality isn't exactly perfect. And sometimes, for some users/companies, this is acceptable. By default, SAS will not allow missing dimension keys into OLAP cubes and generate an error and stop processing altogether. However if missing dimension keys are okay for your situation, click the Advanced button on the initial edit/create cube screen, move to the Star Schema Options tab and select something other than 'Print the error & stop processing'.
2 Comments
Harry,It would be nice to have that information immediately in the proc OLAP log. In the meantime, here is a quick example of how to retrieve the missing keys using a proc SQL statement. The work.keys table was simply the dimension table example with a missing key (select distinct region from sashelp.shoes where region ne 'Canada'). The sashelp.shoes table would then act as the FACT table.proc sql;select distinct shoes.region, keys.region as regionkeyfrom sashelp.shoes full join work.keyson shoes.region = keys.regionwhere regionkey = '';quit;~ Angela
Angela:I've sent a couple suggestions to SAS Tech Support regarding the missing dim key issue. It'd be really helpful to have an option to create a dataset of the fact table rows that have dimension key issues, and/or display in the log how many fact rows were affected by the dim key not found.