SSAS - How to create a geographical dimension?
I'm working on a SQL Server Analysis Services multidimensionnal cube. I want to create a geaographical dimension to allow user to explore the data regarding to a specific country, region, department, city, store.
Country > Region > Department > City > Store > Sale
So I created a new dimension by selecting the Sale table including all cascading dependencies. Then, I dragged items'name to create a hierachy as the screen capture bellow explains.
This is the diagram of all relationships between attributes.
Finally, after processing I can browse the dimension. However, there is something that scaring me, I can't browse stores, there are only country, region, department and city nodes. So how to configure to display stores ?
Is there a way to display only branches containing stores because it's not relevant to display 36 000 cities for only 80 stores.
As you can seen, there are three warnings :
Avertissement 10 AttributeRelationship [DimStore].[Sale].[Id_] : Le nom spécifié pour la relation d'attribut diffère du nom de l'attribut associé. 0 0
Avertissement 11 AttributeRelationship [DimStore].[Store].[Name_] : Le nom spécifié pour la relation d'attribut diffère du nom de l'attribut associé. 0 0
Avertissement 12 Dimension [DimStore] : Éviter les hiérarchies d'attributs visibles pour les attributs utilisés comme niveaux dans les hiérarchies définies par l'utilisateur. 0 0
Any idea to solve that ?
I would flatten all those tables into one using a SQL view or ETL into a Dimension table. Then I would add that to the Data Source View, then painfully repoint each attribute at that. This gives you the opportunity to shape the data to suit SSAS.
I would also return the Attribute Relationships to their original state - all related directly to the Key attribute. There is little benefit in arranging them hierarchically and it relies on the input data being perfectly structured now and into the future.
- Audit table update trigger does not work using a stored procedure
- Entity Framework | Sequence contains more than one matching element
- How can I alter column name in Sql server
- How to partition into batch of records with max batch size limit
- LOAD DATA INFILE error in MySQL
- Cannot perform an aggregate function on an expression containing an aggregate or a subquery with STRING_AGG
- Perform a row count of each table in my list of tables
- Entity Framework Core 3.1.6 Item With Same Key Has Already Been Added
- Split a string with no delimiters into columns
- Each GROUP BY expression must contain at least one column that is not an outer reference
- Add custom columns to pivot table
- Parameterized raw sql query much slower than query with actual values
- SQL Server database backup restore on lower version
- Add a value on their SESSION_CONTEXT for all sys.dm_exec_sessions
- How can I create a unique constraint on my column (SQL Server 2008 R2)?
- Summation of a column using certain conditions
- Natural join in SQL Server
- How to concatenate text from multiple rows into a single text string in SQL Server
- How to Renumber Set/Subset of Table Based on Numbering of A Second Table
- Using Azure Devops deploy release to multiple repos
- SQL Server Cursor vs WHILE loop
- SSIS: Insert multiple Excel files with same format but different file name to multiple database table destinations based on the Excel filename
- Seeking Advice on Efficient Data Unpivoting in SQL Server with SSIS or Alternative Tools
- SQL-How to Insert Row Without Auto incrementing a ID Column?
- Why can't I use an alias in a DELETE statement?
- Can ransomware encrypt a mounted SQL database? Hypothetical
- Grant user DDL permissions on specific schema
- How do I perform a GROUP BY on an aliased column in SQL Server?
- Collecting data from a joined table based on condition(s) in the joined table
- Stored procedure for auto generating roll no?